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
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
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)