15.00 - P_INTERSECT - 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)

P_INTERSECT

Purpose  

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

Syntax  

where:

 

Syntax element...

Specifies...

period_expression

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:

    PERIOD(
      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.

    Example  

    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')