[Solved] Query displaying t>=CURRENT_TIME with offset etc

Creating tables and queries
Post Reply
User avatar
MikeytheMagnificent
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

[Solved] Query displaying t>=CURRENT_TIME with offset etc

Post by MikeytheMagnificent »

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!
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by Villeroy »

Code: Select all

SELECT "blah", "blah" FROM "blob" WHERE ABS(DATEDIFF('minute',CURRENT_TIME,"TimeField"))<=5
Database design is development. HSQL documentation is for developers. Developers don't need any more particularly when the product conforms to well known standards.
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
User avatar
MikeytheMagnificent
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by MikeytheMagnificent »

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

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by Villeroy »

With HSQL2:

Code: Select all

SELECT "blah", "blah" 
FROM "blob" 
WHERE "TimeStampField" BETWEEN CURRENT_TIMESTAMP AND DATEADD('minute',5,"TargetStamp")
HSQL1:

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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by chrisb »

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:-

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
USE ONLY the where clause which is most appropriate to your situation.
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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by chrisb »

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'.
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
User avatar
MikeytheMagnificent
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by MikeytheMagnificent »

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
Screenshot 2015-10-26 22.28.13.png
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?).
Screenshot 2015-10-26 22.27.02.png
Screenshot 2015-10-26 22.27.02.png (8.11 KiB) Viewed 3445 times
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' }
and the qDesigner syntax

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by Villeroy »

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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by chrisb »

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.

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
User avatar
MikeytheMagnificent
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

Re: Query displaying t>=CURRENT_TIME with offset (+- mins)

Post by MikeytheMagnificent »

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

Code: Select all

 update TT1 set TTIME=TIME
; 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

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
However I'm sure I can trust your code now I've got 2.3 working
Yes, thanks again it says command successful

Code: Select all

select *from "2681"
where MON_FRI between current_time and current_time + 55 minute  
or whatever
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
Post Reply