You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/20 17:30:35 UTC
(datafusion) 01/01: fix: keep the time zone when unparsing timestamp literals (#25949)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-25949-9c626cd7d466c2e087b062a6cf9183c57b5879ed
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 39e2304222600d996f0927f0d2428ebd127e9c3a
Author: Mohd Quamar Tyagi <10...@users.noreply.github.com>
AuthorDate: Tue Oct 20 17:30:11 2026 +0000
fix: keep the time zone when unparsing timestamp literals (#25949)
## Which issue does this PR close?
- Closes #25945.
## Rationale for this change
When the unparser turns a timestamp literal that has a time zone into
SQL, it casts the value to `TIMESTAMP` instead of `TIMESTAMP WITH TIME
ZONE`. For example, `ScalarValue::TimestampSecond(Some(1704078000),
Some("+08:00"))` becomes `CAST('2024-01-01T11:00:00+08:00' AS
TIMESTAMP)`. PostgreSQL and DuckDB discard the offset when they cast
that string to a timestamp without a time zone, so the generated SQL no
longer refers to the same instant. Unparsing a `CAST` to the same type
already produces `TIMESTAMP WITH TIME ZONE`, so literals and casts
disagree.
## What changes are included in this PR?
`Unparser::handle_timestamp` now passes the literal's time zone to
`Dialect::timestamp_cast_dtype`, as the `CAST` path already does. Each
dialect therefore uses the same type for a timestamp literal as for a
cast to that type:
| Dialect | Type for a timestamp literal with a time zone |
|---|---|
| Default, PostgreSQL, DuckDB, Snowflake | `TIMESTAMP WITH TIME ZONE`
(was `TIMESTAMP`) |
| `CustomDialect` | the time-zone type given to
`with_timestamp_cast_dtype` (default `TIMESTAMP WITH TIME ZONE`; was the
type without a time zone) |
| MySQL, SQLite, BigQuery | unchanged: `DATETIME`, `TEXT`, `TIMESTAMP` |
Literals without a time zone are unchanged.
## What is the testing strategy for this PR?
Unit tests in `datafusion/sql/src/unparser/expr.rs`:
- `expr_to_sql_ok` and `test_timestamp_with_tz_format` expected
`TIMESTAMP` for literals with a time zone; they now expect `TIMESTAMP
WITH TIME ZONE`. The BigQuery cases still expect `TIMESTAMP`.
`test_timestamp_with_tz_format` also has a new Snowflake case.
- `custom_dialect_with_timestamp_cast_dtype_scalar_expr` now covers
literals with a time zone, for the default builder and for a dialect
that maps both types to `DATETIME`.
- `test_timestamp_literal_passes_tz_to_dialect` uses a test-only dialect
that writes the zone it receives into the cast type. It checks that a
literal and a cast both pass the literal's actual zone (`Asia/Tokyo`) to
the dialect.
## Are there any user-facing changes?
Yes. With the default, PostgreSQL, DuckDB, Snowflake and `CustomDialect`
dialects, unparsed SQL now casts timestamp literals that have a time
zone to the dialect's time-zone type. DataFusion still plans the
generated SQL to the same instant. There are no public API changes.
---------
Signed-off-by: Tyagiquamar <mo...@gmail.com>
Co-authored-by: Neil Conway <ne...@gmail.com>
Co-authored-by: Dmitrii Blaginin <dm...@blaginin.me>
---
datafusion/sql/src/unparser/expr.rs | 119 +++++++++++++++++++++++++++++-------
1 file changed, 98 insertions(+), 21 deletions(-)
diff --git a/datafusion/sql/src/unparser/expr.rs b/datafusion/sql/src/unparser/expr.rs
index 70ee725e80..40a45d106c 100644
--- a/datafusion/sql/src/unparser/expr.rs
+++ b/datafusion/sql/src/unparser/expr.rs
@@ -1265,7 +1265,7 @@ impl Unparser<'_> {
Ok(ast::Expr::Cast {
kind: ast::CastKind::Cast,
expr: Box::new(ast::Expr::value(SingleQuotedString(ts))),
- data_type: self.dialect.timestamp_cast_dtype(&time_unit, &None),
+ data_type: self.dialect.timestamp_cast_dtype(&time_unit, &tz.cloned()),
format: None,
})
}
@@ -2214,7 +2214,7 @@ mod tests {
ScalarValue::TimestampSecond(Some(10001), Some("+08:00".into())),
None,
),
- r#"CAST('1970-01-01T10:46:41+08:00' AS TIMESTAMP)"#,
+ r#"CAST('1970-01-01T10:46:41+08:00' AS TIMESTAMP WITH TIME ZONE)"#,
),
(
Expr::Literal(ScalarValue::TimestampMillisecond(Some(10001), None), None),
@@ -2225,7 +2225,7 @@ mod tests {
ScalarValue::TimestampMillisecond(Some(10001), Some("+08:00".into())),
None,
),
- r#"CAST('1970-01-01T08:00:10.001+08:00' AS TIMESTAMP)"#,
+ r#"CAST('1970-01-01T08:00:10.001+08:00' AS TIMESTAMP WITH TIME ZONE)"#,
),
(
Expr::Literal(ScalarValue::TimestampMicrosecond(Some(10001), None), None),
@@ -2236,7 +2236,7 @@ mod tests {
ScalarValue::TimestampMicrosecond(Some(10001), Some("+08:00".into())),
None,
),
- r#"CAST('1970-01-01T08:00:00.010001+08:00' AS TIMESTAMP)"#,
+ r#"CAST('1970-01-01T08:00:00.010001+08:00' AS TIMESTAMP WITH TIME ZONE)"#,
),
(
Expr::Literal(ScalarValue::TimestampNanosecond(Some(10001), None), None),
@@ -2247,7 +2247,7 @@ mod tests {
ScalarValue::TimestampNanosecond(Some(10001), Some("+08:00".into())),
None,
),
- r#"CAST('1970-01-01T08:00:00.000010001+08:00' AS TIMESTAMP)"#,
+ r#"CAST('1970-01-01T08:00:00.000010001+08:00' AS TIMESTAMP WITH TIME ZONE)"#,
),
(
Expr::Literal(ScalarValue::Time32Second(Some(10001)), None),
@@ -3082,19 +3082,37 @@ mod tests {
)
.build();
- for (dialect, identifier) in [
- (&default_dialect, "TIMESTAMP"),
- (&mysql_dialect, "DATETIME"),
+ let timestamp = ScalarValue::TimestampMillisecond(Some(1738285549123), None);
+ let timestamp_with_tz =
+ ScalarValue::TimestampMillisecond(Some(1738285549123), Some("+08:00".into()));
+
+ for (dialect, scalar, expected) in [
+ (
+ &default_dialect,
+ ×tamp,
+ "CAST('2025-01-31 01:05:49.123' AS TIMESTAMP)",
+ ),
+ (
+ &default_dialect,
+ ×tamp_with_tz,
+ "CAST('2025-01-31T09:05:49.123+08:00' AS TIMESTAMP WITH TIME ZONE)",
+ ),
+ (
+ &mysql_dialect,
+ ×tamp,
+ "CAST('2025-01-31 01:05:49.123' AS DATETIME)",
+ ),
+ (
+ &mysql_dialect,
+ ×tamp_with_tz,
+ "CAST('2025-01-31T09:05:49.123+08:00' AS DATETIME)",
+ ),
] {
let unparser = Unparser::new(dialect);
- let expr = Expr::Literal(
- ScalarValue::TimestampMillisecond(Some(1738285549123), None),
- None,
- );
+ let expr = Expr::Literal(scalar.clone(), None);
let ast = unparser.expr_to_sql(&expr)?;
let actual = format!("{ast}");
- let expected = format!(r#"CAST('2025-01-31 01:05:49.123' AS {identifier})"#);
assert_eq!(actual, expected);
}
@@ -3746,12 +3764,13 @@ mod tests {
let duckdb_dialect: Arc<dyn Dialect> = Arc::new(DuckDBDialect::new());
let bigquery_dialect: Arc<dyn Dialect> = Arc::new(BigQueryDialect::new());
+ let snowflake_dialect: Arc<dyn Dialect> = Arc::new(SnowflakeDialect::new());
for (dialect, scalar, expected) in [
(
Arc::clone(&default_dialect),
ScalarValue::TimestampSecond(Some(1757934000), Some("+00:00".into())),
- "CAST('2025-09-15T11:00:00+00:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T11:00:00+00:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&default_dialect),
@@ -3759,7 +3778,7 @@ mod tests {
Some(1757934000123),
Some("+01:00".into()),
),
- "CAST('2025-09-15T12:00:00.123+01:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T12:00:00.123+01:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&default_dialect),
@@ -3767,7 +3786,7 @@ mod tests {
Some(1757934000123456),
Some("-01:00".into()),
),
- "CAST('2025-09-15T10:00:00.123456-01:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T10:00:00.123456-01:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&default_dialect),
@@ -3775,12 +3794,12 @@ mod tests {
Some(1757934000123456789),
Some("+00:00".into()),
),
- "CAST('2025-09-15T11:00:00.123456789+00:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T11:00:00.123456789+00:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&duckdb_dialect),
ScalarValue::TimestampSecond(Some(1757934000), Some("+00:00".into())),
- "CAST('2025-09-15T11:00:00+00:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T11:00:00+00:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&duckdb_dialect),
@@ -3788,7 +3807,7 @@ mod tests {
Some(1757934000123),
Some("+01:00".into()),
),
- "CAST('2025-09-15T12:00:00.123+01:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T12:00:00.123+01:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&duckdb_dialect),
@@ -3796,7 +3815,7 @@ mod tests {
Some(1757934000123456),
Some("-01:00".into()),
),
- "CAST('2025-09-15T10:00:00.123456-01:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T10:00:00.123456-01:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&duckdb_dialect),
@@ -3804,7 +3823,7 @@ mod tests {
Some(1757934000123456789),
Some("+00:00".into()),
),
- "CAST('2025-09-15T11:00:00.123456789+00:00' AS TIMESTAMP)",
+ "CAST('2025-09-15T11:00:00.123456789+00:00' AS TIMESTAMP WITH TIME ZONE)",
),
(
Arc::clone(&bigquery_dialect),
@@ -3835,6 +3854,14 @@ mod tests {
),
"CAST('2025-09-15T11:00:00.123456789+00:00' AS TIMESTAMP)",
),
+ (
+ Arc::clone(&snowflake_dialect),
+ ScalarValue::TimestampMillisecond(
+ Some(1757934000123),
+ Some("+01:00".into()),
+ ),
+ "CAST('2025-09-15T12:00:00.123+01:00' AS TIMESTAMP WITH TIME ZONE)",
+ ),
] {
let unparser = Unparser::new(dialect.as_ref());
@@ -3846,6 +3873,56 @@ mod tests {
Ok(())
}
+ #[test]
+ fn test_timestamp_literal_passes_tz_to_dialect() -> Result<()> {
+ // Puts the time zone it receives into the cast type, so the generated
+ // SQL shows which zone the unparser passed to the dialect.
+ struct TzEchoDialect;
+
+ impl Dialect for TzEchoDialect {
+ fn identifier_quote_style(&self, _identifier: &str) -> Option<char> {
+ None
+ }
+
+ fn timestamp_cast_dtype(
+ &self,
+ _time_unit: &TimeUnit,
+ tz: &Option<Arc<str>>,
+ ) -> ast::DataType {
+ match tz {
+ Some(tz) => ast::DataType::Custom(
+ ObjectName::from(vec![Ident::new("TIMESTAMPTZ")]),
+ vec![tz.to_string()],
+ ),
+ None => ast::DataType::Timestamp(None, TimezoneInfo::None),
+ }
+ }
+ }
+
+ let unparser = Unparser::new(&TzEchoDialect);
+
+ let literal = Expr::Literal(
+ ScalarValue::TimestampSecond(Some(1704078000), Some("Asia/Tokyo".into())),
+ None,
+ );
+ let cast_expr = cast(
+ col("a"),
+ DataType::Timestamp(TimeUnit::Second, Some("Asia/Tokyo".into())),
+ );
+
+ for (expr, expected) in [
+ (
+ literal,
+ "CAST('2024-01-01T12:00:00+09:00' AS TIMESTAMPTZ(Asia/Tokyo))",
+ ),
+ (cast_expr, "CAST(a AS TIMESTAMPTZ(Asia/Tokyo))"),
+ ] {
+ let actual = format!("{}", unparser.expr_to_sql(&expr)?);
+ assert_eq!(actual, expected);
+ }
+ Ok(())
+ }
+
#[test]
fn test_bigquery_dialect_overrides() -> Result<()> {
let bigquery_dialect: Arc<dyn Dialect> = Arc::new(BigQueryDialect::new());
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org