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 |