Example: EXPAND ON MONTH_BEGIN and MONTH_END - Advanced SQL Engine - Teradata Database

This example shows how to use MONTH_BEGIN and MONTH_END in an EXPAND ON clause.

First create the ttimestamp table as follows.

     CREATE SET TABLE ttimestamp (
       id       INTEGER,
       quantity INTEGER,
       pd       PERIOD(TIMESTAMP(0)))
     PRIMARY INDEX (id);

Table ttimestamp contains the following rows.

When you specify an EXPAND ON clause by MONTH_BEGIN or MONTH_END, every expanded row starts from either the MONTH_BEGIN value or from the MONTH_END value for that month, and the granularity of each expanded period is one month. In this example, the table data is shown with the default session time zone set to INTERVAL ‘00:00’ HOUR TO MINUTE.

     SET TIME ZONE INTERVAL ‘00:00’ HOUR TO MINUTE;

The following SELECT statement specifies an EXPAND BY MONTH_BEGIN.

     SELECT id, quantity, BEGIN(bg)
     FROM ttimestamp
     EXPAND ON pd AS bg BY ANCHOR MONTH_BEGIN;

Each row is expanded at the default time literal value 00:00:00+00:00 for each MONTH_BEGIN value.

The following SELECT statement specifies an EXPAND BY ANCHOR MONTH_END, but is otherwise identical to the previous statement.

     SELECT id, quantity, BEGIN(bg)
     FROM ttimestamp
     EXPAND ON pd AS bg BY ANCHOR MONTH_END;

Each row is expanded at the default time literal value 23:59:59+00:00 at each month end.