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))
Tagged:
0
Best Answer
-
marcothesane - Select Field - Administrator
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 aTIME
type. Above 24 hours, it has the days, formatted asINTEGER
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
1
Answers
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
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