14.3. Legacy Timestamp
TIMESTAMP semantics is still experimental. It’s recommended to keep
TIMESTAMP semantics enabled. You can experiment with the new semantics
by enabling in the global configuration or session level. The legacy semantics
may be deprecated in a future release.
TIMESTAMP semantics can be set to
deprecated.legacy-timestamp in the
Additionally, there is a session level property
that can be set.
The Teradata distribution of Presto fixes the semantics of the
types to align with the SQL standard. See the following sections for details.
TIMESTAMP semantic changes
TIMESTAMP type described an instance in time in the Presto session’s time zone.
Now, Presto treats
TIMESTAMP values as a set of the following fields representing wall time:
YEAR OF ERA
MONTH OF YEAR
DAY OF MONTH
HOUR OF DAY
MINUTE OF HOUR
SECOND OF MINUTE- as
For that reason, a
TIMESTAMP value is not linked with the session time zone in any way until a time zone is needed explicitly,
such as when casting to a
TIMESTAMP WITH TIME ZONE or
TIME WITH TIME ZONE.
In those cases, the time zone offset of the session time zone is applied, as specified in the SQL standard.
TIME semantic changes
TIME type was changed similarly to the
TIME WITH TIME ZONE semantic changes
Due to compatibility requirements, having
TIME WITH TIME ZONE completely aligned with the SQL standard was not possible yet.
For that reason, when calculating the time zone offset for
TIME WITH TIME ZONE, the Teradata distribution of Presto uses
the session’s start date and time.
This can be seen in queries using
TIME WITH TIME ZONE in a time zone that has had time zone policy changes or uses DST.
eg. With session start time on 1 March 2017
SELECT TIME '10:00:00 Asia/Kathmandu' AT TIME ZONE 'UTC'
- Legacy result:
- New result: