Teradata Interval Field Overflow

by

Interval is the datatype in Teradata which represents duration between 2 time components. We can add INTERVAL to DATE/TIMESTAMP column values to get to new DATE/TIMESTAMP. Also whenever we subtract two timestamp in Teradata, output is of INTERVAL type. Just like any other data type INTERVAL too has permissible range and if the value exceeds it we get "Interval Field Overflow" error.

Now you know the real reason of Interval Field Overflow error , let us see few examples which explains it.
Create a dummy table with some data:

create volatile table dummy_ts
 (
 start_ts timestamp ,
 end_ts timestamp 
 )
on commit preserve rows;

insert into dummy_ts select    '2016-08-10 22:12:11','2016-08-12 20:17:21';
insert into dummy_ts select    '2015-01-14 20:17:21','2017-05-14 12:16:54';
insert into dummy_ts select    '2016-08-17 20:17:21','2019-01-16 18:17:21';
insert into dummy_ts select    '2015-01-22 20:17:21','2015-07-22 22:45:11';

select * from dummy_ts;

 start_ts                      end_ts
 2015-01-14 20:17:21.000000    2017-05-14 12:16:54.000000
 2016-08-17 20:17:21.000000    2019-01-16 18:17:21.000000
 2016-08-10 22:12:11.000000    2016-08-12 20:17:21.000000
 2015-01-22 20:17:21.000000    2015-07-22 22:45:11.000000

Whenever we subtract 2 timestamp columns we must specify the INTERVAL type format of the output else we will get "Invalid operation for DateTime or Interval" error. 

select start_ts,end_ts, end_ts - start_ts from dummy_ts;

Error: "Invalid operation for DateTime or Interval"

Let's specify the DAY difference between 2 timestamp values

select start_ts,end_ts, end_ts - start_ts DAY as Day_Diff from dummy_ts;

Error: "Interval Field Overflow"

Since DAY means DAY(1) by default, we have to specify the precision to which we are expecting result. We can maximum have difference of 9999 DAYS between 2 timestamp in INTERVAL data type. In very exceptional case, you may need INTERVAL more than 9999 days. You can specify DAY(4) to specify DAY difference in the output with precision upto 4 digits. Hence 9999 is max value.

So now let's subtract 2 timestamp column and get the difference:

select  
start_ts,end_ts, 
end_ts - start_ts DAY(4) as Day_Diff,
type(Day_Diff) from dummy_ts;
 
 start_ts               end_ts                Day_Diff    Type(Day_Diff)
 2015-01-14 20:17:21    2017-05-14 12:16:54     851     INTERVAL DAY(4)
 2016-08-17 20:17:21    2019-01-16 18:17:21     882     INTERVAL DAY(4)
 2016-08-10 22:12:11    2016-08-12 20:17:21     2       INTERVAL DAY(4)
 2015-01-22 20:17:21    2015-07-22 22:45:11     181     INTERVAL DAY(4)

To get output from DAY to HOUR

select start_ts,end_ts, 
end_ts - start_ts DAY(4) to HOUR as Day_Diff, 
type(Day_Diff) from dummy_ts;

start_ts            end_ts        Day_Diff          Type(Day_Diff)
2015-01-14 20:17:21 2017-05-14 12:16:54   850 16    INTERVAL DAY(4) TO HOUR
2016-08-17 20:17:21 2019-01-16 18:17:21   881 22    INTERVAL DAY(4) TO HOUR
2016-08-10 22:12:11 2016-08-12 20:17:21     1 22    INTERVAL DAY(4) TO HOUR
2015-01-22 20:17:21 2015-07-22 22:45:11   181 02    INTERVAL DAY(4) TO HOUR

To get output from DAY to MINUTE

select start_ts,end_ts, 
end_ts - start_ts DAY(4) to MINUTE as Day_Diff, 
type(Day_Diff) from dummy_ts;

2015-01-14 20:17:21 2017-05-14 12:16:54   850 15:59 INTERVAL DAY(4) TO MINUTE
2016-08-17 20:17:21 2019-01-16 18:17:21   881 22:00 INTERVAL DAY(4) TO MINUTE
2016-08-10 22:12:11 2016-08-12 20:17:21     1 22:05 INTERVAL DAY(4) TO MINUTE
2015-01-22 20:17:21 2015-07-22 22:45:11   181 02:28 INTERVAL DAY(4) TO MINUTE

To get output from DAY to SECOND

select start_ts,end_ts, 
end_ts - start_ts DAY(4) to SECOND as Day_Diff, 
type(Day_Diff) from dummy_ts;

2015-01-14 20:17:21 2017-05-14 12:16:54   850 15:59:33.000000   INTERVAL DAY(4) TO SECOND(6)
2016-08-17 20:17:21 2019-01-16 18:17:21   881 22:00:00.000000   INTERVAL DAY(4) TO SECOND(6)
2016-08-10 22:12:11 2016-08-12 20:17:21     1 22:05:10.000000   INTERVAL DAY(4) TO SECOND(6)
2015-01-22 20:17:21 2015-07-22 22:45:11   181 02:27:50.000000   INTERVAL DAY(4) TO SECOND(6)

Important point to note here is the milliseconds component of interval. It has taken SECOND(6) as default as the timestamp format defined in the table. If you don't want milliseconds then you have to try other alternative. You cannot truncate seconds in INTERVAL datatype.

select start_ts,end_ts, 
end_ts - start_ts DAY(4) to SECOND(0) as Day_Diff, 
type(Day_Diff) from dummy_ts;

Error: "Interval Field Overflow"

You can explicitly convert TIMESTAMP(6) to TIMESTAMP(0) and then apply INTERVAL SECOND to get desired output. To get rid of milliseconds portion or to want just the SECOND you can try something like:

select start_ts,end_ts, 
  cast(cast(end_ts as char(19)) as timestamp(0))- 
  cast(cast(start_ts  as char(19)) as timestamp(0)) DAY(4) to SECOND(0) as Day_Diff, 
type(Day_Diff) from dummy_ts;
                                                
2015-01-14 20:17:21 2017-05-14 12:16:54   850 15:59:33  INTERVAL DAY(4) TO SECOND(0)
2016-08-17 20:17:21 2019-01-16 18:17:21   881 22:00:00  INTERVAL DAY(4) TO SECOND(0)
2016-08-10 22:12:11 2016-08-12 20:17:21     1 22:05:10  INTERVAL DAY(4) TO SECOND(0)
2015-01-22 20:17:21 2015-07-22 22:45:11   181 02:27:50  INTERVAL DAY(4) TO SECOND(0)

Also before applying any other function on INTERVAL datatype you may want to convert it into compatible datatype first. Since most of the functions are not applicable on INTERVAL you may get INVALID OPERATION error. Like I tried to apply TRIM function to INTERVAL output in the query.

select start_ts,end_ts, 
trim(cast(cast(end_ts as char(19)) as timestamp(0))
- cast(cast(start_ts  as char(19)) as timestamp(0)) DAY(4) to SECOND(0)) as Day_Diff, 
type(Day_Diff) from dummy_ts;

Error: "Invalid operation for DateTime or Interval"

I can convert INTERVAL to VARCHAR and then may apply String functions on it. Like below:

select start_ts,end_ts, 
trim(cast((end_ts - start_ts DAY(4) to SECOND(6)) as varchar(30))) as Day_Diff,
type(Day_Diff) from dummy_ts;
                                                             
start_ts                    end_ts                    Day_Diff              Type(Day_Diff)
2015-01-14 20:17:21.000000  2017-05-14 12:16:54.000000  850 15:59:33.000000 VARCHAR(30)
2016-08-17 20:17:21.000000  2019-01-16 18:17:21.000000  881 22:00:00.000000 VARCHAR(30)
2016-08-10 22:12:11.000000  2016-08-12 20:17:21.000000  1 22:05:10.000000   VARCHAR(30)
2015-01-22 20:17:21.000000  2015-07-22 22:45:11.000000  181 02:27:50.000000 VARCHAR(30)

So you can see in above example, INTERVAL is converted to VARCHAR and then we can apply string functions like TRIM or SUBSTRING etc on it.
Now you know why do we get "Interval Field Overflow" or "Invalid operation for DateTime or Interval" error.
Hope this helps.

Leave a Comment