Timestamp difference in seconds

Database

Timestamp difference in seconds

Hello,

I would like to calculate the difference between two timestamp(6) and have the result in number of seconds.
I am a little lost with all the conversion formulas existing in Teradata.
Has somebody already calculated this kind of thing ?
Thanks a lot for your help.
Regards,
Emilie
4 REPLIES
Teradata Employee

Re: Timestamp difference in seconds


Select (Timestamp'2008-12-10 10:12:00.000000' - Timestamp'2008-12-10 10:10:12.000000') Second(4,6)

This example subtracts the two timestamp literals and returns an Interval Second of 108.000000 .
The result can have upto 4 digits for Seconds and 6 digits for fractional seconds.

Re: Timestamp difference in seconds

Hello Shaw,

Thanks a lot for your response.
I have tried to implement your solution but I have an error message :

Select (CAST(date AS TIMESTAMP(6)) - CAST(ACAP_LogDateTime AS TIMESTAMP(6))) Second(4,6)
from tst_data.ACAP_AssistCaseHistoryRep

7453: Interval field overflow.

From my side, I have thought to this solution :

select
(
(extract(day FROM (CAST(date AS TIMESTAMP(6)) - CAST(ACAP_LogDateTime AS TIMESTAMP(6)) day(4) TO second)) * 86400) +
(extract(hour FROM (CAST(date AS TIMESTAMP(6)) - CAST(ACAP_LogDateTime AS TIMESTAMP(6)) day(4) TO second)) * 3600) +
(extract(minute FROM (CAST(date AS TIMESTAMP(6)) - CAST(ACAP_LogDateTime AS TIMESTAMP(6)) day(4) TO second)) * 60) +
(extract(second FROM (CAST(date AS TIMESTAMP(6)) - CAST(ACAP_LogDateTime AS TIMESTAMP(6)) day(4)TO second)))
)
from tst_data.ACAP_AssistCaseHistoryRep

But :
- I think we can find a more simple formula
- I want to round the result to the second and I don't sucess to do it (for the moment, the result is given with the fractionned second : 13321933.684000 and I would like just the result in number of seconds : 13321934)

Regards,
Emilie
Enthusiast

Re: Timestamp difference in seconds

Hi ,

I tried this with timestamp(6):

sel extract(second from timestamp '2008-08-27 05:24:44.470000+00:00') -
extract(second from timestamp '2008-08-27 05:24:31.240000+00:00')
Teradata Employee

Re: Timestamp difference in seconds

I think this issue has been discussed before; please search the archives.

Two examples of rounding a decimal:

Select Cast(3.178 as Decimal(18, 0));

This example returns 3.

Select Cast(3.678 as Decimal(18, 0));

This example returns 4.