17.00 - 17.05 - Result Value - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)
  • Assuming p1 and p2 are comparable Period expressions, p1 RDIFF p2 returns PERIOD(END(p2), END(p1)) if p1 OVERLAPS p2 is TRUE and END(p1) is greater than END(p2). If either Period expression is NULL, p1 OVERLAPS p2 is FALSE, or END(p1) is not greater than END(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 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 RDIFF p2 is as follows if p1 OVERLAPS p2 is TRUE:

PERIOD(
  CASE WHEN CAST(END(p1) AS rt) > CAST(END(p2) AS rt)
     THEN CAST(END(p2) AS rt)
     ELSE NULL END,
  CASE WHEN CAST(END(p1) AS rt) > CAST(END(p2) AS rt)
     THEN CAST(END(p1) 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 RDIFF operation is performed if they overlap.