Example: Same Expansion in Two Different Sessions in Different Time Zones - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

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
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

The beginning bound of the expanding period value is adjusted to start at the time specified by time_literal before it expands the rows. The anchor point, for example MONTH_BEGIN, is computed based on the session time zone. As a result, the output can be different for two sessions that are at two different time zones.

In the following example, the time literal defaults to 00:00:00 at the session time zone because the EXPAND ON clause input row does not specify a time zone, because the value of duration has a PERIOD(TIMESTAMP) data type. After the time literal is converted to UTC, it is the previous day. Therefore, the previous day-to-month begin is checked with the row, and when it is returned,the database adds the session time.

First you create the following table.

     CREATE SET TABLE test (
       testid   INTEGER,
       duration PERIOD(TIMESTAMP))
     PRIMARY INDEX (testid);

Table test contains the following row.

testid duration (at UTC)
ABC 2002-01-31 15:30:00, 2002-05-31 15:00:00

You then perform the following anchor point expansion by MONTH_BEGIN with a default time zone literal.

     SET TIME ZONE INTERVAL '09:00' HOUR TO MINUTE;
     SELECT BEGIN(xyz)
     FROM test
     EXPAND ON duration AS xyz BY ANCHOR MONTH_BEGIN;

This statement returns the following rows:

BEGIN(xyz)
2002-03-01 00:00:00
2002-04-01 00:00:00
2002-05-01 00:00:00

In the following example, the time literal is 20:00:00 at session time zone because the input row does not specify a time zone, because the value of duration has a PERIOD(TIMESTAMP) data type. After the time literal is converted to UTC, it is the next day. Therefore, the database checks the next day to month end with the row, and when it is returns the row, it adds the session time.

You have the following table:

     CREATE SET TABLE test1 (
       testid   INTEGER,
       duration PERIOD(TIMESTAMP))
     PRIMARY INDEX (testid);

Table test1 contains the following row:

testid duration (at UTC)
ABC 2005-12-03 04:30:00, 2006-04-01

You perform the following anchor period expansion by MONTH_END with a time literal and with a default session time zone.

     SET TIME ZONE INTERVAL -'07:00' HOUR TO MINUTE;
     SELECT BEGIN(xyz)
     FROM test1
     EXPAND ON duration AS xyz BY ANCHOR MONTH_END AT TIME ‘20:00:00’;
BEGIN(XYZ)
2005-12-31 20:00:00
2006-01-31 20:00:00
2006-02-28 20:00:00

In the next example, the time literal value is 07:00:00 at time zone +10:00. After the time literal is converted to UTC, the time is 21:00 on the previous day. Therefore, the database checks the previous day to month end value with the row and, when the time series value is returned by the statement, the database adds the session time, which is 00:00.

You have the following table.

     CREATE SET TABLE test2 (
       testid   INTEGER,
       duration PERIOD(TIMESTAMP))
     PRIMARY INDEX (testid);

Table test2 contains the following row.

testid duration (at UTC)
timeseries 2005-12-30 22:30:00, 2006-04-29 18:00:00

You perform the following anchor period expansion by MONTH_END, specifying both a time literal and a time zone.

     SET TIME ZONE INTERVAL '00:00' HOUR TO MINUTE;
     SELECT timeseries
     FROM test2
     EXPAND ON duration AS timeseries BY ANCHOR PERIOD MONTH_END 
                                         AT TIME '07:00:00+10:00';

This statement returns the following row set.

timeseries
2005-12-30 21:00:00, 2006-01-30 21:00:00
2006-01-30 21:00:00, 2006-02-27 21:00:00
2006-02-27 21:00:00, 2006-03-30 21:00:00
2006-03-30 21:00:00, 2006-04-29 21:00:00