We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Round datetime down to nearest 30 minute interval — Vertica Forum
Options

Round datetime down to nearest 30 minute interval

slc1axjslc1axj Vertica Customer ✭
in General Discussion

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

  • marcothesanemarcothesane - Select Field - Administrator

    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
  • marcothesanemarcothesane - Select Field - Administrator

    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
  • slc1axjslc1axj Vertica Customer ✭

    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 - Select Field - 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
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...