neilconway opened a new issue, #25945:
URL: https://github.com/apache/datafusion/issues/25945
### Describe the bug
When the unparser converts a timestamp literal that has a time zone to SQL,
the generated SQL casts the value to `TIMESTAMP`, not `TIMESTAMP WITH TIME
ZONE`. This happens with the default, PostgreSQL and DuckDB dialects. When
Postgres or DuckDB evaluates the generated SQL, the UTC offset is discarded, so
the value no longer refers to the same instant. Unparsing a `CAST` to the same
type does produce `TIMESTAMP WITH TIME ZONE`.
### To Reproduce
A program depending only on `datafusion`:
```rust
use datafusion::arrow::datatypes::{DataType, TimeUnit};
use datafusion::common::ScalarValue;
use datafusion::error::Result;
use datafusion::prelude::*;
use datafusion::sql::unparser::Unparser;
use datafusion::sql::unparser::dialect::{DuckDBDialect, PostgreSqlDialect};
use datafusion::sql::unparser::expr_to_sql;
fn main() -> Result<()> {
// 2024-01-01T03:00:00Z
let ts = lit(ScalarValue::TimestampSecond(
Some(1704078000),
Some("+08:00".into()),
));
let tz_type = DataType::Timestamp(TimeUnit::Second,
Some("+08:00".into()));
println!("literal: {}", expr_to_sql(&ts)?);
println!("literal (postgres): {}", Unparser::new(&PostgreSqlDialect
{}).expr_to_sql(&ts)?);
println!("literal (duckdb): {}",
Unparser::new(&DuckDBDialect::new()).expr_to_sql(&ts)?);
println!("cast: {}", expr_to_sql(&cast(col("x"),
tz_type))?);
Ok(())
}
```
Output:
```
literal: CAST('2024-01-01T11:00:00+08:00' AS TIMESTAMP)
literal (postgres): CAST('2024-01-01T11:00:00+08:00' AS TIMESTAMP)
literal (duckdb): CAST('2024-01-01T11:00:00+08:00' AS TIMESTAMP)
cast: CAST(x AS TIMESTAMP WITH TIME ZONE)
```
Evaluating the generated literal SQL in PostgreSQL 18.6 and DuckDB 1.5.5
(session time zone UTC) returns `2024-01-01 11:00:00`, a timestamp without time
zone. The literal's value is `2024-01-01 03:00:00` UTC.
### Expected behavior
The generated SQL keeps the literal's time zone, as it does for a cast to
the same type.
### Additional context
Reproduced on `main` at commit 2b1fcae86.
*This bug report was generated by Claude.*
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
