CODEWITHSUNDEEP
mysqlselect
user1774442
user1774442

Reputation: 49

select mysql statement where current time is at least 60+ minutes from timestamp

I have a database with a timestamp field called DATECREATED and I am trying to run a select statement to retrieve all records where current time is at least 60+ minutes from timestamp

Eg.

timestamp is 2012-10-29 12:08:11.

I want to retrieve a record only if it has passed 60 minutes from the timestamp above. So if the time is 13:09, or 15:09, it should be retrieved. If the time is 12:15, it should return 0;

Any help would be greatly appreciated as other answers here haven`t helped so far.

Thanks.

Upvotes: 3

Views: 2725

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272166

CURRENT_TIMESTAMP - INTERVAL 60 MINUTE returns the time that was 60 minutes ago. Any time less than or equal to that value should be, well, at most 60 minutes old.

SELECT *
FROM `table`
WHERE `datecreated` <= CURRENT_TIMESTAMP - INTERVAL 60 MINUTE

Upvotes: 1

juergen d
juergen d

Reputation: 204794

select * from your_table
where DATECREATED - interval 60 minute <= now()

See this SQLFiddle

Upvotes: 2

Bohemian
Bohemian

Reputation: 425083

...
where DATECREATED between subdate(timestamp, interval 60 minute) 
                      and adddate(timestamp, interval 60 minute)

by using a between like this with two constant values (calculated once per query), the index on DATECREATED will be used (if one exists)

Upvotes: 0

Related Questions