mysql 日期小时,MYSQL日期时间到最近一小时

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

解决方案

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.