Mismatch in Delta Lake timestamp Mapping to Presto

[imjalpreet]

Starting from Delta Kernel 3.2,

Delta Lake supports two distinct timestamp types:
1. timestamp: Includes time zone context.
2. timestamp_ntz: Represents a timestamp without time zone context. (Unsupported in Presto)

It appears that the Delta Lake timestamp data type is being mapped to Presto’s TIMESTAMP type, which does not include time zone information. However, the Delta Lake timestamp type inherently includes time zone context, and this mapping may lead to potential inconsistencies or data misinterpretations when querying Delta tables via Presto.

presto/presto-delta/src/main/java/com/facebook/presto/delta/DeltaTypeUtils.java

Lines 233 to 235 in fed9c5d

	else if (deltaType instanceof TimestampType) {
	return TIMESTAMP;
	}

https://docs.databricks.com/en/sql/language-manual/data-types/timestamp-type.html
https://docs.databricks.com/en/sql/language-manual/data-types/timestamp-ntz-type.html

Your Environment

• Presto version used: Latest master (0.291-SNAPSHOT)
• Storage (HDFS/S3/GCS..): N.A
• Data source and connector used: Delta Lake Connector
• Deployment (Cloud or On-prem): N.A

Expected Behavior

Delta Lake’s timestamp type should map to Presto’s TIMESTAMP WITH TIME ZONE to preserve the time zone context and ensure the data is interpreted correctly.

Current Behavior

Delta Lake’s timestamp type is mapped to Presto’s TIMESTAMP, which does not account for time zone information.

Possible Solution

Update the Delta Lake connector in Presto to map Delta Lake timestamp to Presto TIMESTAMP WITH TIME ZONE. This would ensure accurate representation of the data and align with Delta Lake’s data type semantics.

Map Delta timestamp_ntz to Presto’s TIMESTAMP.

Context

This issue could lead to incorrect query results when dealing with time zone-sensitive data in Delta Lake tables, potentially causing significant inaccuracies in time-based calculations or reports.

[infvg]

Working on this

[imjalpreet]

TimestampNTZ support: PR #24418