15.00 - INTERVAL - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

INTERVAL

Purpose  

Finds the difference between the ending and beginning bounds of a Period expression or a derived period and returns the difference as the duration of the argument in a specified interval qualifier.

Syntax  

where:

 

Syntax element...

Specifies...

period_expression

any expression that evaluates to a Period data type.

derived_period

any expression that evaluates to a derived period.

interval_qualifier

any interval qualifier appropriate for the argument's element type. The interval qualifiers are as follows:

Year-Month intervals:

  • YEAR
  • YEAR TO MONTH
  • MONTH
  • Day-Time intervals:

  • DAY
  • DAY TO HOUR, MINUTE or SECOND
  • HOUR
  • HOUR TO MINUTE or SECOND
  • MINUTE
  • MINUTE to SECOND
  • SECOND
  • Return Value

    The result of the INTERVAL (p) IQ function is the value of (END(p) - BEGIN(p)) IQ, where argument p is a Period expression and IQ is an interval qualifier.

    If the argument is NULL, the result is NULL.

    Format and Title

    The format is the default format for the interval data type corresponding to the specified interval qualifier.

    The title is INTERVAL(period _expression) interval_qualifier.

    Error Conditions

    An error may be reported:

  • If the argument of the INTERVAL function does not have a Period data type.
  • If the argument has a PERIOD(DATE) data type and the interval qualifier is not YEAR, YEAR TO MONTH, MONTH, or DAY.
  • If the argument has a PERIOD(TIME(n) [WITH TIME ZONE]) data type and the interval qualifier is not HOUR, HOUR TO MINUTE, HOUR TO SECOND, MINUTE, MINUTE TO SECOND or SECOND.
  • If the result of an INTERVAL expression violates the rules specified for the precision of an interval qualifier, an error is reported. For example, assume p1 is a PERIOD(TIMESTAMP(0)) expression that has a value of PERIOD '(2006-01-01 12:12:12, 2007-01-01 12:12:12)'. If INTERVAL(p1) DAY is specified, the default precision for the DAY interval qualifier is 2, and, since the result is 365 days which is a three-digit value that cannot fit into a DAY(2) interval qualifier, an error is reported.
  • If the argument of the INTERVAL function is a period of a DATE or TIMESTAMP(n) [WITH TIME ZONE] and the ending bound value is UNTIL_CHANGED.
  • Example  

    Assume the following query is executed on the employee table with PERIOD(DATE) column period1:

       SELECT INTERVAL (period1) MONTH FROM employee;
     
       ename   dept          period1
       -----   -----------   ----------------------------
       Jones   Sales         ('2004-01-02', '2004-03-05')

    The result is:

       INTERVAL(period) MONTH
      -----------------------
                            2

    Example  

    INTERVAL returns the interval value of a derived period called jobduration created using the following SQL statement:

    CREATE TABLE employee(id INTEGER,
                          name VARCHAR(50),
                          jdbegin DATE NOT NULL FORMAT 'YYYY-MM-DD',
                          jdend   DATE NOT NULL FORMAT 'YYYY-MM-DD',
                          PERIOD FOR jobduration(jdbegin,jdend)
      )PRIMARY INDEX(id);

    When the following values are inserted into the employee table:

    INSERT INTO employee(1025,'John',DATE'2011-01-02',DATE'2012-05-02');

    the following SQL statement:

    SELECT INTERVAL(jobduration)MONTH(4) FROM employee;

    returns:

        INTERVAL(jobduration) MONTH
      -----------------------------
                                 16