Teradata Date Time - Part 9 DAY-TIME interval

We know that there are two general categories of INTERVAL data type.
 

• Year- Month Interval.
   

• Day-time Interval.

Day-time interval can be further segregated in two categories:

• Single categories- (DAY,HOUR,MINUTE,SECONDS) only one unit of measurement
   

INTERVAL DAY(n)      (-9999 to +9999 days) 

INTERVAL HOUR(n)     (-9999 to +9999 hours)

INTERVAL MINUTE(n)   (-9999 to +9999 minutes)

INTERVAL SECOND(n,m)  (9999.999999 seconds)

Values of N ranges from 1 to 4

Values of M ranges from 0 to 6

Default values for N is 2 and M is 6.

• Complex Categories  - Two or more units of measurement

INTERVAL DAY(n)TO HOUR(n)

INTERVAL DAY(n) TO MINUTE(n)

INTERVAL DAY(n) TO SECOND(n,m)

INTERVAL HOUR(n) TO MINUTE(n)

INTERVAL HOUR(n) TO SECOND(n,m)

INTERVAL MINUTE(n) TO SECOND(n,m)

Interval Type	Description	Internal Storage	Range
DAY	Represents a number of days	SMALLINT	-9999 to +9999 days
DAY TO HOUR	Represents a number of days and hours	SMALLINT	-9999 to +9999 days
		SMALLINT	-9999 to +9999 hours
DAY TO MINUTE	Represents a number of days, hours and minutes	SMALLINT	-9999 to +9999 days
		SMALLINT	-9999 to +9999 hours
		SMALLINT	-9999 to +9999 min.
DAY TO SECOND	Represents a number of days, minutes, hours and seconds	SMALLINT	-9999 to +9999 days
		SMALLINT	-9999 to +9999 hours
		SMALLINT	-9999 to +9999 min.
		DEC(8,6)	+-9999.999999 seconds
HOUR	Represents a number of hours	SMALLINT	-9999 to +9999 hours
HOUR TO MINUTE	Represents a number of hours and minutes	SMALLINT	-9999 to +9999 hours
		SMALLINT	-9999 to +9999 min.
HOUR TO SECOND	Represents a number of hours, minutes and seconds	SMALLINT	-9999 to +9999 hours
		SMALLINT	-9999 to +9999 min.
		DEC(8,6)	+-9999.999999 seconds
MINUTE	Represents a number of minutes	SMALLINT	-9999 to +9999 min.
MINUTE TO SECOND	Represents a number of minutes and seconds	SMALLINT	-9999 to +9999 min.
		DEC(8,6)	+-9999.999999 seconds
SECOND	Represents a number of seconds	DEC(8,6)	+-9999.999999 seconds

When representing literals the character strings should conform with the literal types. Parser will check the precision and assign a proper data type . The parser will scan a  day-time literal to see if it conforms to the expected syntax, then assign a working interval data type to it. Otherwise, an error is generated.

.

Examples:

select

INTERVAL '3' DAY,

INTERVAL -'9999' DAY,

INTERVAL '12' HOUR,

INTERVAL '234' MINUTE,

INTERVAL '3421.445554' SECOND,

INTERVAL '33 12' DAY TO HOUR,

INTERVAL '33 12:12' DAY TO MINUTE,

INTERVAL '12 12:12:12.123421' DAY TO SECOND, --> will assign the data type DAY TO SECOND(6)

INTERVAL '12:34' HOUR TO MINUTE,

INTERVAL '13:12:45' HOUR TO SECOND, --> will assign the data type HOUR TO SECOND(0)

INTERVAL '34:12' MINUTE TO SECOND

3

-9999

12

234

3421.445554

33 12

33 12:12

12 12:12:12.123421

12:34

13:12:45

34:12

• Interval date time operation

SELECT INTERVAL '03 05:23' DAY TO MINUTE  + INTERVAL '16:23:32.123421' HOUR TO SECOND;

    

( 3 05:23+ 16:23:32.123421)

3 21:46:32.123421

SELECT TYPE(INTERVAL '03 05:23' DAY TO MINUTE  + INTERVAL '16:23:32.123421' HOUR TO SECOND)

    

Type(( 3 05:23+ 16:23:32.123421))

INTERVAL DAY(4) TO SECOND(6)

Notice the output data type gets changed accordingly. We are adding INTERVAL DAY TO MINUTE to HOUR TO SECOND.

The output data type automatically got adjusted as DAY(4) TO SECOND(6). It gets adjusted to the larger input.

SELECT INTERVAL '05:23' HOUR TO MINUTE + INTERVAL '43' MINUTE;

( 5:23+ 43)

6:06