Partitioning Expressions Using DATE or TIMESTAMP Built-In Functions - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530

Vantage supports the BEGIN and END bound functions on Period columns in partitioning expressions anywhere that a DateTime expression is valid.

For information about how to optimize partitioning expressions based on updatable dates and updatable timestamps for reconciliation using ALTER TABLE TO CURRENT statements, see Partitioning Expression Based on Updatable Current Date and Timestamp Expressions.

Follow these guidelines for creating the partitioning expression for tables that are to be queried using DATE conditions.
  • Use RANGE_N with a DATE partitioning column. For example,
    PARTITION BY RANGE_N(date_column BETWEEN DATE '...'
                                 AND     DATE '...'
                                 EACH INTERVAL 's' t)
  • Use INTERVAL constants in the EACH clause, where t is DAY, MONTH, YEAR, or YEAR TO MONTH
  • Use DATE constants for the ranges, for example: DATE '2003-08-06'.

    Do not use INTEGER or CHARACTER constants for dates.

    DATE constants are a better choice for the following reasons:
    • DATE constants are easier to read, and make clear that the ranges are over dates.
    • DATE constants are less dependent on FORMAT.
  • Partitioning by a DATE column such as PARTITION BY salesdate may seem intuitive.

    This does not return a syntax error, may works correctly for old dates, and follows the rule of implicit conversion to get an INTEGER partition number.

    The problem is that a table partitioned this way is not useful.

    Instead, use RANGE_N and EACH INTERVAL '1' DAY.

  • Specifying a partition like PARTITION BY 7 to indicate that a date column in the primary index is to be partitioned by week may seem intuitive, but this syntax returns a syntax error.

    Instead, use RANGE_N and EACH INTERVAL '7' DAY

  • Have no more date ranges than you need.Balance the following concerns:
    • Limiting ranges to only those that are needed helps the Optimizer to cost plans better and allows more efficient primary index access and joins if the partitioning column is not included in the primary index definition.
    • Define future ranges in such a way as to minimize the frequency of adding and dropping partitions using the ALTER TABLE statement (see ALTER TABLE Usage Notes (Basic Table Parameters)).

      However, if you perform your partition adding and dropping task too infrequently, you may forget to do these tasks when needed to provide accurate query support.