17.10 - Result Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)
  • Assuming p1 and p2 are comparable Period expressions, p1 LDIFF p2 returns PERIOD(BEGIN(p1), BEGIN(p2)) if p1 OVERLAPS p2 is TRUE and BEGIN(p1) is less than BEGIN(p2). If either Period expression is NULL, p1 OVERLAPS p2 is FALSE, or BEGIN(p1) is not less than BEGIN(p2), the result is NULL.
  • If the Period expressions have PERIOD(TIME(n) [WITH TIME ZONE]) or PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) data types but have different precisions, the result has the higher of the two precisions. If one of the Period expressions contains time zones and the other does not, the result contains a time zone for each element. The result time zones are evaluated 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 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 LDIFF 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 NULL END,
  CASE WHEN CAST(BEGIN(p1) AS rt) < CAST(BEGIN(p2) AS rt)
     THEN CAST(BEGIN(p2) AS rt)
     ELSE NULL END)

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