8.3. Legacy and New Timestamp
New TIMESTAMP and TIME semantics align the types with the SQL standard.
See the following sections for details.
Note
The new TIMESTAMP semantics is still experimental. It’s recommended to keep
the legacy TIMESTAMP semantics enabled. You can experiment with the new semantics
by configuring it globally or on a per-session basis. The legacy semantics
may be deprecated in a future release.
Configuration
The legacy semantics can be enabled using the deprecated.legacy-timestamp
config property. Setting it to true (the default) enables the legacy semantics,
whereas setting it to false enables the new semantics.
Additionally, it can be enabled or disabled on a per-session basis
with the legacy_timestamp session property.
TIMESTAMP semantic changes
Previously, the 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 ERAMONTH OF YEARDAY OF MONTHHOUR OF DAYMINUTE OF HOURSECOND OF MINUTE- asDECIMAL(5, 3)
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
The TIME type was changed similarly to the TIMESTAMP type.
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, 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. For example, with a session start time of 2017-03-01:
- Query:
SELECT TIME '10:00:00 Asia/Kathmandu' AT TIME ZONE 'UTC'- Legacy result:
04:30:00.000 UTC- New result:
04:15:00.000 UTC