Round datetime down to nearest 30 minute interval
slc1axj
Vertica Customer ✭
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
0
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:00And 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:00Thanks - 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")
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)