TIMESTAMPDIFF in HH:MM:SS

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