PERIOD(TIMESTAMP) Data Type Usage Notes - 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

Storage

A PERIOD(TIMESTAMP[(n)]) is a variable length data type and is saved as two TIMESTAMP values. The ending bound value of UNTIL_CHANGED occupies a single byte for these variable length data types.

Element Type Field Size in bytes Maximum Size in bytes in the row
TIMESTAMP(n) 20 if ending bound is not UNTIL_CHANGED 24 if ending bound is not UNTIL_CHANGED
11 if the ending bound is UNTIL_CHANGED 16 if the ending bound is UNTIL_CHANGED

External Representation of PERIOD(TIMESTAMP)

In field mode, Vantage returns PERIOD(TIMESTAMP) data as character data.

Assume L is the maximum length of the formatted character string for the format associated with this Period data type. The resulting character string contains two strings representing the beginning and ending bounds of the period value expression, each up to length L , and each enclosed in apostrophes ('), separated by comma and a space (,), and then enclosed within a left and right parenthesis [( )]. Thus, the maximum length of the resulting character string is 2* L +8.

Assume the actual length is K which may be less than 2*L+8, for example, if the format includes the full names of months and the specific month for a bound is July.

For modes other than field mode, and for input data, the external representation of PERIOD(TIMESTAMP) consists of 2-bytes representing the length of the data followed by 2 consecutive timestamp values. Each timestamp value consists of the following fields, returned in the specified order:
  • Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal (for example, 12.56 seconds is returned as 12560000).
  • Year: 2-byte, signed short integer flipped to client form. This integer represents the year value.
  • Month: 1 unsigned byte. This byte represents the month value.
  • Day: 1 unsigned byte. This byte represents the day of the month.
  • Hour: 1 unsigned byte. This byte represents the number of hours.
  • Minute: 1 unsigned byte. This byte represents the number of minutes.

Restrictions

A primary index column or partitioning column cannot be a column that has a Period data type.