Introducing PopSQL + dbt. The first SQL editor with built-in dbt support.Read more →

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 useDATE_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

Ready for a modern SQL editor?