Example: Same Expansion in Two Different Sessions Using Different Time Zone Intervals - 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 this example the time zone for the first session is set to INTERVAL -’01:00’ HOUR TO MINUTE, and the time zone for the second session is set to INTERVAL ‘02:00’ HOUR TO MINUTE.

You set the time zone for the first session as follows and then submit the indicated SELECT statement anchored on MONTH_BEGIN:

     SET TIME ZONE INTERVAL -'01:00' HOUR TO MINUTE;
     SELECT id, quantity, BEGIN(pd) AS bg
     FROM ttimestamp
     EXPAND ON PERIOD bg BY ANCHOR MONTH_BEGIN;
id quantity bg
11 110 2005-03-01 00:00:00
11 110 2005-04-01 00:00:00
11 110 2005-05-01 00:00:00
11 110 2005-06-01 00:00:00
10 100 2004-02-01 00:00:00
10 100 2004-03-01 00:00:00
10 100 2004-04-01 00:00:00
10 100 2004-05-01 00:00:00

The output of the same SELECT statement submitted in the second session returning one additional row (shaded in orange):

     SET TIME ZONE INTERVAL '02:00' HOUR TO MINUTE;
     SELECT id, quantity, BEGIN(pd) AS bg
     FROM ttimestamp 
     EXPAND ON PERIOD bg BY ANCHOR MONTH_BEGIN;
id quantity bg
11 110 2005-02-01 00:00:00
11 110 2005-03-01 00:00:00
11 110 2005-04-01 00:00:00
11 110 2005-05-01 00:00:00
11 110 2005-06-01 00:00:00
10 100 2004-02-01 00:00:00
10 100 2004-03-01 00:00:00
10 100 2004-04-01 00:00:00
10 100 2004-05-01 00:00:00E