17.05 - Example: Same Expansion in Two Different Sessions Using Different Time Zone Intervals - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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