TIMESTAMP-to-Period Conversion Usage Notes - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

A TIMESTAMP(n) [WITH TIME ZONE] value can be cast as PERIOD(DATE), PERIOD(TIME[(n)] [WITH TIME ZONE]), or PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]) using the CAST function.

If the target type is PERIOD(TIME[(n)] [WITH TIME ZONE]) or PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]):

  • If the target precision is higher than the source precision, trailing zeros are added in the result bounds to adjust the precision.
  • If the target precision is lower than the source precision, an error is reported.

If the target type is PERIOD(DATE), the result beginning bound is the date portion of the source beginning bound adjusted to the current session time zone.

If the target type is PERIOD(TIME[(n)]), the result beginning bound is the time portion of the source value (in UTC).

If the target type is PERIOD(TIME[(n)] WITH TIME ZONE), the result beginning bound is formed from the time portion of the source value (in UTC) and, if the source type is WITH TIME ZONE, the source time zone displacement and, if not, the current session time zone displacement.

If the target type is PERIOD(TIMESTAMP[(n)]), the result beginning bound is the timestamp portion of the source value (in UTC).

If the target type is PERIOD(TIMESTAMP[(n)] WITH TIME ZONE), the result beginning bound is formed from the timestamp portion of the source value (in UTC) and, if the source type is WITH TIME ZONE, the source time zone displacement and, if not, the current session time zone displacement.

If the TIMESTAMP source value contains leap seconds, the seconds portion gets adjusted to 59.999999 with the precision truncated to the target precision.

The result ending element is set to the result beginning bound plus one granule of the target type. If the result ending bound exceeds the maximum allowed DATE or TIMESTAMP value for a target type of PERIOD(DATE) or PERIOD(TIMESTAMP[(n)]), respectively, or the ending bound has a lower value than the result beginning bound in their UTC forms for a target type of PERIOD(TIME[(n)]), an error is reported.

If the target type is WITH TIME ZONE, the result beginning and ending bounds have the same time zones.

Also, the result has the same value for the beginning bound and last value.