TIMESTAMPDIFF in HH:MM:SS

slc1axj

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))

marcothesane

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

slc1axj

Sorry - only 1 parentheses at end of statement:
CAST(TIMESTAMPDIFF(s,a.START_TIME_UTC,a.END_TIME_UTC)::VARCHAR || ' sec' AS INTERVAL) as hhmmss

Bryan_H

If you need an integer difference in seconds from UTC times, try the following:
SELECT (EXTRACT(EPOCH from a.END_TIME_UTC) - EXTRACT(EPOCH from a.START_TIME_UTC)) as hhmmss
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/EXTRACT.htm