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:
And the other way round ...
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")
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: