While calculating measures you may want to convert seconds to TIME. Say you want to know how many HOURS,MINUTES & Seconds constitute this number. Example you may want to convert 500 seconds to hh:mi:ss format. This can be done easily by using "INTERVALs". Let's quickly see how to do it
select 5000 *CAST( INTERVAL '1' SECOND AS INTERVAL Day TO second(0)) ; OUTPUT: 0 01:23:20
This means 5000 seconds means 1 hour 23 minutes and 20 seconds.
Another way of writing same command:
select 5000 *INTERVAL '00 00:00:01' DAY TO second ; OUTPUT: 0 01:23:20
Let's increase the value of number (i.e. seconds)
select 500000 *CAST( INTERVAL '1' SECOND AS INTERVAL Day TO second(0)) ; OUTPUT: 5 18:53:20
This means 500000 seconds constitutes 5 Days 18 Hours 53 Minutes and 20 Seconds.
You can change the INTERVAL RANGE to get output in desired format. Like I just want output in hh:mi:ss format. Then we can run below query:
select 500000 *CAST( INTERVAL '1' SECOND AS INTERVAL HOUR TO second(0)) ; OUTPUT: 138:53:20
This means 500000 seconds constitutes 138 Hours 53 Minutes and 20 Seconds.
There can be another use-case where you already have "TIME" data stored as number.
Like 65320 i.e. 6th Hour 53rd Minute & 20th second.
Now you just want to change this number to proper time format. Then you can use below command.
If you just want to change the appearance of number then try below command:
select 65320 (format '99:99:99') OUTPUT: 06:53:20
But the return data type is INTEGER only. If you want "actual" TIME column then cast it to TIME(0)
select cast(trim(65320 (format '99:99:99')) as TIME(0)) OUTPUT: 06:53:20