Format number of seconds as interval HH:MM:SS in Redshift

user_10241576 0
Viewed 729

Im working with quantities of times represented as an absolute number of seconds in my data pipeline. It's fairly trivial do something like

select 42602 * interval '1 second';

which return 11:50:02 the proper answer.

However when i run the same logic on actual data

select on_call, 
    on_call::int * interval '1 second'
from dev_isaac.agency_time_report
order by agent_name, day_of;

The resulting interval column instead populates as enter image description here

Why is there a difference in how redshift handles this and is there a good way around this without just manually calculating each piece.

amazon-redshift amazon-web-services
1 Answers

A bit convoluted... but this could work for HH:MM conversion:

(DATEDIFF(second,'2011-12-31 8:30:00','2011-12-31 20:30:15')/(60*60))::VARCHAR || ':' || MOD(DATEDIFF(second,'2011-12-31 8:30:00','2011-12-31 20:30:15'),(60*60))

Here is the solution we landed on:

LTRIM(DATEADD(seconds,DATEDIFF(seconds,order_create,placed_time),'1900-01-01 00:00:00'),'1900-01-01')::time
user_32009500
Related