MYSQL Date Time Round To Nearest Hour

Question

I have a date time field in a MySQL database and wish to output the result to the nearest hour.

e.g. 2012-04-01 00:00:01 should read 2012-04-01 00:00:00

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

From How to round a DateTime in MySQL?:

It's a little nasty when you do it with datetime data types; a nice candidate for a stored function.

DATE_SUB(DATE_SUB(time, INTERVAL MOD(MINUTE(time),5) MINUTE ), 
         INTERVAL SECOND(time) SECOND)

It's easier when you use UNIXTIME timestamps but that's limited to a 1970 - 2038 date range.

FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))

Good luck.

Answer 5

To round down to the current hour, select:

FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(column_name) / 3600) * 3600).

The value is expressed in the current time zone doc

Answer 6

This will return the next hour, that is '2012-01-02 18:02:30' will be converted into '2012-01-02 19:00:00'

TIMESTAMPADD(HOUR,
    TIMESTAMPDIFF(HOUR,CURDATE(),timestamp_column_name),
    CURDATE())

Instead of CURDATE() you can use an arbitrary date, for example '2000-01-01' Not sure if there could be problems using CURDATE() if the system date changes between the two calls to the function, don't know if Mysql would call both at the same time.

to get the nearest hour would be:

TIMESTAMPADD(MINUTE,
    ROUND(TIMESTAMPDIFF(MINUTE,CURDATE(),timestamp_column_name)/60)*60,
    CURDATE())

changing 60 by 15 would get the nearest 15 minutes interval, using SECOND you can get the nearest desired second interval, etc.

To get the previous hour use TRUNCATE() or FLOOR() instead of ROUND().

Hope this helps.

Answer 7

If you need to round just time to next hour you may use this:

SELECT TIME_FORMAT(
  ADDTIME(
    TIMEDIFF('16:15', '10:00'), '00:59:00'
  ),
  '%H:00:00'
)

Answer 8

I think this is the best way, since it also will use the least amount of resources-

date_add(date(date_completed), interval hour(date_completed) hour) as date_hr