DATE and TIME Intervals Teradata

To make Teradata SQL more ANSI compliant and compatible with other RDBMS SQL, NCR has added INTERVAL processing. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.

Although Teradata allowed arithmetic on DATE and TIME, it was not performed in accordance to ANSI standards and therefore, an extension instead of a standard. With INTERVAL being a standard instead of an extension, more SQL can be ported directly from an ANSI compliant database to Teradata without conversion.

Additionally, when a data value was used to perform date or time math, it was always "assumed" to be at the lowest level for the definition (days for DATE and seconds for TIME). Now, any portion of either can be expressed and used.

interval chart

Using Intervals

To use the ANSI syntax for intervals, the SQL statement must be very specific as to what the data values mean and the format in which they are coded. ANSI standards tend to be lengthier to write and more restrictive as to what is and what is not allowed regarding the values and their use.

Simple INTERVAL Examples using literals:

Complex INTERVAL Examples using literals:

For several of the INTERVAL literals, their use seems obvious based on the literal nonnumeric literals used. However, notice that the HOUR TO MINUTE and the MINUTE TO SECOND above, are not so obvious. Therefore, the declaration of the meaning is important. For instance, notice that they are coded as character literals. This allows for use of a slash (/), colon (: ) and space as part of the literal. Also, notice the use of a negative time frame requires a "−" sign to be outside of the quotes. The presence of the quotes also denotes that the numeric values are treated as character for conversion to a point in time.

The format of a timestamp requires the space between the day and hour when using intervals. For example, notice the blank space between the day and hour in the compound DAY TO HOUR interval. Without the space, it is an error.

INTERVAL Arithmetic with DATE and TIME

To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations.

INTERVAL Arithmetic with DATE and TIME

Date and time arithmetic results using intervals

Note: It makes little sense to add two dates together.

Traditionally, the output of the subtraction is an integer, up to 2.147 billion. However, Teradata knows that when an integer is used in a formula with a date, it must represent a number of days. The following uses the ANSI representation for a DATE:

SELECT (DATE '1999-10-01' - DATE '1988-10-01') AS Assumed_Days ;
1 Row Returned

Assumed_Days

4017

The next SELECT uses the ANSI explicit DAY interval:

SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY AS Actual_Days ; **** Failure 7453 Internal Field Overflow

The above request fails on an overflow of the INTERVAL. Using this ANSI interval, the output of the subtraction is an interval with 4 digits. The default for all intervals is 2 digits and therefore the overflow occurs until the SELECT is modified with DAY(4), below:

SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY(4) AS Actual_Days ; 1 Row Returned

Actual_Days

4017

Normally, a date minus a date yields the number of days between them. To see months instead, the following SELECT operations use literals to demonstrate the conversions performed on various DATE and INTERVAL data:

SELECT (DATE '2000-10-01' – DATE '1999-10-01') MONTH (Title 'Months') ; 1 Row Returned

Months

12

The next SELECT shows INTERVAL operations used with TIME:


1 Row ReturnedSELECT shows INTERVAL operations used with TIME

Although Intervals tend to be more accurate, they are more restrictive and therefore, more care is required when coding them into the SQL constructs. However, one miscalculation, like in the overflow example, and the SQL fails. Additionally, 9999 is the largest value for any interval. Therefore, it might be required to use a combination of intervals, such as: MONTHS to DAYS in order to receive an answer without an overflow occurring.

CAST Using Intervals

Compliance: ANSI

The CAST function was seen in an earlier chapter as the ANSI method for converting data from one type to another. It can also be used to convert one INTERVAL to another INTERVAL representation. Although the CAST is normally used in the SELECT list, it works in the WHERE clause for comparison reasons.

Below is the syntax for using the CAST with a date:

The following converts an INTERVAL of 6 years and 2 months to an INTERVAL number of months:

SELECT CAST( (INTERVAL '6-02' YEAR TO MONTH) AS INTERVAL MONTH );
1 Row Returned

‘6-02’

74

Logic seems to dictate that if months can be shown, the years and months should also be available. This request attempts to convert 1300 months to show the number of years and months:

The above failed because the number of months takes more than two digits to hold a number of years greater than 99. The fix is to change the YEAR to YEAR(3) and rerun:

SELECT CAST((INTERVAL '1202' MONTH) AS INTERVAL YEAR(3) TO MONTH ) (Title 'Years & Months') ;
1 Row Returned

Years & Months

100-02

The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible with Teradata.

At the same time, care must be taken to use a representation that is large enough to contain the answer. The default is 2 digits and anything larger, 4 digits maximum, must be literally requested. The incorrect size results in an SQL runtime error.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics