The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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