Adding time feilds

Database
Fan

Adding time feilds

Any one may help a novice on how to add to Time feilds?

example: '05:50:31' + '01:12:30' it's supposed to be '07:03:01' but I'm getting '06:62:61'

Thanks,
6 REPLIES
Teradata Employee

Re: Adding time feilds

Use INTERVAL

select cast('05:50:31' as interval hour to second) + cast('01:12:30' as interval hour to second)

result
7:03:01.000000
Fan

Re: Adding time feilds

It works, thanks Jeff
N/A

Re: Adding time feilds

I am trying a similar opperation of subtracting an interval from a timestamp, but I'm getting a syntax error when I try to add days or years into the Interval:

SELECT EXTRACT( SECOND FROM( TIMESTAMP '2008-06-01 00:00:10.000000' - CAST( '06-01 00:00:05' AS INTERVAL DAY TO SECOND)))

If there is a better way to do this, my goal is that I have 2 Timestamp fields in the format of 'YYYY-MM-DD HH:MM:SS.000000' where the zeroes are miliseconds. I'm looking to subtract the two timestamps and determine if at a precision of seconds the two timestamps are equal, or if not but how many seconds they are different. The above is a test to see if the syntax/algorithm of how I'm trying to do this works.

I hope that makes sense.

Thanks for any help in advance.

-Matt

Re: Adding time feilds

I am having the extact opposite problem. I can not subtract from a time type field.

I have two time fields (starttime and endtime). I’m wanting to subtract the start from the end and I keep receiving the error “Invalid operation on an ANSI datetime or interval value.

Starttime = 17:41:05
endTime = 18:45:14

Help. I’ve tried the extract function, I’ve tried the convert function but I keep getting the same error.
WAQ
Enthusiast

Re: Adding time feilds

try this:
select cast(cast(endTime as time) - cast(Starttime as time) as time)
Senior Apprentice

Re: Adding time feilds

You have to define the resulting datatype:

select (endTime - startTime) hour to second

Dieter