Selecting record using a time interval

dcjones

Hi all,

I have a table which has the following fields;

CurrentTime - type TIME

I want to be able to SELECT all the records in the table that have a "CurrentTime" which matches now time and all the records for the next 6 hours. The code I am playing with is:

SELECT
*
FROM
Flight24
CurrentTime   >= curtime()
AND
CurrentTime  >= DATE_SUB(NOW(),INTERVAL 6 HOUR) 
ORDER BY CurrentTime

The above query does bring back data where the "CurrentTime" is now time or greater but also all the records up to midnight. Can anyone see where I am going wrong.

Lars_Berg

select *
  from Flight24
 where CurrentTime between current_time and current_time + interval'6'hour
 order by CurrentTime

dcjones

Hi Lars Berg,

Many thanks for your reply.

Where does the "current_time" fit in with the query. Its not one of the fields in the table.

Lars_Berg

Cuurent_time is a standard sql function that returns the current time. Since you said you had a time column it is more apropriate to use than the now function that returns a timestamp and in addition is specific to mysql.

dcjones

Hi Lars Berg,

If I run:
[PHP
select *
from Flight24
where CurrentTime between current_time and current_time + interval'6'hour
order by CurrentTime
[/code]

no records are returned.

I know I have records in the table with the "CurrentTime" field with times between "04:38" and "23:05".

What I am trying to get as a result is all the records that have a "CurrentTime" between the time the query is run and 6 hours ahead.

So if the query was run at 14:00 hours I would like the result to return all the records where the field "CurrentTime" has a value of "14:00" and up to "20:00".

Can you help please.

johanafm

Intervals can be used with datetimes, and since dates are converted to [THE_DATE] 00:00:00, dates are also acceptable. A time however, cannot be converted to a datetime, and as such can't be used with INTERVAL.
NOW() returns current datetime, i.e. YYYY-MM-DD HH:ii:ss, which can be used with interval. And the result of this operation can be used as an argument to DATE_FORMAT

SELECT * FROM table
WHERE your_time_field BETWEEN CURTIME()
AND DATE_FORMAT(NOW() + INTERVAL 6 HOUR, '%H:%i')

But I'm still curious. Do you really store flight times as hours:minutes? And should they not in fact be stored as datetime?

dcjones

Hi johanafm,

That just what I was lookingfor, great result.

Under normal conditions you are correct when you say that flight data id stroed as a date but in this case I also have a field called CurrentTime which holds data concerning the wheels up time for processing else where. Wheels up time is slightly different from the actual departure time.

Again, many thanks for your help.

dcjones

Hi johanafm,

Sorry to be a pain, I have had to revistit this problem again because it was not working as expected because as you rightly stated I should be using "dateTime". Using:

SELECT * FROM table
WHERE your_time_field BETWEEN CURTIME()
AND DATE_FORMAT(NOW() + INTERVAL 6 HOUR, '%H:%i')

caused issues when the "CurrentTime" was approaching midnight.

I have now included a "datetime" field called "ScheduledDateTime" which holds the scheduled date and time of a flight. Example: 2011-03-15 06:40:00

Is there a way to display records that are 1 hours before "CurrentTime" and 6 hours after "CurrentTime", something like:

SELECT * FROM table
WHERE ScheduledDateTime = DATE_FORMAT(NOW() - INTERVAL 1 HOUR, '%Y%m%d%H:%i')
AND
ScheduledDateTime = DATE_FORMAT(NOW() + INTERVAL 6 HOUR, '%Y%m%d%H:%i')

although the above does not work.

I have been struggling with this all weekend.

dcjones

Hi all,

Sorry to bump my post but I am strutting to get this to work. Can anyone help.

johanafm

For starters, you are not using the correct date time format, since there is a space between the date and the time, whereas you are formatting without whitespace, i.e.

2011-03-2812:55
-- rather than
2011-03-28 12:55

But, there is no need to use any special formatting since you are now allready using datetimes. As such, all you need is NOW() - INTERVAL 1 HOUR and NOW() + INTERVAL 6 HOUR respectively.

However, you are using the equality operator, which means a matching row needs to have a datetime matching 1 hour in the past as well as 6 hours in the future. No time will ever do that. Let's say you have current time as january 1st, 2011, 01:00 which gives
1 hour in the past as
2011-01-01 00:00
and 6 hours in the past as
2011-01-01 07:00

By inspection, it is obviously impossible for one time to match both those times. You need to use either NOW() - INTERVAL 1 HOUR <= table.datetimefield && datetimefield <= NOW() + INTERVAL 6 HOUR, or you could use table.datetimefield BETWEEN NOW() - INTERVAL 1 HOUR AND NOW() + INTERVAL 6 HOUR.

dcjones

Hi,

I am still having problems with trying to get my SQL statement to produce the correct results.

I have a table which holds data and within the table there is a feild named "CurrentTime" and it is a type "time". A typical entry would be "08:45:00"

I am trying to write a query that will look at the table and bring back all records where the CurrentTime is equal to NOW() time minus 30 mins plas 120 mins.

The query below does this but, and theres always a but, when the NOW() time approaches midnight no data is returned.


SELECT 
ScheduledTime, 
Flight, 
Logo, 
IATALookup, 
RemarksWithTime, 
Terminal, 
Zone 
FROM TableName
WHERE CurrentTime BETWEEN DATE_FORMAT(NOW() - INTERVAL 45 MINUTE, '%H:%i') AND DATE_FORMAT(NOW() + INTERVAL 120  MINUTE, '%H:%i') AND ArriveDepart = 'O'  

AND FlightDisplay = 'Y' 
ORDER BY ScheduledTime, FlightHistoryID, CodeShare ASC

I have received a lot a help "johanafm" and I would not have gotten this far without it, so johanafm, thank you for that.

Can anyone see how I can make this work.

johanafm

As you approach midnight, you will eventually end up asking if
23:30
is between
23:00 and 01:00

Which it isn't. But
23:30 day 1
is between
23:00 day 1 and 01:00 day 2

So, use CURRENT_TIMESTAMP or NOW() instead of CURRENT_TIME.

dcjones

Hi johanafm,

Many thanks for getting back to me so quickly.

if my SQL query is:

SELECT 
ScheduledTime, 
Flight, 
Logo, 
IATALookup, 
RemarksWithTime, 
Terminal, 
Zone 
FROM TableName 
WHERE CurrentTime BETWEEN DATE_FORMAT(NOW() - INTERVAL 45 MINUTE, '%H:%i') AND DATE_FORMAT(NOW() + INTERVAL 120  MINUTE, '%H:%i') AND ArriveDepart = 'O'   

AND FlightDisplay = 'Y' 
ORDER BY ScheduledTime, FlightHistoryID, CodeShare ASC

where would I place or replace CURRENT_TIMESTAMP within this query, I have tried a couple of changes but they produce a syntax error.

Again, many thanks for your help.

Derokorian

The problem is the CurrentTime column, if its of time TIME there is no way to distinguish what DAY that time belongs to. If I have a TIME type column with the value 15:27 can you tell me if that's within 20minutes of Feb 21, 2012 15:15 ? You can't because as far as you know that's 15:27 on March 8, 1967. You might try using DATETIME instead.

johanafm

Oh of course. I assumed that CurrentTime was actually the reserved word CURRENT_TIME, which would have made no sense as used in the actual query.

Like Derokorian said, change it to datetime if that's the problem. But wether it is or not, you should also keep the date information in the rest of the where clause. That is, don't format the datetime as H:i. For this part, the easiest way would be to just drop date_format alltogether.

dcjones

Hi johanafm,

Thanks for your reply.

Because the field CurrentTime is used for other calculations I have inserted a new field in the table called CurrentDateTime (Type: datetime).

The QUERY I am now using is:

$MinusIntervalDepart = 45
$PlusIntervalDepart = 120

CurrentDateTime BETWEEN CURRENT_TIMESTAMP - INTERVAL $MinusIntervalDepart MINUTE  AND  CURRENT_TIMESTAMP + INTERVAL $PlusIntervalDepart MINUTE

This appears to be returning the results I was looking for.

The results are now returning all records where the CurrentDateTime is between CURRENT_TIMESTAMP - 45 minuets and + 120 minuets.

I think I have this right at last.

Many thanks for all your time.

johanafm

dcjones;10997344 wrote:
Because the field CurrentTime is used for other calculations I have inserted a new field in the table called CurrentDateTime (Type: datetime).

I'd recommend fixing your existing code so that it uses DATA_FORMAT(dtfield, '%H:%i:%s') where it needs only time, rather than storing redundant data in every record.
What happens if someone updates one of those but not both? Suddenly you have one field with 9:00 and another with 17:45...

Store data in one datetime field and use whatever parts of it you need as needed. Just like you wouldn't add another field called yeardata rather than using YEAR(dtfield)