CODEWITHSUNDEEP
mysqldatetimerounding
Iain Toft
Iain Toft

Reputation: 53

mysql 5.6 round datetime to nearest minute

I am trying to round a millisecond precision DATETIME, i.e. DATETIME(3) to the nearest minute. For example:

2020-07-27 17:33:59.954 => 2020-07-27 17:34:00.000
2020-07-27 17:42:58.799 => 2020-07-27 17:42:59.000

Upvotes: 1

Views: 72

Answers (3)

nbk
nbk

Reputation: 49373

Simply use CAST it as DATETIME

CREATE TABLE Table1
    (`dt` datetime(6))
;
    
INSERT INTO Table1
    (`dt`)
VALUES
    ('2020-07-27 17:33:59.954'),
    ('2020-07-27 17:42:58.799')
;

SELECT `dt` ,CAST(`dt` AS DATETIME)  FROM Table1

dt                         | CAST(`dt` AS DATETIME)
:------------------------- | :---------------------
2020-07-27 17:33:59.954000 | 2020-07-27 17:34:00   
2020-07-27 17:42:58.799000 | 2020-07-27 17:42:59

db<>fiddle here

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

mysql> select now(3), from_unixtime(floor((unix_timestamp(now(3))+30)/60)*60) as nearest_minute;
+-------------------------+---------------------+
| now(3)                  | nearest_minute      |
+-------------------------+---------------------+
| 2020-07-31 18:46:22.518 | 2020-07-31 18:46:00 |
+-------------------------+---------------------+

Upvotes: 2

Iain Toft
Iain Toft

Reputation: 53

Here is the rather unsatisfying solution I came up with:

CREATE FUNCTION timestamp_to_nearest_minute (t DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN 
    CASE 
    WHEN CAST(EXTRACT(MICROSECOND FROM t) AS DECIMAL(11,3)) / 1000.0 < 500
        THEN 
            DATE_SUB(t, INTERVAL CAST(EXTRACT(MICROSECOND FROM t) AS DECIMAL(11,3)) MICROSECOND)
        ELSE
            DATE_ADD(t, INTERVAL 1000000.0 - CAST(EXTRACT(MICROSECOND FROM t) AS DECIMAL(11,3)) MICROSECOND)
    END
;

Upvotes: 0

Related Questions