MYSQL SELECT where time is HOURS less

[username]

Hi, I have been trying a few methods to SELECT records from my db, which were inserted say 4 hours ago. I have tried with a DATE field and TIME field on it's own, and a DATETIME field, and have been unable to get an accurate query.

The field are:

date_added: 2009-02-26
time_added: 10:51:55
date_time: 2009-02-26 10:51:55

My query's have been:

SELECT * FROM table WHERE date_added >= DATE_SUB(CURDATE(), INTERVAL 8 HOUR)

I have also tried:

SELECT * FROM table WHERE date_time >= TIME_SUB(NOW(), INTERVAL 8 HOUR)

I have tried a few others and must be close. Your help would be appreciated..thanks.

[henry0]

I will change it all, have only one field (timestamp)
from there you may extract anything in any format you wish
for the query it will be timestamp-(14400)

[mikhaill]

Try .... => (curdate( ) - INTERVAL 8 HOUR)

[username]

Hi, still no luck. I have added a DATETIME field to the database with sample data: 2009-03-02 8:51:55 , but am still looking for a query which will pull results such as this which are less than 4 hours than the current time i.e now(). Any ideas?

Basically my query needs to extract records added 4 hours ago.

Thanks.

[coopster]

The INTERVAL should work fine.

WHERE myTimeStampField >= CURRENT_TIMESTAMP - INTERVAL 4 HOUR

[rocknbil]

pull results such as this which are less than 4 hours than the current time i.e now()

You almost had it at first, but hour values won't show any date math with curdate() unless it actually changes the date. For example, if it's two minutes past midnight and you do date_sub(curdate(), interval 2 hour) it will show yesterday's date, but nothing about the hour because hour data is not part of the data type.

Also if you're doing date/time math, you need to use now() which does the correct datetime format, 2009-02-12 12:34:56. If you use curdate() it will work, but it's like this: 2009-02-12 00:00:00.

Drop this into your mysql command line, presuming table: date_added is a datetime field:

select * from table where date_added >= date_sub(now(), interval 4 hour);

This will give you all entries from 4 hours ago and into the future, if you have entries that are later than "now".

[username]

Thanks rocknbil, I implemented the query, and although this is an improvement strangley enough it still gives entries which were entered 13 hours forward to the present time. I only want entries entered 4 hours ago until the present. I tried it with the INTERVAL as 1 hour, and this brings the results to approximately 7 hours until the present. I am using a UNION ALL query, to search multiple db tables. Would this matter?

[rocknbil]

it still gives entries which were entered 13 hours forward to the present time.

Are you saying it is giving you dates in the future, like, tomorrow? This is what I meant with

This will give you all entries from 4 hours ago and into the future, if you have entries that are later than "now".

If you want from 4 hours ago up until this moment, you need to tell it so:

select * from table where date_added >= date_sub(now(), interval 4 hour) and date_added <= now();

If you mean 13 hours in the past, I would say your system clock is either wrong or you are not taking into account the time difference in GMT. If you're on the west coast and your server is using GMT as it's time, you need to offset by 8 hours to get YOUR current time.

select * from table where date_added >= date_sub(now()+8, interval 4 hour) and date_added <= now()+8;

To tell, just enter this in your server's mysql

select now();

If your local computer says it's 12 noon and your server mysql says it's 8PM, it's running on GMT.

[username]

No that still not it. Just to clarify, I need a query which will pull results which were added 4 hours ago until the current time now. These results will be tested on a field called date_time, which is of DATETIME type. I am wondering whether the solution provieded above takes into account this is a datetime field. i.e 2009-12-31 10:11:59.

It is still not working. Any ideas? I am using this to extract data for a query of new content added within the last 4 hours.

[stargateanubis14]

try something simple like where your column "date_added" is simply the time put in there (via Unix timestamps). So date_added would be an integer. so when the data is added just use a php time() (in which "time()" returns the current unix timestamp)
If you wanted 8 hours before input time, it would be date_added - (60*60*8)
so your query would be something like

$ctime=time();
SELECT * FROM table WHERE date_added >= ($ctime-28800)

i know that probably has flaws, simply because i'm writting in a hurry...but it gives the general idea