15.00 - P_NORMALIZE - 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)
Last Update
2018-09-24

P_NORMALIZE

Purpose  

Returns a Period value that is the combination of the two Period expressions if the Period expressions overlap or meet. If the Period expressions neither meet nor overlap, the function returns NULL. If either Period expression is NULL, the function returns NULL.

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

  • Assuming p1 and p2 are comparable Period expressions and ((BEGIN(p1) >= BEGIN(p2) AND BEGIN(p1) <= END(p2)) OR (BEGIN(p2) >= BEGIN(p1) AND BEGIN(p2) <= END(p1))) is TRUE, p1 P_NORMALIZE p2 returns PERIOD(minimum(BEGIN(p1), BEGIN(p2)), maximum(END(p1), END(p2))).
  • If either Period expression is NULL or ((BEGIN(p1) >= BEGIN(p2) AND BEGIN(p1) <= END(p2)) OR (BEGIN(p2) >= BEGIN(p1) AND BEGIN(p2) <= END(p1))) is FALSE, the result is NULL. P_NORMALIZE returns a Period value if the Period expressions satisfy the MEETS or OVERLAPS condition.
  • If the Period expressions have PERIOD(TIME(n) [WITH TIME ZONE]) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) data type but have different precisions, the result has the higher of the two precisions. If one of the Period expressions contains a time zone, the result contains a time zone for each element. The result time zones are 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 expressions 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_NORMALIZE p2 is as follows if p1 OVERLAPS p2 OR p1 MEETS 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 or MEETS operator is evaluated using these UTC represented formats and the P_NORMALIZE operation is performed if they overlap or meet.

    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_NORMALIZE operator is used to collapse two Period columns.

       SELECT period2 P_NORMALIZE period1 
       FROM product_tests 
       WHERE pid = 11215;

    Assume the query is executed on the following table product_tests where period1 is PERIOD(TIME(1)) column and period2 is 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')
       11215   ('10:40:10.1', '11:20:20.1')   ('11:10:10', '11:50:10')

    The result is as follows:

     

       (period2 P_NORMALIZE period1)
       -----------------------------
       ('10:40:10.1', '11:50:10.0')