MYSQL Date Time Round To Nearest Hour

Update: I think https://stackoverflow.com/a/21330407/480943 is a better answer.

You can do it with some date arithmetic:

SELECT some_columns,    DATE_ADD(        DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00"),        INTERVAL IF(MINUTE(the_date) < 30, 0, 1) HOUR    ) AS the_rounded_dateFROM your_table

Explanations:

• DATE_FORMAT: DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00") returns the date truncated down to the nearest hour (sets the minute and second parts to zero).

• MINUTE: MINUTE(the_date) gets the minute value of the date.

• IF: This is a conditional; if the value in parameter 1 is true, then it returns parameter 2, otherwise it returns parameter 3. So IF(MINUTE(the_date) < 30, 0, 1) means "If the minute value is less than 30, return 0, otherwise return 1". This is what we're going to use to round -- it's the number of hours to add back on.

• DATE_ADD: This adds the number of hours for the round into the result.

Half of the hour is a 30 minutes. Simply add 30 minutes to timestamp and truncate minutes and seconds.

SELECT DATE_FORMAT(DATE_ADD(timestamp_column, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00:00') FROM table

soul's first solution truncates instead of rounding and the second solution doesn't work with Daylight Savings cases such as:

select FROM_UNIXTIME(UNIX_TIMESTAMP('2012-03-11 2:14:00') - MOD(UNIX_TIMESTAMP('2012-03-11 2:14:00'),300));

Here is an alternate method (1):

DATE_ADD(    tick,    INTERVAL (IF((MINUTE(tick)*60)+SECOND(tick) < 1800, 0, 3600) - (MINUTE(tick)*60)+SECOND(tick)) SECOND)

If you don't need to worry about seconds you can simplify it like this (2):

DATE_ADD(    tick,    INTERVAL (IF(MINUTE(tick) < 30, 0, 60) - MINUTE(tick)) MINUTE)

Or if you prefer to truncate instead of round, here is simpler version of soul's method (3):

DATE_SUB(tick, INTERVAL MINUTE(tick)*60+SECOND(tick) SECOND)

EDIT: I profiled some of these queries on my local machine and found that for 100,000 rows the average times were as follows:

• soul's UNIXTIME method: 0.0423 ms (fast, but doesn't work with DST)
• My method 3: 0.1255 ms
• My method 2: 0.1289 ms
• Ben Lee's DATE_FORMAT method: 0.1495 ms
• My method 1: 0.1506 ms