Rounding a Datetime Value in MySQL to the Nearest Minute

When you store timestamps in MySQL, the values often include seconds even when your report, dashboard, or grouping logic only needs minute-level precision. Rounding a DATETIME value to the nearest minute is useful for analytics, event logs, scheduling screens, and any query where 2026-07-10 09:14:29 and 2026-07-10 09:14:31 should not be treated the same way.

The easiest approach is to convert the datetime to a Unix timestamp, divide by 60 seconds, round it, and convert it back. This lets MySQL do the math in seconds while still returning a readable datetime value.

SELECT
    created_at,
    FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(created_at) / 60) * 60) AS rounded_to_minute
FROM events;

Here is what happens in that expression. UNIX_TIMESTAMP(created_at) converts the datetime to seconds. Dividing by 60 turns seconds into minutes. ROUND() chooses the nearest whole minute. Multiplying by 60 converts the rounded minute back into seconds, and FROM_UNIXTIME() turns the result back into a datetime.

For example, a value ending in :29 rounds down to the current minute, while a value ending in :30 or later rounds up to the next minute. That behavior is usually what people expect when they say “nearest minute,” but it is different from truncating. If you want to always round down, use FLOOR(). If you want to always round up, use CEIL().

SELECT
    created_at,
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(created_at) / 60) * 60) AS rounded_down,
    FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(created_at) / 60) * 60) AS rounded_up
FROM events;

You can also use the rounded value for grouping. This is handy when building per-minute charts from rows that arrive at irregular seconds.

SELECT
    FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(created_at) / 60) * 60) AS minute_bucket,
    COUNT(*) AS total_events
FROM events
GROUP BY minute_bucket
ORDER BY minute_bucket;

For large tables, avoid wrapping indexed datetime columns inside functions in the WHERE clause when possible. Filter by a normal datetime range first, then round the remaining rows in the SELECT list or a derived query. That keeps MySQL closer to an index-friendly plan and makes the reporting query easier to tune later.

One caution: if your application stores time zones inconsistently, rounding may make reporting confusion worse. Normalize your stored timestamps, document whether they are UTC or local time, and only then apply minute bucketing in queries or views.

If your MySQL-backed website handles sensitive user activity, login events, payment flows, or customer records, this is also a good moment to review your broader security posture. ViWeb Technology offers Web Security Service support for teams that want stronger protection around production websites, hosting, and application operations.