Convert number to TIME in Teradata
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
This means 5000 seconds means 1 hour 23 minutes and 20 seconds.
Another way of writing same command:
Let’s increase the value of number (i.e. seconds)
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:
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:
But the return data type is INTEGER only. If you want “actual” TIME column then cast it to TIME(0)