@vkondepati
I spent some time reproducing a similar scenario using Neon PostgreSQL connected to Databricks through Unity Catalog Lakehouse Federation and found a few observations that might help explain the timezone/date-aggregation mismatch.
1. PostgreSQL setup and baseline validation
I created a PostgreSQL table containing both timestamp types:
CREATE TABLE timezone_test.events (
id INTEGER PRIMARY KEY,
event_description TEXT,
local_timestamp TIMESTAMP WITHOUT TIME ZONE,
utc_timestamp TIMESTAMP WITH TIME ZONE
); Inserted six test records around America/Chicago midnight boundaries and daylight-saving transitions (March 8 and November 1, 2026).
PostgreSQL correctly handled both UTC and Chicago timezone conversions. For example:
Source timestamp (Chicago) UTC instant Chicago date
| 2026-03-07 23:30 | 2026-03-08 05:30 UTC | Mar 7 |
| 2026-03-08 00:30 | 2026-03-08 06:30 UTC | Mar 8 |
| 2026-03-08 03:30 | 2026-03-08 08:30 UTC | Mar 8 |
This established the source-side baseline before testing federation.
2. Unity Catalog federation observations
I created a PostgreSQL connection and foreign catalog in Databricks, then queried the same six records.
One important observation: both PostgreSQL timestamp types were exposed as Databricks TIMESTAMP by default.
With:
SET TIME ZONE 'America/Chicago';
the PostgreSQLtimestamp without time zone values were displayed differently in Databricks.
For example:
- PostgreSQL source: 2026-03-08 00:30:00
- Databricks federated value: 2026-03-07 18:30:00
The underlying epoch remained unchanged across Databricks UTC and Chicago sessions, confirming that the mapped value was being treated as an instant rather than preserving the original local wall-clock representation.
3. Query-plan investigation - Aggregate pushdown
I compared the same date aggregation against:
- The Unity Catalog foreign table.
- A native Delta copy of the same records
- remote_query() with aggregate/filter pushdown disabled.
Interestingly, the foreign catalog returned different date groupings from the native Delta table.
EXPLAIN FORMATTED confirmed that the foreign-catalog query pushed the date casts and aggregation directly to PostgreSQL:
SELECT
CAST("local_timestamp" AS DATE),
CAST("utc_timestamp" AS DATE),
COUNT(*)
FROM "timezone_test"."events"
GROUP BY
CAST("local_timestamp" AS DATE),
CAST("utc_timestamp" AS DATE)
For example, three records were grouped under March 8 for the PostgreSQL local timestamp, whereas Databricks-local evaluation grouped those same records under March 7.
This demonstrates how timestamp mapping combined with aggregate pushdown can produce different business-day groupings.
An additional observation regarding timezone configuration: although SET TIME ZONE 'America/Chicago' changed the Databricks session's timestamp interpretation, it did not ensure equivalent date-casting semantics when aggregation was pushed to PostgreSQL.
EXPLAIN FORMATTED confirmed that date casts and aggregation executed remotely in PostgreSQL. When I disabled aggregate pushdown through reomte_query(), the date conversions and grouping executed locally in Photon, producing results consistent with the native Delta table.
This suggests that configuring the Databricks SQL session or warehouse timezone alone may not resolve discrepancies involving remote execution.
4. Workarounds tested
A. Disable aggregate pushdown
SELECT
CAST(local_timestamp AS DATE) AS local_date,
COUNT(*) AS records
FROM remote_query(
'neon_postgres_connection',
database => 'neondb',
query => 'SELECT local_timestamp FROM timezone_test.events',
`pushdown.aggregates.enabled` => 'false',
`pushdown.filters.enabled` => 'false'
)
GROUP BY CAST(local_timestamp AS DATE)
This successfully produced results consistent with the native Delta table.
EXPLAIN FORMATTED also confirmed that Photon performed the date conversion and aggregation locally, rather than pushing them to PostgreSQL.
This is a useful workaround, although it may affect performance and does not necessarily preserve the original PostgreSQL business-date semantics.
B. Preserve source timestamps using TIMESTAMP_NTZ
An explicit CAST(local_timestamp AS TIMESTAMP_NTZ) after federation did not restore the original PostgreSQL wall-clock values.
However, converting the timestamp to text in PostgreSQL first, then casting to TIMESTAMP_NTZ in Databricks, worked:
SELECT
id,
CAST(local_ts_text AS TIMESTAMP_NTZ) AS local_ntz
FROM remote_query(
'neon_postgres_connection',
database => 'neondb',
query => '
SELECT id,
local_timestamp::text AS local_ts_text
FROM timezone_test.events
'
)
This preserved the original PostgreSQL values, including 2026-03-08 00:30:00 without the earlier Chicago display shift.
This workaround is specifically relevant to PostgreSQL timestamp without time zone columns representing local wall-clock values, not timestamptz columns representing actual instants.
5. What about preferTimestampNTZ?
The Databricks PostgreSQL federation documentation mentions preferTimestampNTZ=true for mapping PostgreSQL timezone-naive timestamps to TimestampNTZType.
I checked foreign catalog connection and couldn't find a way to specify it through the connection.
Conclusion
Based on this controlled reproduction, the interaction between PostgreSQL timestamp type mapping and federated aggregate pushdown can lead to different date-aggregation results, even under the same Databricks session timezone.
The tests demonstrated two practical approaches depending on the intended semantics:
- Disable aggregate pushdown when Databricks-local timestamp interpretation is required.
- Preserve PostgreSQL timezone-naive wall-clock values through source-side text conversion followed by TIMESTAMP_NTZ.
Neither should be treated as a universal fix without validating the source timestamp semantics.
Also another community post highlighting the same issue and narrowed down to the same governed view approach to encapsulate remote_query or source-side text-to-TIMESTAMP_NTZ conversion
It would also be valuable for Databricks to clarify timezone handling and document the availability of preferTimestampNTZ for Unity Catalog federation. I tried Altering the foreign catalog to set this property but it complains does not support the following option(s): preferTimestampNTZ.
