This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new f284e3bb73 feat: add UDF to_local_time() (#11347)
f284e3bb73 is described below
commit f284e3bb73e089abc0c06b3314014522411bf1da
Author: Chunchun Ye <[email protected]>
AuthorDate: Thu Jul 11 11:17:09 2024 -0500
feat: add UDF to_local_time() (#11347)
* feat: add UDF `to_local_time()`
* chore: support column value in array
* chore: lint
* chore: fix conversion for us, ms, and s
* chore: add more tests for daylight savings time
* chore: add function description
* refactor: update tests and add examples in description
* chore: add description and example
* chore: doc
chore: doc
chore: doc
chore: doc
chore: doc
* chore: stop copying
* chore: fix typo
* chore: mention that the offset varies based on daylight savings time
* refactor: parse timezone once and update examples in description
* refactor: replace map..concat with flat_map
* chore: add hard code timestamp value in test
chore: doc
chore: doc
* chore: handle errors and remove panics
* chore: move some test to slt
* chore: clone time_value
* chore: typo
---------
Co-authored-by: Andrew Lamb <[email protected]>
---
datafusion/functions/src/datetime/mod.rs | 11 +-
datafusion/functions/src/datetime/to_local_time.rs | 564 +++++++++++++++++++++
datafusion/sqllogictest/test_files/timestamps.slt | 177 +++++++
3 files changed, 751 insertions(+), 1 deletion(-)
diff --git a/datafusion/functions/src/datetime/mod.rs
b/datafusion/functions/src/datetime/mod.rs
index 9c2f80856b..a7e9827d6c 100644
--- a/datafusion/functions/src/datetime/mod.rs
+++ b/datafusion/functions/src/datetime/mod.rs
@@ -32,6 +32,7 @@ pub mod make_date;
pub mod now;
pub mod to_char;
pub mod to_date;
+pub mod to_local_time;
pub mod to_timestamp;
pub mod to_unixtime;
@@ -50,6 +51,7 @@ make_udf_function!(
make_udf_function!(now::NowFunc, NOW, now);
make_udf_function!(to_char::ToCharFunc, TO_CHAR, to_char);
make_udf_function!(to_date::ToDateFunc, TO_DATE, to_date);
+make_udf_function!(to_local_time::ToLocalTimeFunc, TO_LOCAL_TIME,
to_local_time);
make_udf_function!(to_unixtime::ToUnixtimeFunc, TO_UNIXTIME, to_unixtime);
make_udf_function!(to_timestamp::ToTimestampFunc, TO_TIMESTAMP, to_timestamp);
make_udf_function!(
@@ -108,7 +110,13 @@ pub mod expr_fn {
),(
now,
"returns the current timestamp in nanoseconds, using the same value
for all instances of now() in same statement",
- ),(
+ ),
+ (
+ to_local_time,
+ "converts a timezone-aware timestamp to local time (with no offset or
timezone information), i.e. strips off the timezone from the timestamp",
+ args,
+ ),
+ (
to_unixtime,
"converts a string and optional formats to a Unixtime",
args,
@@ -277,6 +285,7 @@ pub fn functions() -> Vec<Arc<ScalarUDF>> {
now(),
to_char(),
to_date(),
+ to_local_time(),
to_unixtime(),
to_timestamp(),
to_timestamp_seconds(),
diff --git a/datafusion/functions/src/datetime/to_local_time.rs
b/datafusion/functions/src/datetime/to_local_time.rs
new file mode 100644
index 0000000000..c84d1015bd
--- /dev/null
+++ b/datafusion/functions/src/datetime/to_local_time.rs
@@ -0,0 +1,564 @@
+// 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.
+
+use std::any::Any;
+use std::ops::Add;
+use std::sync::Arc;
+
+use arrow::array::timezone::Tz;
+use arrow::array::{Array, ArrayRef, PrimitiveBuilder};
+use arrow::datatypes::DataType::Timestamp;
+use arrow::datatypes::{
+ ArrowTimestampType, DataType, TimestampMicrosecondType,
TimestampMillisecondType,
+ TimestampNanosecondType, TimestampSecondType,
+};
+use arrow::datatypes::{
+ TimeUnit,
+ TimeUnit::{Microsecond, Millisecond, Nanosecond, Second},
+};
+
+use chrono::{DateTime, MappedLocalTime, Offset, TimeDelta, TimeZone, Utc};
+use datafusion_common::cast::as_primitive_array;
+use datafusion_common::{exec_err, DataFusionError, Result, ScalarValue};
+use datafusion_expr::TypeSignature::Exact;
+use datafusion_expr::{
+ ColumnarValue, ScalarUDFImpl, Signature, Volatility, TIMEZONE_WILDCARD,
+};
+
+/// A UDF function that converts a timezone-aware timestamp to local time
(with no offset or
+/// timezone information). In other words, this function strips off the
timezone from the timestamp,
+/// while keep the display value of the timestamp the same.
+#[derive(Debug)]
+pub struct ToLocalTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToLocalTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToLocalTimeFunc {
+ pub fn new() -> Self {
+ let base_sig = |array_type: TimeUnit| {
+ [
+ Exact(vec![Timestamp(array_type, None)]),
+ Exact(vec![Timestamp(array_type,
Some(TIMEZONE_WILDCARD.into()))]),
+ ]
+ };
+
+ let full_sig = [Nanosecond, Microsecond, Millisecond, Second]
+ .into_iter()
+ .flat_map(base_sig)
+ .collect::<Vec<_>>();
+
+ Self {
+ signature: Signature::one_of(full_sig, Volatility::Immutable),
+ }
+ }
+
+ fn to_local_time(&self, args: &[ColumnarValue]) -> Result<ColumnarValue> {
+ if args.len() != 1 {
+ return exec_err!(
+ "to_local_time function requires 1 argument, got {}",
+ args.len()
+ );
+ }
+
+ let time_value = &args[0];
+ let arg_type = time_value.data_type();
+ match arg_type {
+ DataType::Timestamp(_, None) => {
+ // if no timezone specificed, just return the input
+ Ok(time_value.clone())
+ }
+ // If has timezone, adjust the underlying time value. The current
time value
+ // is stored as i64 in UTC, even though the timezone may not be in
UTC. Therefore,
+ // we need to adjust the time value to the local time. See
[`adjust_to_local_time`]
+ // for more details.
+ //
+ // Then remove the timezone in return type, i.e. return None
+ DataType::Timestamp(_, Some(timezone)) => {
+ let tz: Tz = timezone.parse()?;
+
+ match time_value {
+ ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(
+ Some(ts),
+ Some(_),
+ )) => {
+ let adjusted_ts =
+
adjust_to_local_time::<TimestampNanosecondType>(*ts, tz)?;
+
Ok(ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(
+ Some(adjusted_ts),
+ None,
+ )))
+ }
+ ColumnarValue::Scalar(ScalarValue::TimestampMicrosecond(
+ Some(ts),
+ Some(_),
+ )) => {
+ let adjusted_ts =
+
adjust_to_local_time::<TimestampMicrosecondType>(*ts, tz)?;
+
Ok(ColumnarValue::Scalar(ScalarValue::TimestampMicrosecond(
+ Some(adjusted_ts),
+ None,
+ )))
+ }
+ ColumnarValue::Scalar(ScalarValue::TimestampMillisecond(
+ Some(ts),
+ Some(_),
+ )) => {
+ let adjusted_ts =
+
adjust_to_local_time::<TimestampMillisecondType>(*ts, tz)?;
+
Ok(ColumnarValue::Scalar(ScalarValue::TimestampMillisecond(
+ Some(adjusted_ts),
+ None,
+ )))
+ }
+ ColumnarValue::Scalar(ScalarValue::TimestampSecond(
+ Some(ts),
+ Some(_),
+ )) => {
+ let adjusted_ts =
+ adjust_to_local_time::<TimestampSecondType>(*ts,
tz)?;
+ Ok(ColumnarValue::Scalar(ScalarValue::TimestampSecond(
+ Some(adjusted_ts),
+ None,
+ )))
+ }
+ ColumnarValue::Array(array) => {
+ fn transform_array<T: ArrowTimestampType>(
+ array: &ArrayRef,
+ tz: Tz,
+ ) -> Result<ColumnarValue> {
+ 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: i64 =
+ adjust_to_local_time::<T>(ts, tz)?;
+ builder.append_value(adjusted_ts)
+ }
+ }
+ }
+
+
Ok(ColumnarValue::Array(Arc::new(builder.finish())))
+ }
+
+ match array.data_type() {
+ Timestamp(_, None) => {
+ // if no timezone specificed, just return the
input
+ Ok(time_value.clone())
+ }
+ Timestamp(Nanosecond, Some(_)) => {
+
transform_array::<TimestampNanosecondType>(array, tz)
+ }
+ Timestamp(Microsecond, Some(_)) => {
+
transform_array::<TimestampMicrosecondType>(array, tz)
+ }
+ Timestamp(Millisecond, Some(_)) => {
+
transform_array::<TimestampMillisecondType>(array, tz)
+ }
+ Timestamp(Second, Some(_)) => {
+ transform_array::<TimestampSecondType>(array,
tz)
+ }
+ _ => {
+ exec_err!("to_local_time function requires
timestamp argument in array, got {:?}", array.data_type())
+ }
+ }
+ }
+ _ => {
+ exec_err!(
+ "to_local_time function requires timestamp argument,
got {:?}",
+ time_value.data_type()
+ )
+ }
+ }
+ }
+ _ => {
+ exec_err!(
+ "to_local_time function requires timestamp argument, got
{:?}",
+ arg_type
+ )
+ }
+ }
+ }
+}
+
+/// This function converts a timestamp with a timezone to a timestamp without
a timezone.
+/// The display value of the adjusted timestamp remain the same, but the
underlying timestamp
+/// representation is adjusted according to the relative timezone offset to
UTC.
+///
+/// This function uses chrono to handle daylight saving time changes.
+///
+/// For example,
+///
+/// ```text
+/// '2019-03-31T01:00:00Z'::timestamp at time zone 'Europe/Brussels'
+/// ```
+///
+/// is displayed as follows in datafusion-cli:
+///
+/// ```text
+/// 2019-03-31T01:00:00+01:00
+/// ```
+///
+/// and is represented in DataFusion as:
+///
+/// ```text
+/// TimestampNanosecond(Some(1_553_990_400_000_000_000),
Some("Europe/Brussels"))
+/// ```
+///
+/// To strip off the timezone while keeping the display value the same, we
need to
+/// adjust the underlying timestamp with the timezone offset value using
`adjust_to_local_time()`
+///
+/// ```text
+/// adjust_to_local_time(1_553_990_400_000_000_000, "Europe/Brussels") -->
1_553_994_000_000_000_000
+/// ```
+///
+/// The difference between `1_553_990_400_000_000_000` and
`1_553_994_000_000_000_000` is
+/// `3600_000_000_000` ns, which corresponds to 1 hour. This matches with the
timezone
+/// offset for "Europe/Brussels" for this date.
+///
+/// Note that the offset varies with daylight savings time (DST), which makes
this tricky! For
+/// example, timezone "Europe/Brussels" has a 2-hour offset during DST and a
1-hour offset
+/// when DST ends.
+///
+/// Consequently, DataFusion can represent the timestamp in local time (with
no offset or
+/// timezone information) as
+///
+/// ```text
+/// TimestampNanosecond(Some(1_553_994_000_000_000_000), None)
+/// ```
+///
+/// which is displayed as follows in datafusion-cli:
+///
+/// ```text
+/// 2019-03-31T01:00:00
+/// ```
+///
+/// See `test_adjust_to_local_time()` for example
+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."
+ ),
+ MappedLocalTime::Single(date_time) => Ok(date_time),
+ }
+ }
+
+ let date_time = match T::UNIT {
+ Nanosecond => Utc.timestamp_nanos(ts),
+ Microsecond => convert_timestamp(ts, |ts| Utc.timestamp_micros(ts))?,
+ Millisecond => convert_timestamp(ts, |ts|
Utc.timestamp_millis_opt(ts))?,
+ Second => convert_timestamp(ts, |ts| Utc.timestamp_opt(ts, 0))?,
+ };
+
+ let offset_seconds: i64 = tz
+ .offset_from_utc_datetime(&date_time.naive_utc())
+ .fix()
+ .local_minus_utc() as i64;
+
+ let adjusted_date_time = date_time.add(
+ // This should not fail under normal circumstances as the
+ // maximum possible offset is 26 hours (93,600 seconds)
+ TimeDelta::try_seconds(offset_seconds)
+ .ok_or(DataFusionError::Internal("Offset seconds should be less
than i64::MAX / 1_000 or greater than -i64::MAX / 1_000".to_string()))?,
+ );
+
+ // convert the naive datetime back to i64
+ match T::UNIT {
+ Nanosecond => adjusted_date_time.timestamp_nanos_opt().ok_or(
+ DataFusionError::Internal(
+ "Failed to convert DateTime to timestamp in nanosecond. This
error may occur if the date is out of range. The supported date ranges are
between 1677-09-21T00:12:43.145224192 and
2262-04-11T23:47:16.854775807".to_string(),
+ ),
+ ),
+ Microsecond => Ok(adjusted_date_time.timestamp_micros()),
+ Millisecond => Ok(adjusted_date_time.timestamp_millis()),
+ Second => Ok(adjusted_date_time.timestamp()),
+ }
+}
+
+impl ScalarUDFImpl for ToLocalTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_local_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, arg_types: &[DataType]) -> Result<DataType> {
+ if arg_types.len() != 1 {
+ return exec_err!(
+ "to_local_time function requires 1 argument, got {:?}",
+ arg_types.len()
+ );
+ }
+
+ match &arg_types[0] {
+ Timestamp(Nanosecond, _) => Ok(Timestamp(Nanosecond, None)),
+ Timestamp(Microsecond, _) => Ok(Timestamp(Microsecond, None)),
+ Timestamp(Millisecond, _) => Ok(Timestamp(Millisecond, None)),
+ Timestamp(Second, _) => Ok(Timestamp(Second, None)),
+ _ => exec_err!(
+ "The to_local_time function can only accept timestamp as the
arg, got {:?}", arg_types[0]
+ ),
+ }
+ }
+
+ fn invoke(&self, args: &[ColumnarValue]) -> Result<ColumnarValue> {
+ if args.len() != 1 {
+ return exec_err!(
+ "to_local_time function requires 1 argument, got {:?}",
+ args.len()
+ );
+ }
+
+ self.to_local_time(args)
+ }
+}
+
+#[cfg(test)]
+mod tests {
+ use std::sync::Arc;
+
+ use arrow::array::{types::TimestampNanosecondType,
TimestampNanosecondArray};
+ use arrow::compute::kernels::cast_utils::string_to_timestamp_nanos;
+ use arrow::datatypes::{DataType, TimeUnit};
+ use chrono::NaiveDateTime;
+ use datafusion_common::ScalarValue;
+ use datafusion_expr::{ColumnarValue, ScalarUDFImpl};
+
+ use super::{adjust_to_local_time, ToLocalTimeFunc};
+
+ #[test]
+ fn test_adjust_to_local_time() {
+ let timestamp_str = "2020-03-31T13:40:00";
+ let tz: arrow::array::timezone::Tz =
+ "America/New_York".parse().expect("Invalid timezone");
+
+ let timestamp = timestamp_str
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .and_local_timezone(tz) // this is in a local timezone
+ .unwrap()
+ .timestamp_nanos_opt()
+ .unwrap();
+
+ let expected_timestamp = timestamp_str
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .and_utc() // this is in UTC
+ .timestamp_nanos_opt()
+ .unwrap();
+
+ let res = adjust_to_local_time::<TimestampNanosecondType>(timestamp,
tz).unwrap();
+ assert_eq!(res, expected_timestamp);
+ }
+
+ #[test]
+ fn test_to_local_time_scalar() {
+ let timezone = Some("Europe/Brussels".into());
+ let timestamps_with_timezone = vec![
+ (
+ ScalarValue::TimestampNanosecond(
+ Some(1_123_123_000_000_000_000),
+ timezone.clone(),
+ ),
+
ScalarValue::TimestampNanosecond(Some(1_123_130_200_000_000_000), None),
+ ),
+ (
+ ScalarValue::TimestampMicrosecond(
+ Some(1_123_123_000_000_000),
+ timezone.clone(),
+ ),
+ ScalarValue::TimestampMicrosecond(Some(1_123_130_200_000_000),
None),
+ ),
+ (
+ ScalarValue::TimestampMillisecond(
+ Some(1_123_123_000_000),
+ timezone.clone(),
+ ),
+ ScalarValue::TimestampMillisecond(Some(1_123_130_200_000),
None),
+ ),
+ (
+ ScalarValue::TimestampSecond(Some(1_123_123_000), timezone),
+ ScalarValue::TimestampSecond(Some(1_123_130_200), None),
+ ),
+ ];
+
+ for (input, expected) in timestamps_with_timezone {
+ test_to_local_time_helper(input, expected);
+ }
+ }
+
+ #[test]
+ fn test_timezone_with_daylight_savings() {
+ let timezone_str = "America/New_York";
+ let tz: arrow::array::timezone::Tz =
+ timezone_str.parse().expect("Invalid timezone");
+
+ // Test data:
+ // (
+ // the string display of the input timestamp,
+ // the i64 representation of the timestamp before adjustment in
nanosecond,
+ // the i64 representation of the timestamp after adjustment in
nanosecond,
+ // )
+ let test_cases = vec![
+ (
+ // DST time
+ "2020-03-31T13:40:00",
+ 1_585_676_400_000_000_000,
+ 1_585_662_000_000_000_000,
+ ),
+ (
+ // End of DST
+ "2020-11-04T14:06:40",
+ 1_604_516_800_000_000_000,
+ 1_604_498_800_000_000_000,
+ ),
+ ];
+
+ for (
+ input_timestamp_str,
+ expected_input_timestamp,
+ expected_adjusted_timestamp,
+ ) in test_cases
+ {
+ let input_timestamp = input_timestamp_str
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .and_local_timezone(tz) // this is in a local timezone
+ .unwrap()
+ .timestamp_nanos_opt()
+ .unwrap();
+ assert_eq!(input_timestamp, expected_input_timestamp);
+
+ let expected_timestamp = input_timestamp_str
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .and_utc() // this is in UTC
+ .timestamp_nanos_opt()
+ .unwrap();
+ assert_eq!(expected_timestamp, expected_adjusted_timestamp);
+
+ let input = ScalarValue::TimestampNanosecond(
+ Some(input_timestamp),
+ Some(timezone_str.into()),
+ );
+ let expected =
+ ScalarValue::TimestampNanosecond(Some(expected_timestamp),
None);
+ test_to_local_time_helper(input, expected)
+ }
+ }
+
+ fn test_to_local_time_helper(input: ScalarValue, expected: ScalarValue) {
+ let res = ToLocalTimeFunc::new()
+ .invoke(&[ColumnarValue::Scalar(input)])
+ .unwrap();
+ match res {
+ ColumnarValue::Scalar(res) => {
+ assert_eq!(res, expected);
+ }
+ _ => panic!("unexpected return type"),
+ }
+ }
+
+ #[test]
+ fn test_to_local_time_timezones_array() {
+ let cases = [
+ (
+ vec![
+ "2020-09-08T00:00:00",
+ "2020-09-08T01:00:00",
+ "2020-09-08T02:00:00",
+ "2020-09-08T03:00:00",
+ "2020-09-08T04:00:00",
+ ],
+ None::<Arc<str>>,
+ vec![
+ "2020-09-08T00:00:00",
+ "2020-09-08T01:00:00",
+ "2020-09-08T02:00:00",
+ "2020-09-08T03:00:00",
+ "2020-09-08T04:00:00",
+ ],
+ ),
+ (
+ vec![
+ "2020-09-08T00:00:00",
+ "2020-09-08T01:00:00",
+ "2020-09-08T02:00:00",
+ "2020-09-08T03:00:00",
+ "2020-09-08T04:00:00",
+ ],
+ Some("+01:00".into()),
+ vec![
+ "2020-09-08T00:00:00",
+ "2020-09-08T01:00:00",
+ "2020-09-08T02:00:00",
+ "2020-09-08T03:00:00",
+ "2020-09-08T04:00:00",
+ ],
+ ),
+ ];
+
+ cases.iter().for_each(|(source, _tz_opt, expected)| {
+ let input = source
+ .iter()
+ .map(|s| Some(string_to_timestamp_nanos(s).unwrap()))
+ .collect::<TimestampNanosecondArray>();
+ let right = expected
+ .iter()
+ .map(|s| Some(string_to_timestamp_nanos(s).unwrap()))
+ .collect::<TimestampNanosecondArray>();
+ let result = ToLocalTimeFunc::new()
+ .invoke(&[ColumnarValue::Array(Arc::new(input))])
+ .unwrap();
+ if let ColumnarValue::Array(result) = result {
+ assert_eq!(
+ result.data_type(),
+ &DataType::Timestamp(TimeUnit::Nanosecond, None)
+ );
+ let left = arrow::array::cast::as_primitive_array::<
+ TimestampNanosecondType,
+ >(&result);
+ assert_eq!(left, &right);
+ } else {
+ panic!("unexpected column type");
+ }
+ });
+ }
+}
diff --git a/datafusion/sqllogictest/test_files/timestamps.slt
b/datafusion/sqllogictest/test_files/timestamps.slt
index 2216dbfa5f..f4e492649b 100644
--- a/datafusion/sqllogictest/test_files/timestamps.slt
+++ b/datafusion/sqllogictest/test_files/timestamps.slt
@@ -2844,3 +2844,180 @@ select arrow_cast('2024-06-17T13:00:00',
'Timestamp(Nanosecond, Some("UTC"))') -
query error
select arrow_cast('2024-06-17T13:00:00', 'Timestamp(Nanosecond,
Some("+00:00"))') - arrow_cast('2024-06-17T12:00:00', 'Timestamp(Microsecond,
Some("+01:00"))');
+
+##########
+## Test to_local_time function
+##########
+
+# invalid number of arguments -- no argument
+statement error
+select to_local_time();
+
+# invalid number of arguments -- more than 1 argument
+statement error
+select to_local_time('2024-04-01T00:00:20Z'::timestamp, 'some string');
+
+# invalid argument data type
+statement error DataFusion error: Execution error: The to_local_time function
can only accept timestamp as the arg, got Utf8
+select to_local_time('2024-04-01T00:00:20Z');
+
+# invalid timezone
+statement error DataFusion error: Arrow error: Parser error: Invalid timezone
"Europe/timezone": failed to parse timezone
+select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE
'Europe/timezone');
+
+# valid query
+query P
+select to_local_time('2024-04-01T00:00:20Z'::timestamp);
+----
+2024-04-01T00:00:20
+
+query P
+select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE '+05:00');
+----
+2024-04-01T00:00:20
+
+query P
+select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE
'Europe/Brussels');
+----
+2024-04-01T00:00:20
+
+query PTPT
+select
+ time,
+ arrow_typeof(time) as type,
+ to_local_time(time) as to_local_time,
+ arrow_typeof(to_local_time(time)) as to_local_time_type
+from (
+ select '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' as
time
+);
+----
+2024-04-01T00:00:20+02:00 Timestamp(Nanosecond, Some("Europe/Brussels"))
2024-04-01T00:00:20 Timestamp(Nanosecond, None)
+
+# use to_local_time() in date_bin()
+query P
+select date_bin(interval '1 day',
to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE
'Europe/Brussels'));
+----
+2024-04-01T00:00:00
+
+query P
+select date_bin(interval '1 day',
to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE
'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels';
+----
+2024-04-01T00:00:00+02:00
+
+# test using to_local_time() on array values
+statement ok
+create table t AS
+VALUES
+ ('2024-01-01T00:00:01Z'),
+ ('2024-02-01T00:00:01Z'),
+ ('2024-03-01T00:00:01Z'),
+ ('2024-04-01T00:00:01Z'),
+ ('2024-05-01T00:00:01Z'),
+ ('2024-06-01T00:00:01Z'),
+ ('2024-07-01T00:00:01Z'),
+ ('2024-08-01T00:00:01Z'),
+ ('2024-09-01T00:00:01Z'),
+ ('2024-10-01T00:00:01Z'),
+ ('2024-11-01T00:00:01Z'),
+ ('2024-12-01T00:00:01Z')
+;
+
+statement ok
+create view t_utc as
+select column1::timestamp AT TIME ZONE 'UTC' as "column1"
+from t;
+
+statement ok
+create view t_timezone as
+select column1::timestamp AT TIME ZONE 'Europe/Brussels' as "column1"
+from t;
+
+query PPT
+select column1, to_local_time(column1::timestamp),
arrow_typeof(to_local_time(column1::timestamp)) from t_utc;
+----
+2024-01-01T00:00:01Z 2024-01-01T00:00:01 Timestamp(Nanosecond, None)
+2024-02-01T00:00:01Z 2024-02-01T00:00:01 Timestamp(Nanosecond, None)
+2024-03-01T00:00:01Z 2024-03-01T00:00:01 Timestamp(Nanosecond, None)
+2024-04-01T00:00:01Z 2024-04-01T00:00:01 Timestamp(Nanosecond, None)
+2024-05-01T00:00:01Z 2024-05-01T00:00:01 Timestamp(Nanosecond, None)
+2024-06-01T00:00:01Z 2024-06-01T00:00:01 Timestamp(Nanosecond, None)
+2024-07-01T00:00:01Z 2024-07-01T00:00:01 Timestamp(Nanosecond, None)
+2024-08-01T00:00:01Z 2024-08-01T00:00:01 Timestamp(Nanosecond, None)
+2024-09-01T00:00:01Z 2024-09-01T00:00:01 Timestamp(Nanosecond, None)
+2024-10-01T00:00:01Z 2024-10-01T00:00:01 Timestamp(Nanosecond, None)
+2024-11-01T00:00:01Z 2024-11-01T00:00:01 Timestamp(Nanosecond, None)
+2024-12-01T00:00:01Z 2024-12-01T00:00:01 Timestamp(Nanosecond, None)
+
+query PPT
+select column1, to_local_time(column1), arrow_typeof(to_local_time(column1))
from t_utc;
+----
+2024-01-01T00:00:01Z 2024-01-01T00:00:01 Timestamp(Nanosecond, None)
+2024-02-01T00:00:01Z 2024-02-01T00:00:01 Timestamp(Nanosecond, None)
+2024-03-01T00:00:01Z 2024-03-01T00:00:01 Timestamp(Nanosecond, None)
+2024-04-01T00:00:01Z 2024-04-01T00:00:01 Timestamp(Nanosecond, None)
+2024-05-01T00:00:01Z 2024-05-01T00:00:01 Timestamp(Nanosecond, None)
+2024-06-01T00:00:01Z 2024-06-01T00:00:01 Timestamp(Nanosecond, None)
+2024-07-01T00:00:01Z 2024-07-01T00:00:01 Timestamp(Nanosecond, None)
+2024-08-01T00:00:01Z 2024-08-01T00:00:01 Timestamp(Nanosecond, None)
+2024-09-01T00:00:01Z 2024-09-01T00:00:01 Timestamp(Nanosecond, None)
+2024-10-01T00:00:01Z 2024-10-01T00:00:01 Timestamp(Nanosecond, None)
+2024-11-01T00:00:01Z 2024-11-01T00:00:01 Timestamp(Nanosecond, None)
+2024-12-01T00:00:01Z 2024-12-01T00:00:01 Timestamp(Nanosecond, None)
+
+query PPT
+select column1, to_local_time(column1), arrow_typeof(to_local_time(column1))
from t_timezone;
+----
+2024-01-01T00:00:01+01:00 2024-01-01T00:00:01 Timestamp(Nanosecond, None)
+2024-02-01T00:00:01+01:00 2024-02-01T00:00:01 Timestamp(Nanosecond, None)
+2024-03-01T00:00:01+01:00 2024-03-01T00:00:01 Timestamp(Nanosecond, None)
+2024-04-01T00:00:01+02:00 2024-04-01T00:00:01 Timestamp(Nanosecond, None)
+2024-05-01T00:00:01+02:00 2024-05-01T00:00:01 Timestamp(Nanosecond, None)
+2024-06-01T00:00:01+02:00 2024-06-01T00:00:01 Timestamp(Nanosecond, None)
+2024-07-01T00:00:01+02:00 2024-07-01T00:00:01 Timestamp(Nanosecond, None)
+2024-08-01T00:00:01+02:00 2024-08-01T00:00:01 Timestamp(Nanosecond, None)
+2024-09-01T00:00:01+02:00 2024-09-01T00:00:01 Timestamp(Nanosecond, None)
+2024-10-01T00:00:01+02:00 2024-10-01T00:00:01 Timestamp(Nanosecond, None)
+2024-11-01T00:00:01+01:00 2024-11-01T00:00:01 Timestamp(Nanosecond, None)
+2024-12-01T00:00:01+01:00 2024-12-01T00:00:01 Timestamp(Nanosecond, None)
+
+# combine to_local_time() with date_bin()
+query P
+select date_bin(interval '1 day', to_local_time(column1)) AT TIME ZONE
'Europe/Brussels' as date_bin from t_utc;
+----
+2024-01-01T00:00:00+01:00
+2024-02-01T00:00:00+01:00
+2024-03-01T00:00:00+01:00
+2024-04-01T00:00:00+02:00
+2024-05-01T00:00:00+02:00
+2024-06-01T00:00:00+02:00
+2024-07-01T00:00:00+02:00
+2024-08-01T00:00:00+02:00
+2024-09-01T00:00:00+02:00
+2024-10-01T00:00:00+02:00
+2024-11-01T00:00:00+01:00
+2024-12-01T00:00:00+01:00
+
+query P
+select date_bin(interval '1 day', to_local_time(column1)) AT TIME ZONE
'Europe/Brussels' as date_bin from t_timezone;
+----
+2024-01-01T00:00:00+01:00
+2024-02-01T00:00:00+01:00
+2024-03-01T00:00:00+01:00
+2024-04-01T00:00:00+02:00
+2024-05-01T00:00:00+02:00
+2024-06-01T00:00:00+02:00
+2024-07-01T00:00:00+02:00
+2024-08-01T00:00:00+02:00
+2024-09-01T00:00:00+02:00
+2024-10-01T00:00:00+02:00
+2024-11-01T00:00:00+01:00
+2024-12-01T00:00:00+01:00
+
+statement ok
+drop table t;
+
+statement ok
+drop view t_utc;
+
+statement ok
+drop view t_timezone;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
