Example of interval_expression - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™
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.