15.00 - P_INTERSECT - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)
Last Update



Returns the portion of the Period expression that is common to the Period expressions if they overlap.




Syntax element...



any expression that evaluates to a Period data type.

Note: The Period expressions specified must be comparable. Implicit casting to a Period data type is not supported.

Result Value

  • If the Period expressions do not overlap, the result is NULL. If either Period expression is NULL, the result is NULL. Otherwise, the result has a Period data type that is comparable to the Period expressions.
  • If the Period expressions have PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) or PERIOD(TIME(n) [WITH TIME ZONE]) data types but different precisions, the result is a Period value of the higher precision data type. If neither Period expression has a time zone, the resulting period does not have a time zone; otherwise, the resulting period has a time zone and the value of the time zone in the result is determined using the following rules:
  • If both Period expressions have a time zone, the time zone displacement of a result bound is obtained from the corresponding bound of the Period expression as defined by the Period value constructor that follows.
  • If only one of the Period expressions has a time zone, the other Period expression is considered to be at the current session time zone and the result is computed as follows.
  • Assuming p1 and p2 are Period expressions and the result element type as determined above is rt, the result of p1 P_INTERSECT p2 is as follows if p1 OVERLAPS p2 is TRUE:

      CASE WHEN CAST(BEGIN(p1) AS rt) >= CAST(BEGIN(p2) AS rt)
         THEN CAST(BEGIN(p1) AS rt)
         ELSE CAST(BEGIN(p2) AS rt) END,
      CASE WHEN CAST(END(p1) AS rt) <= CAST(END(p2) AS rt)
         THEN CAST(END(p1) AS rt)
         ELSE CAST(END(p2) AS rt) END)

    Internally, Period values are saved in UTC and the OVERLAPS operator is evaluated using these UTC represented formats and the P_INTERSECT operation is performed if they overlap.

    Format and Title

    The format is the default format for the resulting Period data type.

    Error Conditions

    If either expression is not a Period expression, an error is reported.

    If the Period expressions are not comparable, an error is reported.


    In the following example, the P_INTERSECT operator is used in the selection list.

       SELECT period2 P_INTERSECT period1 
       FROM product_tests 
       WHERE pid = 11804;

    Assume the query is executed on the following table product_tests where period1 is a PERIOD(TIME(1)) column and period2 is a PERIOD(TIME(0)) column:

       pid     period1                        period2
       -----   ----------------------------   ------------------------
       11804   ('10:10:10.1', '11:10:10.1')   ('10:10:10', '10:10:11')
       10996   ('11:10:10.1', '11:40:40.1')   ('10:10:10', '10:10:11')

    The result is as follows:

       (period2 P_INTERSECT period1)
       ('10:10:10.1', '10:10:11.0')