You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "codetyri0n (via GitHub)" <gi...@apache.org> on 2025/11/29 11:28:47 UTC
[PR] Timezone aware extract SQL expression [datafusion]
codetyri0n opened a new pull request, #18990:
URL: https://github.com/apache/datafusion/pull/18990
## Which issue does this PR close?
Closes #18228
## What changes are included in this PR?
timezone config options support and subsequent results produced aware of the set timezone
## Are these changes tested?
slt
## Are there any user-facing changes?
yes
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-4233356769
Thank you for your contribution. Unfortunately, this pull request is stale because it has been open 60 days with no activity. Please remove the stale label or comment or this will be closed in 7 days.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2572970319
##########
datafusion/functions/src/datetime/mod.rs:
##########
@@ -37,6 +44,55 @@ pub mod to_local_time;
pub mod to_timestamp;
pub mod to_unixtime;
+// Adjusts a timestamp to local time by applying the timezone offset.
Review Comment:
`adjust_to_local_time` refactored to mod.rs as a common helper function for both date_part and to_local_time
CC: @martin-g
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3591725344
thank you for the improvements! @martin-g
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3884767796
> this comment
Sorry, haven't had bandwidth to be involved lately! I can look at this during the weekend although if you can/would be willing to do the needful earlier please feel free to do so! : )
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3602255245
Apologies on the review delay though I hope to get to it in the next few days - honest!
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2573062895
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -193,12 +201,83 @@ impl ScalarUDFImpl for DatePartFunc {
ColumnarValue::Scalar(scalar) => scalar.to_array()?,
};
+ let (is_timezone_aware, tz_str_opt) = match array.data_type() {
+ Timestamp(_, Some(tz_str)) => (true, Some(Arc::clone(tz_str))),
+ _ => (false, None),
+ };
+
let part_trim = part_normalization(&part);
+ let is_epoch = is_epoch(&part);
+
+ // Epoch is timezone-independent - it always returns seconds since 1970-01-01 UTC
+ let array = if is_epoch {
+ array
+ } else if is_timezone_aware {
+ // For timezone-aware timestamps, extract in their own timezone
+ match tz_str_opt.as_ref() {
+ Some(tz_str) => {
+ let tz = match tz_str.parse::<Tz>() {
+ Ok(tz) => tz,
+ Err(_) => return exec_err!("Invalid timezone"),
Review Comment:
Or
```
let tz: Tz = tz_str.parse::<Tz>().map_err(|e| exec_datafusion_err!("Invalid timezone {tz_str}: {e:?}"))?;
```
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3591752569
I'll try and do a full review of this today or tomorrow, thanks for the work on it
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3735097911
sorry for this... ill resolve it properly first thing tomorrow
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2750048507
##########
datafusion/functions/src/datetime/common.rs:
##########
@@ -23,22 +23,89 @@ use arrow::array::{
StringArrayType, StringViewArray,
};
use arrow::compute::DecimalCast;
-use arrow::compute::kernels::cast_utils::string_to_datetime;
-use arrow::datatypes::{DataType, TimeUnit};
+use arrow::compute::kernels::cast_utils::{
+ string_to_datetime, string_to_timestamp_nanos,
+};
+use arrow::datatypes::{ArrowTimestampType, DataType, TimeUnit};
use arrow_buffer::ArrowNativeType;
use chrono::LocalResult::Single;
use chrono::format::{Parsed, StrftimeItems, parse};
-use chrono::{DateTime, TimeZone, Utc};
+use chrono::{DateTime, MappedLocalTime, TimeDelta, TimeZone, Utc};
use datafusion_common::cast::as_generic_string_array;
use datafusion_common::{
DataFusionError, Result, ScalarValue, exec_datafusion_err, exec_err,
internal_datafusion_err, unwrap_or_internal_err,
};
use datafusion_expr::ColumnarValue;
+use std::ops::Add;
/// Error message if nanosecond conversion request beyond supported interval
const ERR_NANOSECONDS_NOT_SUPPORTED: &str = "The dates that can be represented as nanoseconds have to be between 1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.854775804";
+pub fn adjust_to_local_time<T: ArrowTimestampType>(ts: i64, tz: Tz) -> Result<i64> {
+ fn convert_timestamp<F>(ts: i64, converter: F) -> Result<DateTime<Utc>>
+ where
+ F: Fn(i64) -> MappedLocalTime<DateTime<Utc>>,
+ {
+ match converter(ts) {
+ MappedLocalTime::Ambiguous(earliest, latest) => exec_err!(
+ "Ambiguous timestamp. Do you mean {:?} or {:?}",
+ earliest,
+ latest
+ ),
+ MappedLocalTime::None => exec_err!(
+ "The local time does not exist because there is a gap in the local time."
+ ),
+ Single(date_time) => Ok(date_time),
+ }
+ }
+
+ let date_time = match T::UNIT {
+ TimeUnit::Nanosecond => Utc.timestamp_nanos(ts),
+ TimeUnit::Microsecond => convert_timestamp(ts, |ts| Utc.timestamp_micros(ts))?,
+ TimeUnit::Millisecond => convert_timestamp(ts, |ts| Utc.timestamp_millis_opt(ts))?,
+ TimeUnit::Second => convert_timestamp(ts, |ts| Utc.timestamp_opt(ts, 0))?,
+ };
+
+ // Get the timezone offset for this datetime
+ let tz_offset = tz.offset_from_utc_datetime(&date_time.naive_utc());
+ // Convert offset to seconds - offset is formatted like "+01:00" or "-05:00"
+ let offset_str = format!("{tz_offset}");
Review Comment:
When working on merging updates from main I noticed that this function in to_local_time is much simplier than this:
```Rust
let offset_seconds: i64 = tz
.offset_from_utc_datetime(&date_time.naive_utc())
.fix()
.local_minus_utc() as i64;
```
Did this function come from somewhere else or did you make this change for some other reason?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2582273520
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -240,23 +314,43 @@ impl ScalarUDFImpl for DatePartFunc {
}
}
+fn adjust_timestamp_array<T: ArrowTimestampType>(
+ array: &ArrayRef,
+ tz: Tz,
+) -> Result<ArrayRef> {
+ let mut builder = PrimitiveBuilder::<T>::new();
+ let primitive_array = as_primitive_array::<T>(array)?;
+ for ts_opt in primitive_array.iter() {
+ match ts_opt {
+ None => builder.append_null(),
+ Some(ts) => {
+ let adjusted_ts = adjust_to_local_time::<T>(ts, tz)?;
+ builder.append_value(adjusted_ts);
+ }
+ }
+ }
+ Ok(Arc::new(builder.finish()))
+}
+
fn is_epoch(part: &str) -> bool {
let part = part_normalization(part);
Review Comment:
This work seems to be done twice - once before this function is called and once in the function. Perhaps we should just remove this part_normalization() call and pass the result of the previous call on line 209.
##########
datafusion/functions/src/datetime/mod.rs:
##########
@@ -37,6 +44,55 @@ pub mod to_local_time;
pub mod to_timestamp;
pub mod to_unixtime;
+// Adjusts a timestamp to local time by applying the timezone offset.
Review Comment:
Might want to put in common.rs instead.
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -240,23 +314,43 @@ impl ScalarUDFImpl for DatePartFunc {
}
}
+fn adjust_timestamp_array<T: ArrowTimestampType>(
+ array: &ArrayRef,
+ tz: Tz,
+) -> Result<ArrayRef> {
+ let mut builder = PrimitiveBuilder::<T>::new();
+ let primitive_array = as_primitive_array::<T>(array)?;
+ for ts_opt in primitive_array.iter() {
+ match ts_opt {
+ None => builder.append_null(),
+ Some(ts) => {
+ let adjusted_ts = adjust_to_local_time::<T>(ts, tz)?;
+ builder.append_value(adjusted_ts);
+ }
+ }
+ }
+ Ok(Arc::new(builder.finish()))
+}
+
fn is_epoch(part: &str) -> bool {
let part = part_normalization(part);
matches!(part.to_lowercase().as_str(), "epoch")
}
-// Try to remove quote if exist, if the quote is invalid, return original string and let the downstream function handle the error
fn part_normalization(part: &str) -> &str {
part.strip_prefix(|c| c == '\'' || c == '\"')
.and_then(|s| s.strip_suffix(|c| c == '\'' || c == '\"'))
.unwrap_or(part)
}
-/// Invoke [`date_part`] on an `array` (e.g. Timestamp) and convert the
-/// result to a total number of seconds, milliseconds, microseconds or
-/// nanoseconds
+fn interpret_session_timezone(tz_str: &str) -> Result<Tz> {
+ match tz_str.parse::<Tz>() {
+ Ok(tz) => Ok(tz),
+ Err(err) => exec_err!("Invalid timezone '{tz_str}': {err}"),
+ }
+}
+
fn seconds_as_i32(array: &dyn Array, unit: TimeUnit) -> Result<ArrayRef> {
- // Nanosecond is neither supported in Postgres nor DuckDB, to avoid dealing
Review Comment:
Why remove this comment?
##########
datafusion/sqllogictest/test_files/extract_tz.slt:
##########
@@ -0,0 +1,158 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+# Tests for timezone-aware extract SQL statement support.
+# Test with different timezone
+statement ok
+SET datafusion.execution.time_zone = '-03:00';
+
+query I
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-11-18 10:00:00');
+----
+7
+
+query II
+SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-10-30 10:45:30'),
+ EXTRACT(SECOND FROM TIMESTAMP '2023-10-30 10:45:30');
Review Comment:
It would be good to test these with timezone offset like -3:30 (newfoundland)
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -240,23 +314,43 @@ impl ScalarUDFImpl for DatePartFunc {
}
}
+fn adjust_timestamp_array<T: ArrowTimestampType>(
+ array: &ArrayRef,
+ tz: Tz,
+) -> Result<ArrayRef> {
+ let mut builder = PrimitiveBuilder::<T>::new();
+ let primitive_array = as_primitive_array::<T>(array)?;
+ for ts_opt in primitive_array.iter() {
+ match ts_opt {
+ None => builder.append_null(),
+ Some(ts) => {
+ let adjusted_ts = adjust_to_local_time::<T>(ts, tz)?;
+ builder.append_value(adjusted_ts);
+ }
+ }
+ }
+ Ok(Arc::new(builder.finish()))
+}
+
fn is_epoch(part: &str) -> bool {
let part = part_normalization(part);
matches!(part.to_lowercase().as_str(), "epoch")
}
-// Try to remove quote if exist, if the quote is invalid, return original string and let the downstream function handle the error
Review Comment:
Why remove this comment?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2572970319
##########
datafusion/functions/src/datetime/mod.rs:
##########
@@ -37,6 +44,55 @@ pub mod to_local_time;
pub mod to_timestamp;
pub mod to_unixtime;
+// Adjusts a timestamp to local time by applying the timezone offset.
Review Comment:
`adjust_to_local_time` refactored to mod.rs as it was a common helper function for both date_part and to_local_time
CC: @martin-g
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2573021219
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -193,12 +201,83 @@ impl ScalarUDFImpl for DatePartFunc {
ColumnarValue::Scalar(scalar) => scalar.to_array()?,
};
+ let (is_timezone_aware, tz_str_opt) = match array.data_type() {
+ Timestamp(_, Some(tz_str)) => (true, Some(Arc::clone(tz_str))),
+ _ => (false, None),
+ };
+
let part_trim = part_normalization(&part);
+ let is_epoch = is_epoch(&part);
+
+ // Epoch is timezone-independent - it always returns seconds since 1970-01-01 UTC
+ let array = if is_epoch {
+ array
+ } else if is_timezone_aware {
+ // For timezone-aware timestamps, extract in their own timezone
+ match tz_str_opt.as_ref() {
+ Some(tz_str) => {
+ let tz = match tz_str.parse::<Tz>() {
+ Ok(tz) => tz,
+ Err(_) => return exec_err!("Invalid timezone"),
Review Comment:
It would be nice to add `tz_str` to the error message. It would help in debugging.
Also, these 4 lines are repeated at line 247. Maybe you should extract them to a helper function
```suggestion
Err(err) => return exec_err!("Invalid timezone '{tz_str}': {err}"),
```
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -124,7 +131,7 @@ impl DatePartFunc {
],
Volatility::Immutable,
Review Comment:
```suggestion
Volatility::Stable,
```
The volatility depends on the session timezone.
`current_date` and `now` also use `Stable`
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3591597864
Really sorry that the previous PR was closed because I forgot to `git add` before force pushing the conflicts that were resolved with main 😠💀
BUT HOWEVER:
- all previous comments and objective of the issue is are addressed with this and is fit for approval/merge (pending latest comments, if any)
@Omega359 @martin-g
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3775245047
> after a series of conflict resolving clippy tells me this is unused, any idea @Omega359 ? <img alt="Screenshot 2026-01-13 at 3 00 02 AM" width="568" height="90" src="https://private-user-images.githubusercontent.com/111196722/534796430-923d8cb8-16d9-4de4-bc67-65a5641aee01.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3Njg5NDc5NDQsIm5iZiI6MTc2ODk0NzY0NCwicGF0aCI6Ii8xMTExOTY3MjIvNTM0Nzk2NDMwLTkyM2Q4Y2I4LTE2ZDktNGRlNC1iYzY3LTY1YTU2NDFhZWUwMS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjYwMTIwJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI2MDEyMFQyMjIwNDRaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT04OTE5ZTZkYmQxZTc4Zjk4MGM0ZjQ2MDJjMTRlMjU0NzMyZTU5NWJiMGVkZTI3ZjA2YzMxYWY5M2EzYjY5MzE4JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.8BbQacdBvldMmKe0fJqQx7xG_cORORByX_4OH2Y4fCU">
I'll try and take a look this week but I'm rather swamped at the moment with non-df related work :(
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3608240617
I believe this is ready to be merged, thanks! I'll try and find a committer to do the last review and put it into the merge queue.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3681798638
> I'll try and review this in the next day or two.
>
> @codetyri0n there appears to be some conflicts with this PR -- could you please resolve them?
hi @alamb , I've resolved the conflicts a few days back (icymi!)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3735017409
Still failing ci tests ...
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3740571059
after a series of conflict resolving clippy tells me this is unused, any idea @Omega359 ?
<img width="568" height="90" alt="Screenshot 2026-01-13 at 3 00 02 AM" src="https://github.com/user-attachments/assets/923d8cb8-16d9-4de4-bc67-65a5641aee01" />
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #18990: Timezone aware extract SQL expression
URL: https://github.com/apache/datafusion/pull/18990
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2572970762
##########
datafusion/sqllogictest/test_files/extract_tz.slt:
##########
@@ -0,0 +1,158 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+# Tests for timezone-aware extract SQL statement support.
+# Test with different timezone
+statement ok
+SET datafusion.execution.time_zone = '-03:00';
+
+query I
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-11-18 10:00:00');
+----
+7
+
+query II
+SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-10-30 10:45:30'),
+ EXTRACT(SECOND FROM TIMESTAMP '2023-10-30 10:45:30');
+----
+45 30
+
+query III
+SELECT EXTRACT(YEAR FROM DATE '2023-10-30'),
+ EXTRACT(MONTH FROM DATE '2023-10-30'),
+ EXTRACT(DAY FROM DATE '2023-10-30');
+----
+2023 10 30
+
+query I
+SELECT EXTRACT(HOUR FROM CAST(NULL AS TIMESTAMP));
+----
+NULL
+
+statement ok
+SET datafusion.execution.time_zone = '+04:00';
+
+query I
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-10-30 02:00:00');
+----
+6
+
+query III
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-10-30 18:20:59'),
+ EXTRACT(MINUTE FROM TIMESTAMP '2023-10-30 18:20:59'),
+ EXTRACT(SECOND FROM TIMESTAMP '2023-10-30 18:20:59');
+----
+22 20 59
+
+query II
+SELECT EXTRACT(DOW FROM DATE '2025-11-01'),
+ EXTRACT(DOY FROM DATE '2026-12-31');
+----
+6 365
+
+statement ok
+SET datafusion.execution.time_zone = '+00:00';
+
+query I
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-10-30 10:45:30+02:00');
+----
+8
+
+query I
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-10-30 10:45:30-05:00');
+----
+15
+
+query II
+SELECT EXTRACT(YEAR FROM TIMESTAMP '2026-11-30 10:45:30Z'),
+ EXTRACT(MONTH FROM TIMESTAMP '2023-10-30 10:45:30Z');
+----
+2026 10
+
+query III
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-10-30 18:20:59+04:00'),
+ EXTRACT(MINUTE FROM TIMESTAMP '2023-10-30 18:20:59+04:00'),
+ EXTRACT(SECOND FROM TIMESTAMP '2023-10-30 18:20:59+04:00');
+----
+14 20 59
+
+query II
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-10-30 10:25:30+02:30'),
+ EXTRACT(MINUTE FROM TIMESTAMP '2023-10-30 18:20:59-04:30');
+----
+7 50
+
+query III
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-10-30 18:20:59-08:00'),
+ EXTRACT(DAY FROM TIMESTAMP '2023-10-30 18:20:59-07:00'),
+ EXTRACT(DAY FROM TIMESTAMP '2023-10-30 07:20:59+12:00');
+----
+2 31 29
+
+query IIIIII
+SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-12-31 18:20:59-08:45'),
+ EXTRACT(MONTH FROM TIMESTAMP '2023-12-31 18:20:59-08:45'),
+ EXTRACT(DAY FROM TIMESTAMP '2023-12-31 18:20:59-08:45'),
+ EXTRACT(HOUR FROM TIMESTAMP '2023-12-31 18:20:59-08:45'),
+ EXTRACT(MINUTE FROM TIMESTAMP '2023-12-31 18:20:59-08:45'),
+ EXTRACT(SECOND FROM TIMESTAMP '2023-12-31 18:20:59-08:45');
+----
+2024 1 1 3 5 59
+
+query IIIIII
+SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-01-01 03:05:59+08:45'),
+ EXTRACT(MONTH FROM TIMESTAMP '2024-01-01 03:05:59+08:45'),
+ EXTRACT(DAY FROM TIMESTAMP '2024-01-01 03:05:59+08:45'),
+ EXTRACT(HOUR FROM TIMESTAMP '2024-01-01 03:05:59+08:45'),
+ EXTRACT(MINUTE FROM TIMESTAMP '2024-01-01 03:05:59+08:45'),
+ EXTRACT(SECOND FROM TIMESTAMP '2024-01-01 03:05:59+08:45');
+----
+2023 12 31 18 20 59
+
Review Comment:
tested with postive and negative offset timezones such as `Asia/Kolkata` and `America/New_York`
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2573062895
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -193,12 +201,83 @@ impl ScalarUDFImpl for DatePartFunc {
ColumnarValue::Scalar(scalar) => scalar.to_array()?,
};
+ let (is_timezone_aware, tz_str_opt) = match array.data_type() {
+ Timestamp(_, Some(tz_str)) => (true, Some(Arc::clone(tz_str))),
+ _ => (false, None),
+ };
+
let part_trim = part_normalization(&part);
+ let is_epoch = is_epoch(&part);
+
+ // Epoch is timezone-independent - it always returns seconds since 1970-01-01 UTC
+ let array = if is_epoch {
+ array
+ } else if is_timezone_aware {
+ // For timezone-aware timestamps, extract in their own timezone
+ match tz_str_opt.as_ref() {
+ Some(tz_str) => {
+ let tz = match tz_str.parse::<Tz>() {
+ Ok(tz) => tz,
+ Err(_) => return exec_err!("Invalid timezone"),
Review Comment:
Or
```
let tz: Tz = tz.parse::<Tz>().map_err(|e| exec_datafusion_err!("Invalid timezone {tz_str}: {e:?}"))?;
```
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3729298274
@codetyri0n I see there is conflicts on this PR now - would you be able to resolve those?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3880159036
@codetyri0n are you still willing to continue working on this to help push it across the line? If not I think I'll submit another PR based on the work done for [this comment](https://github.com/apache/datafusion/pull/18990#discussion_r2750083691).
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2750083691
##########
datafusion/functions/src/datetime/common.rs:
##########
@@ -23,22 +23,89 @@ use arrow::array::{
StringArrayType, StringViewArray,
};
use arrow::compute::DecimalCast;
-use arrow::compute::kernels::cast_utils::string_to_datetime;
-use arrow::datatypes::{DataType, TimeUnit};
+use arrow::compute::kernels::cast_utils::{
+ string_to_datetime, string_to_timestamp_nanos,
+};
+use arrow::datatypes::{ArrowTimestampType, DataType, TimeUnit};
use arrow_buffer::ArrowNativeType;
use chrono::LocalResult::Single;
use chrono::format::{Parsed, StrftimeItems, parse};
-use chrono::{DateTime, TimeZone, Utc};
+use chrono::{DateTime, MappedLocalTime, TimeDelta, TimeZone, Utc};
use datafusion_common::cast::as_generic_string_array;
use datafusion_common::{
DataFusionError, Result, ScalarValue, exec_datafusion_err, exec_err,
internal_datafusion_err, unwrap_or_internal_err,
};
use datafusion_expr::ColumnarValue;
+use std::ops::Add;
/// Error message if nanosecond conversion request beyond supported interval
const ERR_NANOSECONDS_NOT_SUPPORTED: &str = "The dates that can be represented as nanoseconds have to be between 1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.854775804";
+pub fn adjust_to_local_time<T: ArrowTimestampType>(ts: i64, tz: Tz) -> Result<i64> {
+ fn convert_timestamp<F>(ts: i64, converter: F) -> Result<DateTime<Utc>>
+ where
+ F: Fn(i64) -> MappedLocalTime<DateTime<Utc>>,
+ {
+ match converter(ts) {
+ MappedLocalTime::Ambiguous(earliest, latest) => exec_err!(
+ "Ambiguous timestamp. Do you mean {:?} or {:?}",
+ earliest,
+ latest
+ ),
+ MappedLocalTime::None => exec_err!(
+ "The local time does not exist because there is a gap in the local time."
+ ),
+ Single(date_time) => Ok(date_time),
+ }
+ }
+
+ let date_time = match T::UNIT {
+ TimeUnit::Nanosecond => Utc.timestamp_nanos(ts),
+ TimeUnit::Microsecond => convert_timestamp(ts, |ts| Utc.timestamp_micros(ts))?,
+ TimeUnit::Millisecond => convert_timestamp(ts, |ts| Utc.timestamp_millis_opt(ts))?,
+ TimeUnit::Second => convert_timestamp(ts, |ts| Utc.timestamp_opt(ts, 0))?,
+ };
+
+ // Get the timezone offset for this datetime
+ let tz_offset = tz.offset_from_utc_datetime(&date_time.naive_utc());
+ // Convert offset to seconds - offset is formatted like "+01:00" or "-05:00"
+ let offset_str = format!("{tz_offset}");
Review Comment:
Submitted a PR to your branch @codetyri0n
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3644750828
@alamb @comphead - would either of you have the time to take a look at this PR and merge it if you think it's acceptable?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#issuecomment-3660776812
I'll try and review this in the next day or two.
@codetyri0n there appears to be some conflicts with this PR -- could you please resolve them?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Timezone aware extract SQL expression [datafusion]
Posted by "codetyri0n (via GitHub)" <gi...@apache.org>.
codetyri0n commented on code in PR #18990:
URL: https://github.com/apache/datafusion/pull/18990#discussion_r2583845247
##########
datafusion/functions/src/datetime/date_part.rs:
##########
@@ -240,23 +314,43 @@ impl ScalarUDFImpl for DatePartFunc {
}
}
+fn adjust_timestamp_array<T: ArrowTimestampType>(
+ array: &ArrayRef,
+ tz: Tz,
+) -> Result<ArrayRef> {
+ let mut builder = PrimitiveBuilder::<T>::new();
+ let primitive_array = as_primitive_array::<T>(array)?;
+ for ts_opt in primitive_array.iter() {
+ match ts_opt {
+ None => builder.append_null(),
+ Some(ts) => {
+ let adjusted_ts = adjust_to_local_time::<T>(ts, tz)?;
+ builder.append_value(adjusted_ts);
+ }
+ }
+ }
+ Ok(Arc::new(builder.finish()))
+}
+
fn is_epoch(part: &str) -> bool {
let part = part_normalization(part);
matches!(part.to_lowercase().as_str(), "epoch")
}
-// Try to remove quote if exist, if the quote is invalid, return original string and let the downstream function handle the error
Review Comment:
I had initially planned to give a better description for it but missed it, will revert back to how it was originally
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org