[SQL] Select timestamps over 1 hour old

[simsie]

Hello,

In my table I've got a column called 'time_handled' and in there is a MySQL timestamp. On another page I want to run a query that brings up records where the time_handled is over 1 hour old.

So far I've tried this:

SELECT * FROM table WHERE time_handled < NOW() - 3600

This worked for a bit but as soon as the clock changed from 16:59 to 17:00 the server started showing records less than 1 hour old again. I think it's something to do with how MySQL treats NOW() when used as an integer.

Does anyone out there know the correct way to do this?

Thanks,

Simsie

[HeyRatFans]

By timestamp, do you mean a datetime field (YYYY-MM-DD HH:MM:SS) or is it an INT that you store the UNIX timestamp in directly?

If it's the later, you will need to convert NOW() into a UNIX timestamp for it to work, i.e.:

SELECT * FROM table WHERE time_handled < UNIX_TIMESTAMP(NOW()) - 3600;

If the column is a datetime or date column and you have MySQL 5.0 you can do:

SELECT * FROM table WHERE TIMESTAMPDIFF(MINUTE, time_handled, NOW()) > 60;

Edited August 31, 2009 by DecoyDuck

[Kami-]

Hi...

SELECT *

FROM [dbo].[myTable]

WHERE
	[time_handled] > DATEADD(hour,-1,GETDATE())

[simsie]

Thank you :)

Unfortunatly both solutions seem to come up with errors. I'm using MySQL 4.1.20 annoyingly.

The site itself is using PHP. Would it be easier to do the date - 1 hour in PHP and if so, how?

Thanks once again.

[Cobra]

SELECT * FROM table WHERE time_handled < SUBDATE( timestamp(now()), INTERVAL 1 HOUR)

[simsie]

It works, thanks :)

[Kami-]

Ah, sorry, my solution was for MSSQL :) You didn't make it clear you were using MySQL in your original post.

[-Alex-]

  F7S said:

Ah, sorry, my solution was for MSSQL :) You didn't make it clear you were using MySQL in your original post.

  simsie said:

Hello,

In my table I've got a column called 'time_handled' and in there is a MySQL timestamp...

:p