Subtraction of Timestamps teradata

Question

I am trying to find difference between 2 timestamps in teradata. I am using the following code:

(date1-date2)day(4) to second  as time_diff

This is giving the error: Interval Field Overflow. What could be reason for that? Is there some other way to calculate the difference between 2 timestamps?

And when I am using this:

case when(((date2+ INTERVAL '72' hour )-date1) day(4) to second)>0 then '<72 hrs'

then the error i am getting is Invalid operation for DateTime or Interval. Please help

Solution 1

The range of DAY(4) TO SECOND(6) is -9999 23:59:59.999999 to 9999 23:59:99.999999. For comparison sake you can subtract 1987-01-01 00:00:00 from CURRENT_TIMESTAMP(0) and still be within the acceptable range of values.

If your timestamps differ by more than roughly 27.39 years you will need to consider a casting the values to DATE first to determine the number of days between the two values. Then extract timestamp values and compare them while taking into account that one may fall on the wrong side of midnight.

Solution 2

I think it's because your dates where too far from each other so their interval 'day(4) to second' overflows.

In attached script I test whether the difference is bigger than one year. If yes it's obvious that interval between the dates is bigger than 72 hours. Else I continue with hour interval. Year has give or take 8.8k so HOUR(4) is good enough.

Dates with 20 years of gap

SELECT       CASE WHEN   EXTRACT ( YEAR FROM  ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2000-01-02 12:00:00'))YEAR(4) TO MONTH    )  !=  0 THEN '>72 hours' 
                    WHEN   EXTRACT ( YEAR FROM  ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2000-01-02 12:00:00'))YEAR(4) TO MONTH    ) = 0 THEN
                        CASE WHEN ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2000-01-02 12:00:00'))HOUR(4) < 72 THEN '< 72 hours' END
                 END

Dates with 1 day of gap

SELECT       CASE WHEN   EXTRACT ( YEAR FROM  ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2020-01-02 12:00:00'))YEAR(4) TO MONTH    )  !=  0 THEN '>72 hours' 
                    WHEN   EXTRACT ( YEAR FROM  ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2020-01-02 12:00:00'))YEAR(4) TO MONTH    ) = 0 THEN
                        CASE WHEN ((TIMESTAMP '2000-01-01 00:00:00'+ INTERVAL '72' hour ) - (TIMESTAMP '2020-01-02 12:00:00'))HOUR(4) < 72 THEN '< 72 hours' END
                 END

Clearly in your script you would replace TIMESTAMP 'YYYY-MM-DD