Result Type - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantageā„¢
  • 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 earlier 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.