17.10 - Example of interval_expression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)
The definition for interval_expression can be expressed in three forms:
  • interval_term
  • interval_expression + interval_term
  • (date_time_expression - date_time_term) start TO end

This example uses the second definition.

SELECT (CAST(INTERVAL '125' MONTH AS INTERVAL YEAR(2) TO MONTH))
+ INTERVAL '12' YEAR;

The interval_expression is INTERVAL '125' MONTH.

The interval_term is INTERVAL '12' YEAR.

The processing involves the following stages:

  1. The CAST function converts the interval_expression value of 125 months to 10 years and 5 months.
  2. The interval_term amount of 12 years is added to the interval_expression amount, giving 22 years and 5 months.
  3. The result is converted to the appropriate data type, which is INTERVAL YEAR(2) TO MONTH, giving '22-05'.

This example uses the third definition for interval_expression.

You must ensure that the values for date_time_expression and date_time_term are comparable.

SELECT (TIME '23:59:59.99' - CURRENT_TIME(2)) HOUR(2) TO SECOND(2);

The date_time_expression is TIME '23:59:59.99'.

The date_term is the date_time_primary - CURRENT_TIME(2).

The processing involves the following stages:

  1. Assume that the current system time is 18:35:37.83.
  2. The HOUR(2) TO SECOND(2) time interval 18:35:37.83 is subtracted from the TIME value 23:59:59.99, giving the result '5:24:22.16'.

Here is another example that uses the third definition for interval_expression to find the difference in minutes between two TIMESTAMP values. First define a table:

CREATE TABLE BillDateTime
(start_time TIMESTAMP(0)
,end_time TIMESTAMP(0));

Now, determine the difference in minutes:

SELECT (end_time - start_time) MINUTE(4)
FROM BillDateTime;

The processing involves the following stages:

  1. The start_time TIMESTAMP value is subtracted from the end_time TIMESTAMP value, giving an interval result.
  2. The MINUTE(4) specifies an interval unit of minutes with a precision of four digits, which allows for a maximum of 9999 minutes, or approximately one week.