Repository navigation
Support AtTimeZone node in SqlToRel #9580
Description
Is your feature request related to a problem or challenge?
I would like SqlToRel to be able to construct a LogicalPlan from the a query string containing the AT TIME ZONE clause. Right now it's unsupported:
create table t (a timestamptz);
insert into t values('2000-12-01 04:04:12');
select a AT TIME ZONE 'UTC' from t;
This feature is not implemented: Unsupported ast node in sqltorel: AtTimeZone { timestamp: Identifier(Ident { value: "a", quote_style: None }), time_zone: "UTC" }Describe the solution you'd like
AT TIME ZONE clauses can be parsed and converted into logical plans.
Describe alternatives you've considered
No response
Additional context
No response
Reacted by Andrew Lamb
Activity
tinfoil-knight commentedon Mar 13, 2024
take
tinfoil-knight commentedon Mar 13, 2024
I'm picking up this issue.
This error (
Unsupported ast node in sqltorel) comes from this line:https://github.com/apache/arrow-datafusion/blob/0302d6530a5a7fffe7da40e0f3b13794a9c693ff/datafusion/sql/src/expr/mod.rs#L579
AtTimeZoneSQL Expr is already provided bysqlparser.Intended Change:
Add a condition to handle
SQLExpr::AtTimeZonein the match case insql_expr_to_logical_expr_internalhere:https://github.com/apache/arrow-datafusion/blob/0302d6530a5a7fffe7da40e0f3b13794a9c693ff/datafusion/sql/src/expr/mod.rs#L162-L172
And, add a scalar function to convert a date to a specific time zone as per the HowTo here so that it can be used by
sql_named_function_to_exprto get the Result.alamb commentedon Mar 14, 2024
Since the Timezone is encoded in the DataType I think you could call
Expr::Castwith a DataType that had the appropriate timezone -- e.g. set the timezone correctly https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html#variant.Timestamp and then cast the expression to that typeThus you may not need to make a new scalar function
tinfoil-knight commentedon Mar 16, 2024
Yeah. This worked. Thank you for the suggestion.