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

Creating tables and queries

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

Postby MikeytheMagnificent » Fri Oct 16, 2015 6:49 pm

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/builtinfunctions-chapt.html#bfc_datetime_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
MikeytheMagnificent
 
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

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

Postby Villeroy » Fri Oct 16, 2015 7:17 pm

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27744
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MikeytheMagnificent » Fri Oct 16, 2015 10:03 pm

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

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

Postby Villeroy » Fri Oct 16, 2015 10:21 pm

With HSQL2:
Code: Select all   Expand viewCollapse view
SELECT "blah", "blah"
FROM "blob"
WHERE "TimeStampField" BETWEEN CURRENT_TIMESTAMP AND DATEADD('minute',5,"TargetStamp")


HSQL1:
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27744
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby chrisb » Sun Oct 18, 2015 6:42 pm

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   Expand viewCollapse view
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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

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

Postby chrisb » Sat Oct 24, 2015 9:45 pm

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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

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

Postby MikeytheMagnificent » Tue Oct 27, 2015 2:22 am

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 2425 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   Expand viewCollapse view
SELECT "MON_FRI" AS "midweek" FROM "ALL" WHERE "MON_FRI" > {T '07:15:00' }

and the qDesigner syntax
Code: Select all   Expand viewCollapse view
>#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
MikeytheMagnificent
 
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

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

Postby Villeroy » Tue Oct 27, 2015 2:44 am

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

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

Postby chrisb » Tue Oct 27, 2015 4:00 pm

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   Expand viewCollapse view
select "TIME", "RANDNUMBER" from "TT1"
where cast(current_date || ' ' || "TIME" as timestamp) between current_timestamp and current_timestamp + 5 minute
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

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

Postby MikeytheMagnificent » Mon Nov 02, 2015 7:38 pm

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests