Mysql Question posted in
The official MySQL documentation can be found here.

How can I round up a timestamp to the nearest 5 minutes in MySQL?

For example,

2024-09-09 13:00:00 will be 2024-09-09 13:05:00
2024-09-09 13:00:50 will be 2024-09-09 13:05:00
2024-09-09 13:01:00 will be 2024-09-09 13:05:00
2024-09-09 13:04:00 will be 2024-09-09 13:05:00
2024-09-09 13:04:59 will be 2024-09-09 13:05:00
2024-09-09 13:05:00 will be 2024-09-09 13:10:00

The answers I find are generally about rounding down, but I want to round up.

Thanks

I tried to use various methods, and adapt the round down to round up, but nothing worked.

Tags:

2

Answers


  1. 0

    Here is the query that will round up a DATETIME to the next 5-minute mark:

        SELECT 
    -- Add 4 minutes and 59 seconds to the original time to round up
    DATE_ADD(DATE(datetime_column), 
        INTERVAL (FLOOR((HOUR(datetime_column) * 60 + MINUTE(datetime_column) + 4) / 5) * 5) MINUTE) 
    AS rounded_datetime
FROM 
    your_table;

    Explanation:

    1. HOUR(datetime_column) * 60 + MINUTE(datetime_column): This converts the hour and minute portion of the datetime to total minutes.
    2. + 4: Adds 4 minutes so that any time within a 5-minute interval will round up to the next interval.
    3. FLOOR((minutes + 4) / 5) * 5: Divides the total minutes by 5 to find the nearest 5-minute interval, then rounds down using FLOOR().
    4. DATE_ADD(): Adds the calculated number of minutes to the date.

    Example Output:

    2024-09-09 13:00:00 becomes 2024-09-09 13:05:00

    2024-09-09 13:00:50 becomes 2024-09-09 13:05:00

    Login or Signup to reply.
  2. 0 
    SELECT original_ts, 
       FROM_UNIXTIME(((UNIX_TIMESTAMP(original_ts) + 300) DIV 300) * 300) rounded_ts
FROM table_name;

    300 is the number of seconds in a 5-minute interval.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search