How to subtract time from a timetamp field

Database

How to subtract time from a timetamp field

Hi,

I have a field which has time duration in seconds...It has to be subtracted from a timestamp field. So, the first step would be to convert it to a HH:MM:SS format. I am trying to write a case statement and it works fine when the time is less than 1 hour. If the duration is over one hour, the casting is not done properly.

cast((case
when duration/3600 = 0 then trim('00')||trim(':')||trim(duration/60)||trim(':')||trim(duration mod 60)
when duration/3600 > 0 then trim(duration/3600)||trim(':')||trim((duration mod 3600)/60)||trim(':')||trim((duration mod 3600) mod 60)
end) as time(0))

Is there any teradata function that directly converts the seconds into the HH:MM:SS format?
2 REPLIES
Senior Apprentice

Re: How to subtract time from a timetamp field

There's no need to typecast:
duration * interval '00:00:01' hour to second

Dieter

Re: How to subtract time from a timetamp field

Thanks very much for your reply. It is working properly