Convert a string which contains a time to use in MySQL query

dcjones

Hi all,

I have a field in my table called "CurrentTime" that contains time as a string, ie: 09:34. I need to be able to use that variable in an SQL query, something like;

SELECT * FROM TABLE WHERE CurrentTime = the time now minus 600 seconds.


SELECT ScheduledTime, Flight, Logo, IATALookup, RemarksWithTime, Terminal, Gate  FROM LHRFlightStats24 WHERE CurrentTime = NOWTIME - 600 AND ArriveDepart = 'O'  AND Terminal = '1' AND  FlightDisplay = 'Y' ORDER BY ScheduledTime ASC, IATALookup ASC

I know the above is totally incorrect but its just shows what I am trying to do.

bradgrafelman

If 'CurrentTime' is to hold a time, then I'm assuming that its a TIME column. Following that assumption...

This pseudo-query:

SELECT * FROM TABLE WHERE CurrentTime = the time now minus 600 seconds

would be written in MySQL as:

SELECT *FROM TABLE WHERE CurrentTime = CURTIME() - INTERVAL 600 SECOND

The neat thing about the SQL language is that it's pretty close to what you'd say in plain English. 🙂

dcjones

Hi Brad,

There are two of us sitting here trying to get our heads around this issues, getting nowhere fast.


SELECT *FROM TABLE WHERE CurrentTime = CURTIME() - INTERVAL 600 SECOND

The "CurrentTime" field holds, lets say "09:21:34"

CURTIME() is "09:15:00" minus 600 seconds, I am asumming this is the server time.

When I run the query, I have a nil result. This is the part I can't get my head around. I would have thourght it would return the record with the "CurrentTime" of "09:21:34".

I remove "- INTERVAL 600 SECOND" I get a result.

bradgrafelman

I don't really understand what you're saying. If the value of the CurrentTime field is "09:21:34", then the above query will only exactly 600 seconds after that value, in other words that row will be selected when the time on the server is 09:22:34.

dcjones

Hi Brad,

Just to try and outline in a way that even I am getting confused, which may be the terminology of time!

In the table we have a time field,, lets call it 'table time'
the we have the server time,, lets call it 'clock time'

My objective is to extract all records and display them where they are no more than 10minutes old.

e.g
if the clock time is say 09:15
set my reference at -10 minutes which will be 09:05 (RefTime)

a record where 'table time' is 09:10 would be selected (True)
a record where 'table time' is 09:02 would not (False)

so my thoughts were select where 'table time' > (RefTime)
...........
your previous reply helped but returned no records
SELECT *FROM TABLE WHERE CurrentTime = CURTIME() - INTERVAL 600 SECOND

so we removed the - INTERVAL 600 SECOND
and it returned all records correctly forward of clock time, which is great, but we need that extra 10minute buffer as above....

we all feeling fuzzy as its late on a saturday eve...so any idea would be great...
many thanks for you anticipated help.
Dereck

bradgrafelman

dcjones wrote:
My objective is to extract all records and display them where they are no more than 10minutes old.

Oh.. well that's a lot different than what you originally seemed to be describing.

First off, what column type is the CurrentTime column in your DB? Again, assuming it's of type TIME, something like this should work:

... WHERE CurrentTime BETWEEN (CURTIME() - INTERVAL 600 SECOND) AND CURTIME()