[Solved] Query displaying t>=CURRENT_TIME with offset etc
- MikeytheMagnificent
- Posts: 137
- Joined: Fri Apr 11, 2008 12:06 am
- Location: W. Yorks UK
[Solved] Query displaying t>=CURRENT_TIME with offset etc
Thats the result rows which change, not the arithmetic on the CURRENT_TIME
plus other fields of course
It seems it should be easy, if not via the Wiz then by the Q designer, but
I'm finding even sql can't seem to deliver, but that could simply be my incompetence
I'm even trying Hsql2.3.2 with loads of extra functions (not the query funtions) but these
http://www.hsqldb.org/doc/guide/builtin ... _functions
but its a guide and definitely not a tutorial however complete and competent it may be. I'd rather not even be with 2.x
I find it very difficult even wrt the 1.8.guide
the same stuff applies to CURRENT_DATE
plus midnight-wrapping could be very useful to give a resultset for n hrs from now or now+offset
plus should the sql respond to CURDATE or even 'today' as it boasts, because mine seems not to!
plus other fields of course
It seems it should be easy, if not via the Wiz then by the Q designer, but
I'm finding even sql can't seem to deliver, but that could simply be my incompetence
I'm even trying Hsql2.3.2 with loads of extra functions (not the query funtions) but these
http://www.hsqldb.org/doc/guide/builtin ... _functions
but its a guide and definitely not a tutorial however complete and competent it may be. I'd rather not even be with 2.x
I find it very difficult even wrt the 1.8.guide
the same stuff applies to CURRENT_DATE
plus midnight-wrapping could be very useful to give a resultset for n hrs from now or now+offset
plus should the sql respond to CURDATE or even 'today' as it boasts, because mine seems not to!
Last edited by MikeytheMagnificent on Mon Nov 02, 2015 7:42 pm, edited 1 time in total.
Many opportunities to add short Tutorials or faq's are missed
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
Code: Select all
SELECT "blah", "blah" FROM "blob" WHERE ABS(DATEDIFF('minute',CURRENT_TIME,"TimeField"))<=5
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MikeytheMagnificent
- Posts: 137
- Joined: Fri Apr 11, 2008 12:06 am
- Location: W. Yorks UK
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
The help was much appreciated.
The suspected sarcasm just annoying
Sorry, but I hadn't realised this was a developers forum
anyhow my meaning was rather
at least not in the 1.8 guide, the 2.x guide provides a good selection, but means you have to negotiate all the nasties in 2.3 to benefit
afaik dateadd produces a proper time, wheras datediff a number with time unit segments
Even better
The suspected sarcasm just annoying
Sorry, but I hadn't realised this was a developers forum
anyhow my meaning was rather
Code: Select all
alltimes between (now + 5 mins) and midnight rather than (now + or - 5 mins)
I suspect that may be covered by the dateadd function which doesn't seem to exist
at least not in the 1.8 guide, the 2.x guide provides a good selection, but means you have to negotiate all the nasties in 2.3 to benefit
afaik dateadd produces a proper time, wheras datediff a number with time unit segments
Even better
Code: Select all
alltimes between now and some time tomorrow , still with my 5 min offset, naturally
Many opportunities to add short Tutorials or faq's are missed
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
With HSQL2:
HSQL1:
Code: Select all
SELECT "blah", "blah"
FROM "blob"
WHERE "TimeStampField" BETWEEN CURRENT_TIMESTAMP AND DATEADD('minute',5,"TargetStamp")
Code: Select all
SELECT "blah", "blah"
FROM "blob"
WHERE "TimeStampField" BETWEEN CURRENT_TIMESTAMP AND "TargetStamp"
OR DATEDIFF('minute',"TimeStampField","TargetStamp")<=5
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
OR as you use hsqldb 2.3.2.
to add one day to a 'DATE' or 'TIMESTAMP' field:- select "MyField" + 1 day
to add one day, one hour, ten minutes & ten seconds to a 'TIMESTAMP' field:- select "MyField" + 1 day + 1 hour + 10 minute + 10 second
so your code is:-USE ONLY the where clause which is most appropriate to your situation.
to add one day to a 'DATE' or 'TIMESTAMP' field:- select "MyField" + 1 day
to add one day, one hour, ten minutes & ten seconds to a 'TIMESTAMP' field:- select "MyField" + 1 day + 1 hour + 10 minute + 10 second
so your code is:-
Code: Select all
select *
from "MyTable"
where "MyTimeStampField" between current_timestamp and current_timestamp + 1 day + 5 minute --if using a timestamp field
where cast("MyDateField" || ' ' || "MyTimeField" as timestamp) between current_timestamp and current_timestamp + 1 day + 5 minute --if using individual date & time fields
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
my previous post contains a glaring omission.
the code must be executed in direct mode otherwise a syntax error is generated.
for those who may have attempted & failed to utilise this code i apologise.
please try again using direct mode:-
under 'Database' hit 'Queries'. hit 'Create Query in SQL View'. from the newly opened window >menu:'Edit'>'Run SQL command directly'.
the code must be executed in direct mode otherwise a syntax error is generated.
for those who may have attempted & failed to utilise this code i apologise.
please try again using direct mode:-
under 'Database' hit 'Queries'. hit 'Create Query in SQL View'. from the newly opened window >menu:'Edit'>'Run SQL command directly'.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
- MikeytheMagnificent
- Posts: 137
- Joined: Fri Apr 11, 2008 12:06 am
- Location: W. Yorks UK
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
Awesome answers all 3 respondents including Sliderule from my other topic, very sorry for the delay, on account of me being a very slow learner. I apologise
Please find time to try again ChrisB I would still be very interested to have my simplified version functioning.
Also very interesting, how to ignore the first n results between 'now' and say now+8mins
also how to add or subtract an offset time to each record of a resultset before displaying it
and the qDesigner syntax
Unfortunately I have been unable to recreate ChrisB's function free version although I had foreseen the Direct SQL requirement which demonstrated some improvement in my understanding.I added the brackets, just in case! In my case I only need a time, not timestamp or date (or am I missing something?).
Somehow I was reluctant to believe that I could achieve manipulating arithmetic within the query command and I even thought that if anything it would instead attempt to concatenate the offset.Please find time to try again ChrisB I would still be very interested to have my simplified version functioning.
Also very interesting, how to ignore the first n results between 'now' and say now+8mins
also how to add or subtract an offset time to each record of a resultset before displaying it
Code: Select all
SELECT "MON_FRI" AS "midweek" FROM "ALL" WHERE "MON_FRI" > {T '07:15:00' }
Code: Select all
>#07:15:00#
Many opportunities to add short Tutorials or faq's are missed
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
TIME is an SQL keyword, an identifier for a field type. When you mean your field named "TIME" it has to be quoted. You are free to name your fields "SELECT", "FROM", "WHERE", "DROP" and "INSERT" as long as you put them in quotes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
THIS POST IS RELATIVE TO HSQL 2x ONLY.
my initial post is specific to hsql 2+.
i ensured its validity by testing with hsql 2.3.2 because you stated that this was the version you used (i use the latest version 2.3.3).
the code is not compatible with the bundled version hsql 1.8.10.
if you wish to add or subtract hours, minutes or seconds then you should use a TIMESTAMP field.
a TIME field has strict limitations .i.e. minimum & maximum 00:00:00 to 23:59:59.
if one minute is added to 23:59:00 it then becomes 00:00:00 (not one minute more but 23:59:00 less.
add one minute to a TIMESTAMP field which is equal to '2015-10-27 23:59:00' & the result is '2015-10-28 00:00:00' (one minute more precisely).
the question you are now asking is not the question contained in your original post.
below is a solution in respect of the sql query as posted by you, it simply selects those records where the field "TIME" is between NOW & NOW + 5 minutes
just copy & paste the code directly into the query window remembering to use Direct Sql then execute.
my initial post is specific to hsql 2+.
i ensured its validity by testing with hsql 2.3.2 because you stated that this was the version you used (i use the latest version 2.3.3).
the code is not compatible with the bundled version hsql 1.8.10.
if you wish to add or subtract hours, minutes or seconds then you should use a TIMESTAMP field.
a TIME field has strict limitations .i.e. minimum & maximum 00:00:00 to 23:59:59.
if one minute is added to 23:59:00 it then becomes 00:00:00 (not one minute more but 23:59:00 less.
add one minute to a TIMESTAMP field which is equal to '2015-10-27 23:59:00' & the result is '2015-10-28 00:00:00' (one minute more precisely).
the question you are now asking is not the question contained in your original post.
below is a solution in respect of the sql query as posted by you, it simply selects those records where the field "TIME" is between NOW & NOW + 5 minutes
just copy & paste the code directly into the query window remembering to use Direct Sql then execute.
Code: Select all
select "TIME", "RANDNUMBER" from "TT1"
where cast(current_date || ' ' || "TIME" as timestamp) between current_timestamp and current_timestamp + 5 minute
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
- MikeytheMagnificent
- Posts: 137
- Joined: Fri Apr 11, 2008 12:06 am
- Location: W. Yorks UK
Re: Query displaying t>=CURRENT_TIME with offset (+- mins)
Some of us are just Slow Learners.
I knew the TIME thing really, I guess I was just up too late, I'll try to remember next time. Thanks Villeroy.
But, Why it can't simply be renamed is mysterious. I have worked around this by creating a new replacement field say TTIME updating all records with; copying TT1 to another db and ignoring the TIME field during creation and finally copying the modified table back after deleting the original
Again ChrisB when I said 'I tried 2.3' I meant as in 'tried and failed' , due to my eternal issues with finding a Java JRE which works 'straight out of the box (32 or 64 bit} and then persuading any copied tables from hsql embedded to modify the ID and make the table editable
I seem to have the Java sorted now ('til the next time!
Thanks ChrisB
However I'm sure I can trust your code now I've got 2.3 working
Yes, thanks again it says command successful
or whatever
The timewrapping will actually be a benefit in my case except late on friday
Time to add solved now
"Methinks"
I knew the TIME thing really, I guess I was just up too late, I'll try to remember next time. Thanks Villeroy.
But, Why it can't simply be renamed is mysterious. I have worked around this by creating a new replacement field say TTIME updating all records with
Code: Select all
update TT1 set TTIME=TIME
Again ChrisB when I said 'I tried 2.3' I meant as in 'tried and failed' , due to my eternal issues with finding a Java JRE which works 'straight out of the box (32 or 64 bit} and then persuading any copied tables from hsql embedded to modify the ID and make the table editable
I seem to have the Java sorted now ('til the next time!
Thanks ChrisB
Code: Select all
ALTER TABLE 262 ALTER COLUMN ID INTEGER Generated always as IDENTITY;
works, but
ALTER TABLE 262 ALTER COLUMN ID INTEGER primary key Generated always as IDENTITY;
doesn't. Perhaps I need to do it in 2 stages
Yes, thanks again it says command successful
Code: Select all
select *from "2681"
where MON_FRI between current_time and current_time + 55 minute
The timewrapping will actually be a benefit in my case except late on friday
Time to add solved now
"Methinks"
Many opportunities to add short Tutorials or faq's are missed
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few