How to Round Timestamps in MySQL

Rounding or truncating timestamps are especially useful when you're grouping by time. If you are rounding by year or date, you can use the corresponding functions:

SELECT YEAR(now());  -- or DATE();

However, care must be done if you are grouping by months. Using MONTH() will, for example, make November 2018 and November 2017 both just translate to "11". If that is what you want, then you can use MONTH(). However, if you want to distinguish between months of different years, you need to use DATE_FORMAT():

SELECT date_format(now(),'%Y-%m'); -- round to the month
SELECT date_format(now(),'%Y-%m-%d'); -- round to the day
SELECT date_format(now(),'%Y-%m-%d %H'); -- round to the hour
SELECT date_format(now(),'%Y-%m-%d %H:%i'); -- round to the minute