TIMESTAMPDIFF in HH:MM:SS

slc1axj · July 2022

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 · July 2022

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 · July 2022

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 · July 2022

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

We're Moving!

Create My New Community Account Now

TIMESTAMPDIFF in HH:MM:SS

Best Answer

Answers

Leave a Comment