TIMESTAMPDIFF in HH:MM:SS

slc1axjslc1axj Vertica Customer

Can anyone suggest how to figure this out? I'm trying to bring this into Excel as time (integer) rather than varchar because it needs to be subtotaled. Here's what I have but it's bringing into Excel as varchar:
CAST(TIMESTAMPDIFF(s,a.START_TIME_UTC,a.END_TIME_UTC)::VARCHAR || ' sec' AS INTERVAL))

Best Answer

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    Subtracting two timestamps from each other returns an INTERVAL DAY TO SECOND. Up to 23 hours, 59 minutes and 59 seconds, that data type formats like a TIME type. Above 24 hours, it has the days, formatted as INTEGER in front, separated by one space.

    Behaviour test:

    WITH
    indata(to_ts,from_ts) AS (
              SELECT TIMESTAMP '2021-10-29 07:06:32',TIMESTAMP '2021-10-10 14:11:13'
    UNION ALL SELECT TIMESTAMP '2021-10-29 00:00:14',TIMESTAMP '2021-10-29 00:00:00'
    )
    SELECT
      to_ts - from_ts        AS as_interval
    ,(to_ts - from_ts)::TIME AS cast_to_time
    FROM indata;
    -- out  as_interval | cast_to_time 
    -- out -------------+--------------
    -- out  18 16:55:19 | 16:55:19
    -- out  00:00:14    | 00:00:14
    

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file