CODEWITHSUNDEEP
mysqlsql
lisovaccaro
lisovaccaro

Reputation: 33946

DATETIME select rows from last 2 hours

I'm trying to select rows from the last 2 hours. For some reason it doesn't work. The code syntax looks ok and works when used on other tables, but for some reason when used on table Posts, get rows a lot older than 2 hours.

SELECT * FROM Posts WHERE `Date` > SUBDATE( CURRENT_DATE, INTERVAL 2 HOUR)

Is there any problem with the code? Is there another way to write it? What could be the reason for this?

Upvotes: 14

Views: 21976

Answers (5)

Krisi Suci
Krisi Suci

Reputation: 139

none of the answers above worked for me, try this:

SELECT * FROM POSTS WHERE DATE > DATEADD(HH, (-2), GETDATE())

Upvotes: 0

Faisal
Faisal

Reputation: 4767

You can try this one

SELECT * FROM Posts WHERE `Date` > DATE_SUB(CURDATE(), INTERVAL 3 HOUR)

Upvotes: 1

DarkAjax
DarkAjax

Reputation: 16223

That's because you're using CURRENT_DATE, you should use NOW() or CURRENT_TIMESTAMP instead.

The problem is that using CURRENT_DATE, being a date value, the time defaults to 00:00:00, so by substracting 2 hours you end up getting 22:00:00 of the previous day, instead of the last 2 hours...

Upvotes: 9

Ray
Ray

Reputation: 41408

You can use simpler notation:

SELECT * FROM Posts WHERE `Date` > NOW() - INTERVAL 2 HOUR

Upvotes: 24

Nesim Razon
Nesim Razon

Reputation: 9794

change to this:

SELECT * FROM Posts WHERE `Date` > SUBDATE( CURRENT_TIMESTAMP, INTERVAL 2 HOUR)

Upvotes: 6

Related Questions