SQL to Calculate Total Duration in Redshift

user_16499465 0
Viewed 230

I have duration field in my redshift table as defined as varchar field and the data is in the below formate (hours:minutes:seconds:microseconds):

Example:

Duration
00:00:02.696000
00:04:04
00:01:04.127000
04:03:02.063000
00:03:08.300000
00:05:05
00:01:00.150000
32:01:03.200000

I would need find out the total duration. Some durations are more than 24 hours

Do we have any SQL that we can calculate the total duration in hours:minutes:seconds:microseconds ?

In above case the total is approx. --> 36:18:28 which is 36 hours and 18 minutes and 28 seconds and some micro seconds.

amazon-redshift sql
1 Answers

This is doable with SQL in Redshift. The strings you have are castable to the INTERVAL data type - which is a valid intermediate data type but values cannot be stored to tables as this type. Once cast these interval values can be summed. Here's a simple test case from your data;

CREATE TABLE NEW_TEST 
(dur   VARCHAR(100)
);

INSERT INTO NEW_TEST
VALUES
('00:00:02.696000'),
('00:04:04'),
('00:01:04.127000'),
('04:03:02.063000'),
('00:03:08.300000'),
('00:05:05'),
('00:01:00.150000'),
('32:01:03.200000');

select sum(dur::interval) from NEW_TEST;

The final SQL select statement produces the result:

 1 day 12:18:29.536

Now this is the right answer but not in the format you want. Unfortunately, the Redshift date/time formatting functions don't take intervals as an input so this will be a little manual. This statement makes what you are looking for:

select extract(hour from sum(dur::interval)) || ':' || 
    extract(min from sum(dur::interval)) - extract(hour from sum(dur::interval)) * 60 || ':' ||
    extract(sec from sum(dur::interval)) - extract(min from sum(dur::interval)) * 60 || '.' ||
    extract(usec from sum(dur::interval)) - extract(sec from sum(dur::interval)) * 1000000 as duration
from new_test;

This SQL select statement produces the result:

36:18:29.536000

Does this meet your need?

user_133506520
Related