Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Round datetime down to nearest 30 minute interval

In Vertica, how can I create a formula to take a date time and round down to the next 30 minute interval?

E.g. 9/21/2021 09:45 am would be 9:30

Answers

  • I still have to find a similarly easy way to do that in any other DBMS than Vertica.

    Try this:

    WITH
    -- in data ...
    indata(ts) AS (
              SELECT TIMESTAMP '2021-08-27 21:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:45:45'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:45:45'
    UNION ALL SELECT TIMESTAMP '2021-08-27 23:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:45:45'
    )
    SELECT
      ts  
    , TIME_SLICE(ts,30,'MINUTE','END') AS ts
    FROM indata
    ;
    -- out          ts          |         ts          
    -- out ---------------------+---------------------
    -- out  2021-08-27 21:00:00 | 2021-08-27 21:30:00
    -- out  2021-08-27 21:15:15 | 2021-08-27 21:30:00
    -- out  2021-08-27 21:30:30 | 2021-08-27 22:00:00
    -- out  2021-08-27 21:45:45 | 2021-08-27 22:00:00
    -- out  2021-08-27 22:00:00 | 2021-08-27 22:30:00
    -- out  2021-08-27 22:15:15 | 2021-08-27 22:30:00
    -- out  2021-08-27 22:30:30 | 2021-08-27 23:00:00
    -- out  2021-08-27 22:45:45 | 2021-08-27 23:00:00
    -- out  2021-08-27 23:00:00 | 2021-08-27 23:30:00
    -- out  2021-08-27 13:15:15 | 2021-08-27 13:30:00
    -- out  2021-08-27 13:30:30 | 2021-08-27 14:00:00
    -- out  2021-08-27 13:45:45 | 2021-08-27 14:00:00
    
  • And the other way round ...

    WITH
    -- in data ...
    indata(ts) AS (
              SELECT TIMESTAMP '2021-08-27 21:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 21:45:45'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 22:45:45'
    UNION ALL SELECT TIMESTAMP '2021-08-27 23:00:00'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:15:15'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:30:30'
    UNION ALL SELECT TIMESTAMP '2021-08-27 13:45:45'
    )
    SELECT
      ts
    , TIME_SLICE(ts,30,'MINUTE','START') AS ts
    FROM indata
    ;
    -- out          ts          |         ts          
    -- out ---------------------+---------------------
    -- out  2021-08-27 21:00:00 | 2021-08-27 21:00:00
    -- out  2021-08-27 21:15:15 | 2021-08-27 21:00:00
    -- out  2021-08-27 21:30:30 | 2021-08-27 21:30:00
    -- out  2021-08-27 21:45:45 | 2021-08-27 21:30:00
    -- out  2021-08-27 22:00:00 | 2021-08-27 22:00:00
    -- out  2021-08-27 22:15:15 | 2021-08-27 22:00:00
    -- out  2021-08-27 22:30:30 | 2021-08-27 22:30:00
    -- out  2021-08-27 22:45:45 | 2021-08-27 22:30:00
    -- out  2021-08-27 23:00:00 | 2021-08-27 23:00:00
    -- out  2021-08-27 13:15:15 | 2021-08-27 13:00:00
    -- out  2021-08-27 13:30:30 | 2021-08-27 13:30:00
    -- out  2021-08-27 13:45:45 | 2021-08-27 13:30:00
    
  • Thanks - I'll have to leave this formula in Excel rather than in the query... uggh - formula in Excel: TEXT(IF(AM2="America/New_York",FLOOR(AL2-5/24,"0:30"),IF(AM2="America/Chicago",FLOOR(AL2-6/24,"0:30"),IF(AM2="America/Phoenix",FLOOR(AL2-7/24,"0:30"), IF(AM2="America/Denver",FLOOR(AL2-7/24,"0:30"),IF(AM2="America/Los_Angeles",FLOOR(AL2-8/24,"0:30")))))),"hh:mm")

  • Jim_KnicelyJim_Knicely Administrator

    Building upon @marcothesane's great solution, @slc1axj , maybe you would want to create a User-Defined SQL Function to make querying a whole lot easier!

    Example:

    verticademos=> CREATE OR REPLACE FUNCTION round_down_to_nearest_30_min(d TIMESTAMP) RETURN TIMESTAMP
    verticademos-> AS
    verticademos-> BEGIN
    verticademos->   RETURN TIME_SLICE(d, 30, 'MINUTE', 'START');
    verticademos-> END;
    CREATE FUNCTION
    
    verticademos=> SELECT round_down_to_nearest_30_min('9/21/2021 09:45 am'::TIMESTAMP);
     round_down_to_nearest_30_min
    ------------------------------
     2021-09-21 09:30:00
    (1 row)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.