Repository navigation
Make from_unixtime aware of execution timezone #12892
Description
Is your feature request related to a problem or challenge?
No response
Describe the solution you'd like
Applies an offset to the unixtime after the cast.
Describe alternatives you've considered
No response
Additional context
Datafusion's from_unixtime is not aware of timezone. The following code block demonstrates the interaction between me and datafusion-cli.
> set datafusion.execution.time_zone = '+08:00';
0 row(s) fetched.
Elapsed 0.000 seconds.
> select to_unixtime('2024-09-01 10:00:00+08:00');
+------------------------------------------------+
| to_unixtime(Utf8("2024-09-01 10:00:00+08:00")) |
+------------------------------------------------+
| 1725156000 |
+------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select from_unixtime(1725156000);
+----------------------------------+
| from_unixtime(Int64(1725156000)) |
+----------------------------------+
| 2024-09-01T02:00:00 |
+----------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.Specifically, when converting a date to unixtime with the to_unixtime function, we can provide a timezone and the cast result could reveal the timezone. However, when converting the unixtime back to a date, Datafusion is not aware of timezone. Yes, the cast result is correct, but the timezone info is discarded and only a date with timezone UTC+0 is returned.
I wonder if we can make the from_unixtime function aware of timezone. For example, by setting the execution timezone through the datafusion.execution.time_zone configuration, we can apply an offset to the cast result so the timezone info could be revealed.
Activity
Omega359 commentedon Oct 12, 2024
UDF's currently do not have access to the DF context which would make implementing this using configuration a bit difficult. What could be easily supported is allowing an additional argument 'tz' which would be used to generate the timestamp in tz aware fashion.
buraksenn commentedon Oct 21, 2024
@Omega359 would that be desirable? I can start working on to overload
from_unixtimefunction with additional time_zone parameter as you've mentioned.Omega359 commentedon Oct 21, 2024
Sounds good. I would like to still think about what it would take to get udf's access to the config though.
alamb commentedon Nov 21, 2024
This may now be feasible after
SessionConfigreference toScalarFunctionArgs#13519findepi commentedon Jun 26, 2025
Omega359 commentedon Aug 5, 2025
Now that #16970 was merged this should now be feasible.
fix: `from_unixtime` should respect `datafusion.execution.time_zone` (a…
test: characterization suite for timestamps with time zone (apache#25175
test: characterization suite for timestamps with time zone (apache#25175
test: PostgreSQL differential coverage for timestamps with time zone (a…
test: characterization suite for timestamps with time zone (apache#25175
test: PostgreSQL differential coverage for timestamps with time zone (a…