datafusion.execution.time_zone is not used for basic time zone inference

Describe the bug

I would expect that setting SET datafusion.execution.time_zone = '+08:00' would lead to SELECT '2024-11-01T00:00:00.0+00:00'::timestamptz - '2024-11-01T00:00:00.0'::timestamp to give 8 hours (that is, the same as SELECT '2024-11-01T00:00:00.0+00:00'::timestamptz - '2024-11-01T00:00:00.0+08:00'::timestamptz). Some things do work e.g. SELECT '2024-11-01T00:00:00.0'::timestamptz does give 2024-11-01T00:00:00+08:00.

To Reproduce

No response

Expected behavior

No response

Additional context

No response

@alamb

cc @alamb I think you've written some tests for datafusion.execution.time_zone based on blames, I couldn't track down who originally implemented it

@adriangb

Hi @adriangb -- I am not sure who originally implemented it either. I think @Omega359 was thinking about how to specify / pass the default timezones to functions recently too

Yes, it's been on my radar for a while now. There are a number of issues around tz's some of which are generic tz issues, others are issues where using the default tz in config might be useful. A small but not complete list

Someone can probably take a look at #13130, it seems to be done it just has a doc check error

This may now be feasible after

Add SessionConfig reference to ScalarFunctionArgs #13519

Now that #16970 was merged this should now be feasible.

@Omega359

@Omega359 I know you've been doing a lot of work on this, should this be closed in favor of another tracking issue? Or is this the right issue and it is now possible to fix but hasn't been fixed yet?

I haven't tested the case in this ticket's description so it's quite possible it's still now working. I'll add a note to myself to look into it next week.

./target/release/datafusion-cli 
DataFusion CLI v51.0.0
> SET datafusion.execution.time_zone = '+08:00';
0 row(s) fetched. 
Elapsed 0.001 seconds.

> SELECT '2024-11-01T00:00:00.0+00:00'::timestamptz - '2024-11-01T00:00:00.0'::timestamp;
+---------------------------------------------------------------------+
| Utf8("2024-11-01T00:00:00.0+00:00") - Utf8("2024-11-01T00:00:00.0") |
+---------------------------------------------------------------------+
| 0 days 0 hours 0 mins 0.000000000 secs                              |
+---------------------------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.001 seconds.

> SELECT '2024-11-01T00:00:00.0+00:00'::timestamptz - '2024-11-01T00:00:00.0+08:00'::timestamptz;
+---------------------------------------------------------------------------+
| Utf8("2024-11-01T00:00:00.0+00:00") - Utf8("2024-11-01T00:00:00.0+08:00") |
+---------------------------------------------------------------------------+
| 0 days 8 hours 0 mins 0.000000000 secs                                    |
+---------------------------------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.001 seconds.

> SELECT '2024-11-01T00:00:00.0'::timestamptz ;
+-------------------------------+
| Utf8("2024-11-01T00:00:00.0") |
+-------------------------------+
| 2024-11-01T00:00:00+08:00     |
+-------------------------------+
1 row(s) fetched. 
Elapsed 0.001 seconds.

It looks like the examples in this ticket not been completed resolved.

@adriangb

Looked into this a bit and I think this is coming from the fact that arrow is limited in the ability to set a default TZ (it's just hardcoded).

Either:

A issue should be filed there to allow a different default and datafusion pass that in
Datafusion should transform the timestamp to apply the default TZ prior to passing it over to arrow to do the numeric op.

Looking at the code I wonder if an OptimizerRule for casting Timestamp(, None) type -> Timestamp(, default_tz) would be a good solution? It's not an area of the code I'm super familiar with but I think it would possibly be a lot less invasive a change.

@adriangb @alamb

It seems to me like '2024-11-01T00:00:00.0+00:00'::timestamptz should work fine without a new optimzier pass

When we resolve the cast to timestamptz shouldn't we add the session timezone then?

As in I bet the timezone is coming from something in DataFusion, not arrow 🤔

Existing tz's work, however '2024-11-01T00:00:00.0'::timestamp doesn't. The concern I have with handling it just in the cast is that I don't think it covers all the cases - for example, a table with a column of type Timezone(_, None). I could very well be wrong there as I'm obviously no expert on this.

@adriangb

@adriangb I'm going to do some digging into this. Hopefully you will have time to review if I do come up with a PR.

Yep please tag me and I'll find time this week.

@Omega359

I compared this against PostgreSQL 17, DuckDB 1.5.2 and SQLite 3.51 locally to pin down what the correct behavior actually is.

PostgreSQL and DuckDB agree on every case, and they agree with the expectation in the issue description. Both resolve timestamptz - timestamp by implicitly casting the naive side to timestamptz using the session timezone, and both return interval.

Session timezone Asia/Singapore (= +08:00 for DataFusion):

	PostgreSQL 17	DuckDB 1.5.2	DataFusion 54.0.0
`'2024-11-01T00:00:00.0'::timestamptz`	`2024-11-01 00:00:00+08`	`2024-11-01 00:00:00+08`	`2024-11-01T00:00:00+08:00` ✅
`tstz - tstz`	`08:00:00`	`08:00:00`	`8 hours` ✅
`tstz - ts`	`08:00:00`	`08:00:00`	`0` ❌
`ts::timestamptz`	`2024-11-01 00:00:00+08`	`2024-11-01 00:00:00+08`	`2024-11-01T00:00:00+08:00` ✅
`tstz - ts` with tz = `UTC`	`00:00:00`	—	`0` ✅

SQLite is not a useful reference here — it has no timezone-aware timestamp type and no session timezone at all. CAST(x AS timestamptz) falls through to NUMERIC affinity and returns 2024.

The scope is narrower than it looks

Worth flagging for whoever picks this up: the explicit cast in DataFusion already has the correct semantics today. It shifts the instant, it doesn't just relabel the type:

SET datafusion.execution.time_zone = '+08:00';
CREATE TABLE t AS SELECT
  '2024-11-01T00:00:00.0'::timestamp        AS ts,
  '2024-11-01T00:00:00.0+00:00'::timestamptz AS tstz;

SELECT to_unixtime(ts), to_unixtime(ts::timestamptz), to_unixtime(tstz) FROM t;
+------------+---------------+------------+
| 1730419200 | 1730390400    | 1730419200 |   -- ts::timestamptz correctly shifts -8h
+------------+---------------+------------+

SELECT tstz - ts, tstz - ts::timestamptz FROM t;
+---------+---------+
| 0 hours | 8 hours |   -- with the cast written by hand, the answer is already right
+---------+---------+

And EXPLAIN VERBOSE shows type_coercion leaves the expression completely untouched — no cast is inserted at all:

| initial_logical_plan             | Projection: t.tstz - t.ts |
| logical_plan after type_coercion | SAME TEXT AS ABOVE        |

So this looks like it can be fixed purely in binary-operator type coercion, by inserting the cast that DataFusion already implements correctly — no arrow-rs change and no new optimizer rule needed. @Omega359's concern about table columns of Timestamp(_, None) holds though: the repro above uses a real column rather than a folded literal, and it reproduces there too, so the fix does need to live in coercion rather than in literal casting.

One thing that will bite the fix

Filed separately as #25084: with a named session timezone, that cast currently errors on DST boundaries.

SET datafusion.execution.time_zone = 'America/New_York';
SELECT '2024-11-03T01:30:00'::timestamp::timestamptz;
-- Arrow error: Cast error: Cannot cast timezone to different timezone

Unambiguous times are fine; it's specifically the ambiguous fall-back hour and the nonexistent spring-forward hour, because arrow-cast's adjust_timestamp_to_timezone uses .single() on the local-time resolution. PostgreSQL and DuckDB both resolve those deterministically and identically (ambiguous → later offset, gap → shifted forward). Once coercion starts inserting this cast automatically, any query mixing tz-naive and tz-aware timestamps under a named session timezone will start hitting it.

test: characterization suite for timestamps with time zone (apache#25175

test: PostgreSQL differential coverage for timestamps with time zone (a…

fix: coerce timezone-naive minus timezone-aware timestamps at equal t…

fix: use session timezone for timestamp subtraction and comparison (a…

test: characterization suite for timestamps with time zone (apache#25175

test: PostgreSQL differential coverage for timestamps with time zone (a…

fix: coerce timezone-naive minus timezone-aware timestamps at equal t…

fix: use session timezone for timestamp subtraction and comparison (a…

Navigation Menu

Repository navigation

`datafusion.execution.time_zone` is not used for basic time zone inference #13212

Describe the bug

To Reproduce

Expected behavior

Additional context

The scope is narrower than it looks

One thing that will bite the fix

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

datafusion.execution.time_zone is not used for basic time zone inference #13212

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

adriangb commented on Nov 1, 2024

alamb commented on Nov 1, 2024

Omega359 commented on Nov 1, 2024

jonathanc-n commented on Nov 1, 2024

alamb commented on Nov 21, 2024

Omega359 commented on Aug 5, 2025

adriangb commented on Dec 13, 2025

Omega359 commented on Dec 13, 2025

Omega359 commented on Dec 14, 2025

Omega359 commented on Dec 14, 2025

alamb commented on Dec 14, 2025

Omega359 commented on Dec 15, 2025

kumarUjjawal commented on Sep 8, 2026

adriangb commented on Sep 8, 2026

adriangb commented on Sep 8, 2026

The scope is narrower than it looks

One thing that will bite the fix

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`datafusion.execution.time_zone` is not used for basic time zone inference #13212