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

The beginning bound of the expanding period value is adjusted to start at the time specified by time_literal before expanding the rows. The anchor point (for example MONTH_BEGIN) is based on the session time zone. Therefore, 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. 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, the time is the previous day. Therefore, the previous day-to-month begin is checked with the row, and the database adds the session time to the returned 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.

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:

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, the time is the next day. Therefore, the database checks the next day to month end with the row, and adds the session time to the returned row.

You have the following table:

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

Table test1 contains the following row:

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’;

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.

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.