lisovaccaro lisovaccaro - 4 months ago 18
SQL Question

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 sintaxis to be ok and it works when used on other tables, but for some reason when I use it on table

Posts
I 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 I can try? What could be the reason for this?

Answer

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...

Comments