Re: How to round a datetime field

[Felix Geerinckx]

andrea rossi Wrote:

> I need to round a datetime field to the closer quarter of hour.
>
> Example
> 2006-10-10 14:15:01 // it must return 2006-10-10 14:30:00
> 2006-10-10 14:14:59 // it must return 2006-10-10 14:00:00

It seems to me that your requirement contradicts your example.
The following rounds DATETIMEs to the closest half hour, but it can be easily adapted for other situations.

USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (dttm DATETIME NOT NULL PRIMARY KEY);
INSERT INTO foo (dttm) VALUES ('2006-10-10 14:15:01'), ('2006-10-10 14:14:59'), 
                              ('2006-10-10 14:30:01'), ('2006-10-10 14:45:01');

SELECT 
    dttm AS ORIGINAL,
    dttm + INTERVAL
    CASE
        WHEN EXTRACT(MINUTE_SECOND FROM dttm) BETWEEN    0 AND 1459 THEN +    0 - TIME_TO_SEC(EXTRACT(MINUTE_SECOND FROM dttm))
        WHEN EXTRACT(MINUTE_SECOND FROM dttm) BETWEEN 1500 AND 2959 THEN + 1800 - TIME_TO_SEC(EXTRACT(MINUTE_SECOND FROM dttm))
        WHEN EXTRACT(MINUTE_SECOND FROM dttm) BETWEEN 3000 AND 4459 THEN + 1800 - TIME_TO_SEC(EXTRACT(MINUTE_SECOND FROM dttm))
        WHEN EXTRACT(MINUTE_SECOND FROM dttm) BETWEEN 4500 AND 5959 THEN + 3600 - TIME_TO_SEC(EXTRACT(MINUTE_SECOND FROM dttm))
        ELSE NULL
    END SECOND AS CHANGED
FROM foo;

Or you can create a stored function:

DELIMITER //

CREATE FUNCTION rounddatetime(din DATETIME) RETURNS DATETIME
BEGIN
    DECLARE ms, secs INT;
    
    SET ms =  EXTRACT(MINUTE_SECOND FROM din);
    
    SET secs =   
        (CASE 
            WHEN ms BETWEEN    0 AND 1459 THEN +    0 - TIME_TO_SEC(ms)
            WHEN ms BETWEEN 1500 AND 2959 THEN + 1800 - TIME_TO_SEC(ms)
            WHEN ms BETWEEN 3000 AND 4459 THEN + 1800 - TIME_TO_SEC(ms)
            WHEN ms BETWEEN 4500 AND 5959 THEN + 3600 - TIME_TO_SEC(ms)
        END);
        
        RETURN din + INTERVAL secs SECOND;
END;

//

DELIMITER ;

SELECT
    dttm AS ORIGINAL,
    rounddatetime(dttm) AS CHANGED
FROM foo;

--
felix
Please use BBCode to format your messages in this forum.