A weirdly named site with lots of boring content!

MySQL's Greatest Date and Time Functions

It returns a time as a string, in the format 'HH:MM:SS' with leading zeros:

SELECT * FROM table WHERE TIMEDIFF(NOW(), timestamp) > '00:03:00'

This returns rows where the timestamp was made less than 3 minutes ago. Be sure to put the two times passed to TIMEDIFF in the right order - the second time is subtracted from the first, meaning that if the second time is chronologically after the first, it will return a negative time, like '-54:20:00'.

9)Delete Rows From More than an hour ago



DELETE FROM table WHERE TIMEDIFF(NOW(), timestamp) > '01:00:00'

This deletes rows where the timestamp was made over an hour ago, and is useful for purging a table of expired rows.

10)Select Rows From Less than 20 hours ago



While the TIMEDIFF() function might seem like it would be able to filter rows in this way, there is sometimes a problem when working with time differences of 10 hours or more. Selecting rows where TIMEDIFF(now(), timestamp) < '20:00:00' would not only return rows with a time difference of '19:59:59' and less, but - because the time difference is a string - also rows with differences like '182:05:00' (182 hours, five minutes) and even '209:20:45' (this latter because ':' comes after '9' in the ASCII table).

However we can use the UNIX_TIMESTAMP() function to easily get the number of seconds between two dates or times as in this query:

SELECT * FROM table WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(timestamp) < 72000

Here, 72000 is the number of seconds in 20 hours.
1 2 3