Skip navigation links

MySQL Forums :: General :: How to round a datetime field


Advanced Search

Re: How to round a datetime field
Posted by: Felix Geerinckx ()
Date: December 20, 2006 10:14AM

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.

Options: ReplyQuote


Subject Written By Posted
How to round a datetime field andrea rossi 12/20/2006 09:04AM
Re: How to round a datetime field Peter Brawley 12/20/2006 10:07AM
Re: How to round a datetime field Felix Geerinckx 12/20/2006 10:14AM
Re: How to round a datetime field andrea rossi 12/20/2006 10:31AM
Re: How to round a datetime field Saggi Malachi 03/17/2008 05:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.