Time Option Not Allowed for PERIOD(DATE)
If the data type of the expansion expression is PERIOD(DATE) and you specify a time literal in the anchored interval, the request returns an error to the requestor. You cannot specify a time option when the data type of the expansion expression is PERIOD(DATE).
PERIOD(TIME) and PERIOD(TIME WITH TIME ZONE) Not Valid for an Anchored EXPAND ON Clause
You cannot specify an anchored interval if the data type of the expansion expression is either PERIOD(TIME) or PERIOD(TIME WITH TIME ZONE). Otherwise, the database returns an error.
Timestamp Data Type Uses the Time Literal Value that You Specify
If the element type of the expansion expression is a Timestamp data type, the database uses the time literal value that you specify to define the timestamp value of the anchor during expansion.
If you specify DAY for anchor_name, the expansion interval is INTERVAL '1' DAY for each expanded row.
If you do not specify a time literal, the time literal value defaults to '00:00:00.000000+00:00' for an anchor name of ANCHOR_MILLISECOND, ANCHOR_SECOND, ANCHOR_MINUTE, ANCHOR_HOUR, WEEK_BEGIN, MONTH_BEGIN, QUARTER_BEGIN, or YEAR_BEGIN and to '23:59:59.999999+00:00' for any other anchor name.
The precision of the default value is set to the precision of the expansion expression. The default value includes the time zone value of +00:00 if the expansion expression specifies a time zone. Otherwise, the value is the session time zone and does not specify a time zone.
The anchor, for example MONTH_BEGIN, is computed based on the session time zone. Thus, for two sessions that are at different time zones, the output can differ. For an example of this, see Example: Same Expansion in Two Different Sessions in Different Time Zones.
To Expand a Table by the First Day of the Month, specify MONTH_BEGIN
To expand a table by the first calendar day of every month, specify MONTH_BEGIN in the anchored interval clause.
Each expanded value for the row in the result has the first day of the corresponding month as the BEGIN bound and the expansion interval defaults to INTERVAL '1' MONTH. The BEGIN bound of the expanded value for each result row is 'YYYY-MM-01'.
See the first SELECT request in Example: EXPAND ON MONTH_BEGIN and MONTH_END.
To Expand a Table by the Last Day of the Month, Specify MONTH_END
To expand a table by the last calendar day of every month, specify MONTH_END in the anchored interval clause.
Each expanded value for the result row has the last day of the corresponding month as its BEGIN bound, and the expansion interval defaults to INTERVAL '1' MONTH.
The BEGIN bound of the expanded value for each result row is 'YYYY-MM-DD' where DD is one of 28, 29, 30, or 31, depending on the month and the year.
See the second SELECT request in Example: EXPAND ON MONTH_BEGIN and MONTH_END.
To Expand a Table by a Specific Weekday, Specify the Weekday in the Anchored Interval Clause
To expand a table by a particular weekday, specify the weekday you want to use in the anchored interval clause. The begin bound of each expanded value for the result row corresponds to that day of the week, and the expanding interval defaults to INTERVAL '7' DAY.