43

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

Ben Lee
  • 52,489
  • 13
  • 125
  • 145
user1217917
  • 643
  • 1
  • 6
  • 8
  • possible duplicate of [How to round a DateTime in MySQL?](http://stackoverflow.com/questions/1921362/how-to-round-a-datetime-in-mysql) – Makoto Sep 22 '14 at 14:53

8 Answers8

52

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.

Ben Lee
  • 52,489
  • 13
  • 125
  • 145
44

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

rjhdby
  • 1,278
  • 13
  • 15
14

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
Code Commander
  • 16,771
  • 8
  • 64
  • 65
  • Tick from me because I just wanted to truncate – MikeKulls Jul 12 '13 at 05:28
  • `DATE_FORMAT` is slow? I've never heard of that. Do you have a reference/benchmark for that? I can't find anything on google about `DATE_FORMAT` having poor performance, and nothing indicating that calls to `MINUTE()` and `SECOND()` would be significantly faster than a call to `DATE_FORMAT`. In any case, shouldn't any performance difference be at the nanosecond level of magnitude -- essentially invisible/negligible for nearly every real-world purpose? – Ben Lee Sep 05 '13 at 19:15
  • @BenLee I expected that manipulating strings and parsing the dates would be much slower than `MINUTE`/`SECOND`, but after profiling the two cases that isn't true. Your solution is even potentially a bit faster than mine (perhaps due to mine calling `MINUTE` and `SECOND` twice?). However, my example that omits the seconds is faster than `DATE_FORMAT`. I'll update my answer. – Code Commander Oct 04 '13 at 00:57
  • For anyone else's info, to truncate to other minute intervals (eg 15 mins): select `timestamp,date_sub(timestamp,interval (minute(timestamp) % 15)* 60 +second(timestamp) second)`... – Steve Bennett Dec 02 '14 at 23:57
8

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.

Community
  • 1
  • 1
Ghostman
  • 6,042
  • 9
  • 34
  • 53
  • 3
    Be careful with this. The unix time approach fails for datetimes that fall between the daylight savings time changes. For example: `select FROM_UNIXTIME(UNIX_TIMESTAMP('2012-03-11 2:14:00') - MOD(UNIX_TIMESTAMP('2012-03-11 2:14:00'),300));` – Code Commander Aug 01 '12 at 01:35
7

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

pawzar
  • 113
  • 1
  • 5
2

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
Dane Thomas
  • 51
  • 2
1

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.

TMC
  • 471
  • 4
  • 5
1

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'
)
Peter Tretyakov
  • 3,380
  • 6
  • 38
  • 54
Mikko
  • 21
  • 4