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.