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
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

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