Round datetime down to nearest 30 minute interval

slc1axj

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

marcothesane

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

marcothesane

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

slc1axj

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_Knicely

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)