Skip to content

Commit 41496d9

Browse files
adriangbclaude
authored andcommitted
docs: document the TIMESTAMP WITH TIME ZONE type mapping and fix a stale comment (apache#25171)
## Which issue does this PR close? This PR closes no issue. It changes documentation and one comment, and it changes no behaviour. - Related to apache#25166 ## Rationale for this change ### What a user hits There is no bug to reproduce. There is a mapping a user cannot look up. To find out what `TIMESTAMP WITH TIME ZONE` produces, a user must run it: ```sql -- default configuration: datafusion.execution.time_zone is unset SELECT arrow_typeof('2000-01-01T00:00:00'::TIMESTAMPTZ); +---------------+ | Timestamp(ns) | +---------------+ ``` The type has no timezone. Set the session timezone and the same expression gains one: ```sql SET datafusion.execution.time_zone = 'America/New_York'; SELECT arrow_typeof('2000-01-01T00:00:00'::TIMESTAMPTZ); +-----------------------------------+ | Timestamp(ns, "America/New_York") | +-----------------------------------+ ``` `docs/source/user-guide/sql/data_types.md` holds the SQL-to-Arrow type table. Three things were absent from it: - any row for `TIMESTAMPTZ` or `TIMESTAMP WITH TIME ZONE` - any note that the result depends on `datafusion.execution.time_zone` - any note that `TIMESTAMP(p)` is accepted, and which values of `p` are valid A user who reads the page learns none of the above. The comment in `datafusion/sql/src/planner.rs` was worse than absent. It stated the opposite of the code: ```rust // OUTPUT: [ArrowDataType] Timestamp<TimeUnit, Some(Time Zone)> self.context_provider.options().execution.time_zone.clone() ``` The expression is an `Option<String>` that is `None` by default, so the arm produces `Timestamp(unit, None)` on a default install. ### What changes for a user The type table gains the two absent spellings and the precision rules, so a user can look the mapping up instead of a run of `arrow_typeof`. The page also states that the timezone component comes from a session setting that is unset by default, and it links the open issue on that default. No behaviour changes. ### The technical detail The comment went stale in apache#18359, which changed `datafusion.execution.time_zone` from `String` to `Option<String>` with a `None` default. That PR dropped the `Some(...)` wrapper from the expression as a mechanical consequence of the type change, and left the two comment lines above it untouched. Whether the timezone-naive default is right is under discussion in apache#25166. This PR only makes the documentation match the current code. It deliberately changes no behaviour. ### Field research The table is exactly where a user who moves from another engine looks, so I measured both reference engines. PostgreSQL 17.11 (`postgres:17`): ``` => CREATE TABLE t(b TIMESTAMPTZ, c TIMESTAMP WITH TIME ZONE); => SELECT column_name, data_type FROM information_schema.columns WHERE table_name='t'; b | timestamp with time zone c | timestamp with time zone => SELECT pg_typeof('2024-01-01'::timestamptz); timestamp with time zone ``` DuckDB 1.5.2: ``` D SELECT typeof('2024-01-01 00:00:00'::TIMESTAMPTZ); TIMESTAMP WITH TIME ZONE D SELECT typeof('2024-01-01'::TIMESTAMP WITH TIME ZONE); TIMESTAMP WITH TIME ZONE ``` Both engines always resolve the type to a zone-aware type. Neither has an unset session timezone, so neither can produce a naive result from this SQL type. DataFusion produces a naive result on a default install. That divergence is the subject of apache#25166. The precision rules diverge too: - PostgreSQL accepts `p` from 0 to 6. `TIMESTAMP(1)` and `TIMESTAMP(4)` are valid. `TIMESTAMP(9)` raises a warning and reduces `p` to 6. - DuckDB accepts `p` from 0 to 9 and rounds up to the next storage unit. `TIMESTAMP(1)` gives `timestamp_ms`, `TIMESTAMP(4)` gives `timestamp` and `TIMESTAMP(7)` gives `timestamp_ns`. - DataFusion accepts 0, 3, 6 and 9 alone, and rejects every other value. ## What changes are included in this PR? - `datafusion/sql/src/planner.rs`: correct the `SQLDataType::Timestamp` comment to say `Timestamp<TimeUnit, Time Zone>`, note that the configured time zone is an `Option` that is unset by default, and point at apache#25166. There is no code change. - `docs/source/user-guide/sql/data_types.md`: add the `TIMESTAMP WITH TIME ZONE` and `TIMESTAMPTZ` row to the Date/Time Types table, document the optional `(p)` precision and the units it selects, and state that the timezone component comes from `datafusion.execution.time_zone`, which is unset by default, with a link to the open issue. ## What is the testing strategy for this PR? There are no tests. This is a comment fix plus a documentation fix, with no behaviour change. Every mapping in the docs was verified against a `cargo build --bin datafusion-cli` build of this branch rather than read off the source: ``` > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP); -- Timestamp(ns) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(0)); -- Timestamp(s) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(3)); -- Timestamp(ms) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(6)); -- Timestamp(µs) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(9)); -- Timestamp(ns) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(1)); -- Error: Unsupported SQL type TIMESTAMP(1) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(2)); -- Error: Unsupported SQL type TIMESTAMP(2) -- default configuration (datafusion.execution.time_zone unset) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMPTZ); -- Timestamp(ns) > select arrow_typeof(CAST('2000-01-01T00:00:00' AS TIMESTAMP WITH TIME ZONE)); -- Timestamp(ns) > select arrow_typeof(CAST('2000-01-01T00:00:00' AS TIMESTAMP(6) WITH TIME ZONE)); -- Timestamp(µs) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMPTZ(3)); -- Timestamp(ms) > SET datafusion.execution.time_zone = 'America/New_York'; > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMPTZ); -- Timestamp(ns, "America/New_York") > select arrow_typeof(CAST('2000-01-01T00:00:00' AS TIMESTAMP(3) WITH TIME ZONE)); -- Timestamp(ms, "America/New_York") > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP); -- Timestamp(ns) > select arrow_typeof('2000-01-01T00:00:00'::TIMESTAMP(0)); -- Timestamp(s) ``` The DDL path gives the same answers: ``` > CREATE TABLE t(a TIMESTAMP, b TIMESTAMPTZ, c TIMESTAMP(6), d TIMESTAMP(0) WITH TIME ZONE); > describe t; a | Timestamp(ns) b | Timestamp(ns) c | Timestamp(µs) d | Timestamp(s) ``` `./ci/scripts/doc_prettier_check.sh`, `cargo fmt --all` and `cargo clippy --all-targets -- -D warnings` all pass. The Sphinx docs build cleanly. Its one warning is the pre-existing absent generated `_static/data/deps.svg`, which is unrelated to this change. ## Are there any user-facing changes? Documentation only. There are no behaviour changes and no API changes. ## Merge order There is no file-level conflict with any PR in flight. apache#25175 adds only `datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt`, and this PR touches `planner.rs` and `data_types.md`. The two do share a dependency. Sections 0 to 4 of apache#25175 pin the same timezone-naive result that the new paragraph describes. A fix for apache#25166 must update this page and that file in one change. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5 <[email protected]>
1 parent fa0ca33 commit 41496d9

2 files changed

Lines changed: 43 additions & 7 deletions

File tree

‎datafusion/sql/src/planner.rs‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -775,7 +775,13 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
775775
{
776776
// Timestamp With Time Zone
777777
// INPUT : [SQLDataType] TimestampTz + [Config] Time Zone
778-
// OUTPUT: [ArrowDataType] Timestamp<TimeUnit, Some(Time Zone)>
778+
// OUTPUT: [ArrowDataType] Timestamp<TimeUnit, Time Zone>
779+
//
780+
// Note that the configured time zone is an `Option` that is
781+
// unset by default, so by default this yields the
782+
// timezone-naive `Timestamp<TimeUnit, None>`. Whether that
783+
// should remain the behavior is tracked in
784+
// https://github.com/apache/datafusion/issues/25166
779785
self.context_provider.options().execution.time_zone.clone()
780786
} else {
781787
// Timestamp Without Time zone

‎docs/source/user-guide/sql/data_types.md‎

Lines changed: 36 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -105,12 +105,42 @@ The maximum supported precision for `DECIMAL` types is 76.
105105

106106
## Date/Time Types
107107

108-
| SQL DataType | Arrow DataType |
109-
| ------------ | :------------------------------- |
110-
| `DATE` | `Date32` |
111-
| `TIME` | `Time64(Nanosecond)` |
112-
| `TIMESTAMP` | `Timestamp(Nanosecond, None)` |
113-
| `INTERVAL` | `Interval(IntervalMonthDayNano)` |
108+
| SQL DataType | Arrow DataType |
109+
| ------------------------------------------------------------- | :--------------------------------------------------------- |
110+
| `DATE` | `Date32` |
111+
| `TIME` | `Time64(Nanosecond)` |
112+
| `TIMESTAMP`, `TIMESTAMP(p)`, `TIMESTAMP(p) WITHOUT TIME ZONE` | `Timestamp(unit, None)` |
113+
| `TIMESTAMPTZ(p)`, `TIMESTAMP(p) WITH TIME ZONE` | `Timestamp(unit, None)` by default — see the warning below |
114+
| `INTERVAL` | `Interval(MonthDayNano)` |
115+
116+
:::{warning}
117+
`TIMESTAMPTZ` and `TIMESTAMP WITH TIME ZONE` do **not** give a timezone-aware
118+
type by default. The zone comes from the
119+
[`datafusion.execution.time_zone`] setting, and that setting is unset unless you
120+
set it. So `'2024-01-01T12:00:00Z'::timestamptz` gives `Timestamp(unit, None)`
121+
by default: the result type does not keep timezone metadata.
122+
123+
An explicit offset in the input is still applied. DataFusion normalizes the
124+
value to UTC. For example, with the default setting,
125+
`'2024-01-01T12:00:00+07:00'::timestamptz` gives `2024-01-01T05:00:00`. It is
126+
not read as the local time `12:00:00`.
127+
128+
This is a known bug. See [issue #25166]. PostgreSQL and DuckDB always give an
129+
aware type here, because their session time zone always has a value.
130+
131+
Set `datafusion.execution.time_zone` to make these types aware. `'UTC'` is a
132+
good value.
133+
:::
134+
135+
`unit` comes from the optional precision `p` on the two `TIMESTAMP` rows. Use
136+
`0`, `3`, `6` or `9` for `Second`, `Millisecond`, `Microsecond` or `Nanosecond`.
137+
DataFusion rejects each other value of `p`. If you omit `p`, the unit is
138+
`Nanosecond`. The precision goes after `TIMESTAMPTZ`, but before
139+
`WITH TIME ZONE`: use `TIMESTAMPTZ(3)` or `TIMESTAMP(3) WITH TIME ZONE`.
140+
`DATE`, `TIME` and `INTERVAL` do not accept a precision.
141+
142+
[`datafusion.execution.time_zone`]: ../configs.md
143+
[issue #25166]: https://github.com/apache/datafusion/issues/25166
114144

115145
## Boolean Types
116146

0 commit comments

Comments
 (0)