Using RANGE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

You can define a partitioning expression that uses RANGE_N with the built-in functions CURRENT_DATE or CURRENT_TIMESTAMP. Use of CURRENT_DATE or CURRENT_TIMESTAMP in a partitioning expression is most appropriate when the data must be partitioned as one or more current partitions and one or more history partitions where the current and history partitions are based on the resolved CURRENT_DATE or CURRENT_TIMESTAMP in the partitioning expression. This allows you to periodically reconcile the table to move older data from the current partition into one or more history partitions using the ALTER TABLE TO CURRENT statement instead of redefining the partitioning using explicit dates which must be determined each time the ALTER TABLE DROP/ADD RANGE is done.

For more information, see “Rules and Guidelines for Optimizing the Reconciliation of RANGE_N PPI Expressions Based On Updatable Current Date and Updatable Current Timestamp” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.