[RESOLVED] Using CURTIME()

dcjones

Hi all,

I am working on an SQL Query which return data between two times during the day.

This works fine:

SELECT ScheduledTime, Flight, Logo, IATALookup, RemarksWithTime, Terminal FROM LHRFlightStats24_Vanilla WHERE ScheduledDateTime BETWEEN NOW() - INTERVAL 45 MINUTE AND NOW() + INTERVAL 90 MINUTE AND ArriveDepart = 'O'  AND FlightDisplay = 'Y' ORDER BY ScheduledTime ASC

In the table I have a feild called "ScheduleTime" which is a time type, example "12:02:00"

If I change the query replacing NOW() with CURTIME() it does not work

SELECT ScheduledTime, Flight, Logo, IATALookup, RemarksWithTime, Terminal FROM LHRFlightStats24_Vanilla WHERE ScheduledTime BETWEEN CURTIME() - INTERVAL 45 MINUTE AND CURTIME() + INTERVAL 90 MINUTE AND ArriveDepart = 'O'  AND FlightDisplay = 'Y' ORDER BY ScheduledTime ASC

Is there a way to use CURTIME() or am I doing this completely wrong.

Derokorian

Well I think its the way curtime works, check this page: Date and Time functions When I run this query:

SELECT NOW() - INTERVAL 45 MINUTE as now, CURTIME() - INTERVAL 45 MINUTE as curtime

I get the following:

now	                curtime
2012-02-12 09:45:19	NULL

Which suggests to me you can use intervals on curtime.

dcjones

Thanks Derokorian,

I will play with the code and let you know the results.

Cheers.

Derokorian

Had a minute to play around with it, see if this helps:

SELECT
   TIMEDIFF( CURTIME(), '00:45:00' ) as 45min,
   TIMEDIFF( CURTIME(), '00:30:00' ) as 30min,
   CURTIME() as curtime

Which yields:

45min      30min      curtime
12:40:47   12:55:47   13:25:47

dcjones

Hi Derokorian,

Many thanks for your time. I ended up using your code.

SELECT
   TIMEDIFF( CURTIME(), '00:45:00' ) as 45min,
   TIMEDIFF( CURTIME(), '00:30:00' ) as 30min,
   CURTIME() as curtime

Again, thanks.

Derokorian

No prob! Glad I could help, you got me interested so I had to work on it again lol.