You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jatinkumarsingh (via GitHub)" <gi...@apache.org> on 2025/12/01 19:15:28 UTC
[PR] Add time functions [datafusion]
jatinkumarsingh opened a new pull request, #19031:
URL: https://github.com/apache/datafusion/pull/19031
Add time scalar functions: make_time, to_time, and date_bin support for Time64
## Which issue does this PR close?
- Closes #19025
## Rationale for this change
The time and date scalar functions were missing support for Time datatypes, making it difficult to work with time values. Users couldn't easily create time fields from components, parse time strings directly, or bin time values. This PR adds the missing functionality to improve time handling in DataFusion.
## What changes are included in this PR?
- **make_time**: New function to create `Time64(Nanosecond)` from hour, minute, and second components
- **to_time**: New function to parse time strings directly to `Time64(Nanosecond)`, supporting both time-only strings (e.g., '14:30:45') and full timestamps (extracts time component)
- **date_bin**: Extended to support `Time64` datatype for time binning operations, allowing users to bin time values similar to timestamps
- Updated documentation in `scalar_functions.md` with examples for all new functions
## Are these changes tested?
Yes. All functions include comprehensive unit tests:
- `make_time`: Tests for scalar and array inputs, null handling, and various input types (Int32, Int64, UInt32, UInt64, Utf8)
- `to_time`: Tests for time-only strings, full timestamps, null handling, and formatted parsing
- `date_bin`: Updated existing tests to verify Time64 support works correctly
All 42 datetime tests pass successfully.
## Are there any user-facing changes?
Yes. This PR adds three new user-facing features:
1. `make_time(hour, minute, second)` - Creates a time value from components
2. `to_time(expression[, format_n])` - Converts strings to time values
3. `date_bin(interval, time_value, origin)` - Now supports Time64 inputs in addition to timestamps
--
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] Add time functions [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey closed pull request #19031: Add time functions
URL: https://github.com/apache/datafusion/pull/19031
--
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] Add time functions [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#discussion_r2580897696
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
+"#,
+ argument(
+ name = "expression",
+ description = "Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "format_n",
+ description = "Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct ToTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::variadic_any(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for ToTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ if args.is_empty() {
+ return exec_err!(
+ "to_time function requires 1 or more arguments, got {}",
+ args.len()
+ );
+ }
+
+ if args.len() > 1 {
+ validate_data_types(&args, "to_time")?;
+ }
+
+ match args[0].data_type() {
+ Null => Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None))),
+ Time64(_) => args[0].cast_to(&Time64(Nanosecond), None),
+ Utf8View | LargeUtf8 | Utf8 => to_time_impl(&args, "to_time"),
+ other => {
+ exec_err!("Unsupported data type {other} for function to_time")
+ }
+ }
+ }
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+fn to_time_impl(args: &[ColumnarValue], name: &str) -> Result<ColumnarValue> {
+ fn time_from_timestamp(nanos: i64) -> i64 {
+ let secs = nanos / 1_000_000_000;
+ let nsecs = (nanos % 1_000_000_000) as u32;
+ if let Some(dt) = DateTime::from_timestamp(secs, nsecs) {
+ let h = dt.hour() as i64;
+ let m = dt.minute() as i64;
+ let s = dt.second() as i64;
+ let ns = dt.nanosecond() as i64;
+ (h * 3600 + m * 60 + s) * 1_000_000_000 + ns
+ } else {
+ 0
+ }
+ }
+
+ fn parse_time_str(s: &str) -> Result<i64> {
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S%.f") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ string_to_timestamp_nanos_shim(s).map(time_from_timestamp)
+ }
+
+ match args.len() {
+ 1 => match &args[0] {
+ ColumnarValue::Array(a) => {
+ let nanos_array = match a.data_type() {
+ Utf8View => {
+ let array = a.as_string_view();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ LargeUtf8 => {
+ let array = a.as_string::<i64>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ Utf8 => {
+ let array = a.as_string::<i32>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ other => {
+ return exec_err!(
+ "Unsupported data type {other:?} for function {name}"
+ )
+ }
+ };
+ Ok(ColumnarValue::Array(Arc::new(nanos_array)))
+ }
+ ColumnarValue::Scalar(scalar) => match scalar {
+ ScalarValue::Utf8(Some(s)) | ScalarValue::LargeUtf8(Some(s)) => {
+ let nanos = parse_time_str(s)?;
+ Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(Some(
+ nanos,
+ ))))
+ }
+ ScalarValue::Utf8(None)
+ | ScalarValue::LargeUtf8(None)
+ | ScalarValue::Utf8View(None) => {
+ Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None)))
+ }
+ _ => exec_err!("Unsupported data type {scalar:?} for function {name}"),
+ },
+ },
+ n if n >= 2 => {
+ let mut formats = Vec::new();
+ for arg in &args[1..] {
+ match arg {
+ ColumnarValue::Scalar(ScalarValue::Utf8(Some(f)))
+ | ColumnarValue::Scalar(ScalarValue::LargeUtf8(Some(f))) => {
Review Comment:
Utf8View
--
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] Add time functions [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#issuecomment-3598636097
Thank you, please add .slt tests to verify all these work via sql.
--
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] Add time functions [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#discussion_r2580913617
##########
datafusion/functions/src/datetime/make_time.rs:
##########
@@ -0,0 +1,308 @@
+// 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::sync::Arc;
+
+use arrow::array::builder::PrimitiveBuilder;
+use arrow::array::cast::AsArray;
+use arrow::array::types::Int32Type;
+use arrow::array::PrimitiveArray;
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::{Int32, Int64, Time64, UInt32, UInt64, Utf8, Utf8View};
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{NaiveTime, Timelike};
+
+use datafusion_common::{exec_err, utils::take_function_args, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = "Make a time from hour/minute/second component parts.",
+ syntax_example = "make_time(hour, minute, second)",
+ sql_example = r#"```sql
+> select make_time(14, 30, 45);
++-----------------------------------+
+|| make_time(Int64(14),Int64(30),Int64(45)) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select make_time('14', '30', '45');
++---------------------------------------+
+|| make_time(Utf8("14"),Utf8("30"),Utf8("45")) |
++---------------------------------------+
+|| 14:30:45 |
++---------------------------------------+
+```
+"#,
+ argument(
+ name = "hour",
+ description = "Hour to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "minute",
+ description = "Minute to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "second",
+ description = "Second to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct MakeTimeFunc {
+ signature: Signature,
+}
+
+impl Default for MakeTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl MakeTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::uniform(
+ 3,
+ vec![Int32, Int64, UInt32, UInt64, Utf8, Utf8View],
+ Volatility::Immutable,
+ ),
+ }
+ }
+}
+
+impl ScalarUDFImpl for MakeTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "make_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ let len = args
+ .iter()
+ .fold(Option::<usize>::None, |acc, arg| match arg {
+ ColumnarValue::Scalar(_) => acc,
+ ColumnarValue::Array(a) => Some(a.len()),
+ });
+
+ let [hours, minutes, seconds] = take_function_args(self.name(), args)?;
+
+ if matches!(hours, ColumnarValue::Scalar(ScalarValue::Null))
+ || matches!(minutes, ColumnarValue::Scalar(ScalarValue::Null))
+ || matches!(seconds, ColumnarValue::Scalar(ScalarValue::Null))
+ {
+ return Ok(ColumnarValue::Scalar(ScalarValue::Null));
Review Comment:
```suggestion
return Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None)));
```
--
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] Add time functions [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#discussion_r2580902695
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
+"#,
+ argument(
+ name = "expression",
+ description = "Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "format_n",
+ description = "Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct ToTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::variadic_any(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for ToTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ if args.is_empty() {
+ return exec_err!(
+ "to_time function requires 1 or more arguments, got {}",
+ args.len()
+ );
+ }
+
+ if args.len() > 1 {
+ validate_data_types(&args, "to_time")?;
+ }
+
+ match args[0].data_type() {
+ Null => Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None))),
+ Time64(_) => args[0].cast_to(&Time64(Nanosecond), None),
+ Utf8View | LargeUtf8 | Utf8 => to_time_impl(&args, "to_time"),
+ other => {
+ exec_err!("Unsupported data type {other} for function to_time")
+ }
+ }
+ }
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+fn to_time_impl(args: &[ColumnarValue], name: &str) -> Result<ColumnarValue> {
+ fn time_from_timestamp(nanos: i64) -> i64 {
+ let secs = nanos / 1_000_000_000;
+ let nsecs = (nanos % 1_000_000_000) as u32;
+ if let Some(dt) = DateTime::from_timestamp(secs, nsecs) {
+ let h = dt.hour() as i64;
+ let m = dt.minute() as i64;
+ let s = dt.second() as i64;
+ let ns = dt.nanosecond() as i64;
+ (h * 3600 + m * 60 + s) * 1_000_000_000 + ns
+ } else {
+ 0
+ }
+ }
+
+ fn parse_time_str(s: &str) -> Result<i64> {
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S%.f") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ string_to_timestamp_nanos_shim(s).map(time_from_timestamp)
+ }
+
+ match args.len() {
+ 1 => match &args[0] {
+ ColumnarValue::Array(a) => {
+ let nanos_array = match a.data_type() {
+ Utf8View => {
+ let array = a.as_string_view();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ LargeUtf8 => {
+ let array = a.as_string::<i64>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ Utf8 => {
+ let array = a.as_string::<i32>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ other => {
+ return exec_err!(
+ "Unsupported data type {other:?} for function {name}"
+ )
+ }
+ };
+ Ok(ColumnarValue::Array(Arc::new(nanos_array)))
+ }
+ ColumnarValue::Scalar(scalar) => match scalar {
+ ScalarValue::Utf8(Some(s)) | ScalarValue::LargeUtf8(Some(s)) => {
Review Comment:
missing Utf8View here too
--
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] Add time functions [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#discussion_r2578923022
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
+"#,
+ argument(
+ name = "expression",
+ description = "Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "format_n",
+ description = "Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct ToTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::variadic_any(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for ToTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ if args.is_empty() {
+ return exec_err!(
+ "to_time function requires 1 or more arguments, got {}",
+ args.len()
+ );
+ }
+
+ if args.len() > 1 {
+ validate_data_types(&args, "to_time")?;
+ }
+
+ match args[0].data_type() {
+ Null => Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None))),
+ Time64(_) => args[0].cast_to(&Time64(Nanosecond), None),
+ Utf8View | LargeUtf8 | Utf8 => to_time_impl(&args, "to_time"),
+ other => {
+ exec_err!("Unsupported data type {other} for function to_time")
+ }
+ }
+ }
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+fn to_time_impl(args: &[ColumnarValue], name: &str) -> Result<ColumnarValue> {
+ fn time_from_timestamp(nanos: i64) -> i64 {
+ let secs = nanos / 1_000_000_000;
+ let nsecs = (nanos % 1_000_000_000) as u32;
Review Comment:
If the date is before epoch (Jan 1 1970) then `nanos` will be negative and the cast to u32 will lead to problems.
##########
docs/source/user-guide/crate-configuration.md:
##########
Review Comment:
This is not related to this PR.
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
Review Comment:
```suggestion
| to_time(Utf8("14:30:45")) |
```
Is there an empty column ?!
##########
datafusion/functions/src/datetime/mod.rs:
##########
@@ -86,10 +90,14 @@ pub mod expr_fn {
date_trunc,
"truncates the date to a specified level of precision",
part date
- ),(
+ ), (
Review Comment:
```suggestion
),(
```
##########
datafusion/functions/src/datetime/make_time.rs:
##########
@@ -0,0 +1,308 @@
+// 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::sync::Arc;
+
+use arrow::array::builder::PrimitiveBuilder;
+use arrow::array::cast::AsArray;
+use arrow::array::types::Int32Type;
+use arrow::array::PrimitiveArray;
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::{Int32, Int64, Time64, UInt32, UInt64, Utf8, Utf8View};
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{NaiveTime, Timelike};
+
+use datafusion_common::{exec_err, utils::take_function_args, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = "Make a time from hour/minute/second component parts.",
+ syntax_example = "make_time(hour, minute, second)",
+ sql_example = r#"```sql
+> select make_time(14, 30, 45);
++-----------------------------------+
+|| make_time(Int64(14),Int64(30),Int64(45)) |
Review Comment:
```suggestion
| make_time(Int64(14),Int64(30),Int64(45)) |
```
same below
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
Review Comment:
```suggestion
Additional examples can be found in the [date_time examples](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
```
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
+"#,
+ argument(
+ name = "expression",
+ description = "Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "format_n",
+ description = "Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct ToTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::variadic_any(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for ToTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ if args.is_empty() {
+ return exec_err!(
+ "to_time function requires 1 or more arguments, got {}",
+ args.len()
+ );
+ }
+
+ if args.len() > 1 {
+ validate_data_types(&args, "to_time")?;
+ }
+
+ match args[0].data_type() {
+ Null => Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None))),
+ Time64(_) => args[0].cast_to(&Time64(Nanosecond), None),
+ Utf8View | LargeUtf8 | Utf8 => to_time_impl(&args, "to_time"),
+ other => {
+ exec_err!("Unsupported data type {other} for function to_time")
+ }
+ }
+ }
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+fn to_time_impl(args: &[ColumnarValue], name: &str) -> Result<ColumnarValue> {
+ fn time_from_timestamp(nanos: i64) -> i64 {
+ let secs = nanos / 1_000_000_000;
+ let nsecs = (nanos % 1_000_000_000) as u32;
+ if let Some(dt) = DateTime::from_timestamp(secs, nsecs) {
+ let h = dt.hour() as i64;
+ let m = dt.minute() as i64;
+ let s = dt.second() as i64;
+ let ns = dt.nanosecond() as i64;
+ (h * 3600 + m * 60 + s) * 1_000_000_000 + ns
+ } else {
+ 0
+ }
+ }
+
+ fn parse_time_str(s: &str) -> Result<i64> {
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S%.f") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ string_to_timestamp_nanos_shim(s).map(time_from_timestamp)
+ }
+
+ match args.len() {
+ 1 => match &args[0] {
+ ColumnarValue::Array(a) => {
+ let nanos_array = match a.data_type() {
+ Utf8View => {
Review Comment:
The code below for Utf8, LargeUtf8 and Utf8View is almost identical.
Consider extracting a helper function
##########
datafusion/functions/src/datetime/make_time.rs:
##########
@@ -0,0 +1,308 @@
+// 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::sync::Arc;
+
+use arrow::array::builder::PrimitiveBuilder;
+use arrow::array::cast::AsArray;
+use arrow::array::types::Int32Type;
+use arrow::array::PrimitiveArray;
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::{Int32, Int64, Time64, UInt32, UInt64, Utf8, Utf8View};
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{NaiveTime, Timelike};
+
+use datafusion_common::{exec_err, utils::take_function_args, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = "Make a time from hour/minute/second component parts.",
+ syntax_example = "make_time(hour, minute, second)",
+ sql_example = r#"```sql
+> select make_time(14, 30, 45);
++-----------------------------------+
+|| make_time(Int64(14),Int64(30),Int64(45)) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select make_time('14', '30', '45');
++---------------------------------------+
+|| make_time(Utf8("14"),Utf8("30"),Utf8("45")) |
++---------------------------------------+
+|| 14:30:45 |
++---------------------------------------+
+```
+"#,
+ argument(
+ name = "hour",
+ description = "Hour to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "minute",
+ description = "Minute to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "second",
+ description = "Second to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct MakeTimeFunc {
+ signature: Signature,
+}
+
+impl Default for MakeTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl MakeTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::uniform(
+ 3,
+ vec![Int32, Int64, UInt32, UInt64, Utf8, Utf8View],
+ Volatility::Immutable,
+ ),
+ }
+ }
+}
+
+impl ScalarUDFImpl for MakeTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "make_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ let len = args
+ .iter()
+ .fold(Option::<usize>::None, |acc, arg| match arg {
+ ColumnarValue::Scalar(_) => acc,
+ ColumnarValue::Array(a) => Some(a.len()),
+ });
+
+ let [hours, minutes, seconds] = take_function_args(self.name(), args)?;
+
+ if matches!(hours, ColumnarValue::Scalar(ScalarValue::Null))
+ || matches!(minutes, ColumnarValue::Scalar(ScalarValue::Null))
+ || matches!(seconds, ColumnarValue::Scalar(ScalarValue::Null))
+ {
+ return Ok(ColumnarValue::Scalar(ScalarValue::Null));
+ }
+
+ let hours = hours.cast_to(&Int32, None)?;
+ let minutes = minutes.cast_to(&Int32, None)?;
+ let seconds = seconds.cast_to(&Int32, None)?;
+
+ let get_scalar = |col: &ColumnarValue| -> Result<i32> {
+ let ColumnarValue::Scalar(s) = col else {
+ return exec_err!("Expected scalar value");
+ };
+ let ScalarValue::Int32(Some(i)) = s else {
+ return exec_err!("Unable to parse time from null/empty value");
+ };
+ Ok(*i)
+ };
+
+ let value = if let Some(array_size) = len {
+ let to_array = |col: &ColumnarValue| -> PrimitiveArray<Int32Type> {
+ match col {
+ ColumnarValue::Array(a) => a.as_primitive::<Int32Type>().to_owned(),
+ _ => {
+ let v = get_scalar(col).unwrap();
Review Comment:
This usage of `.unwrap()` may lead to a panic.
You may remake `to_array` to be a function that returns a Result to be able to use '?' and propagate errors.
##########
datafusion/sqllogictest/test_files/timestamps.slt:
##########
@@ -2910,6 +2910,299 @@ query error DataFusion error: Arrow error: Cast error: Cannot cast string '' to
select make_date(2024, 1, '');
+##########
+## make_time tests
+##########
+
+query D
+select make_time(14, 30, 45);
+----
+14:30:45
+
+query D
+select make_time(0, 0, 0);
+----
+00:00:00
+
+query D
+select make_time(23, 59, 59);
+----
+23:59:59
+
+query D
+select make_time('14', '30', '45');
+----
+14:30:45
+
+query D
+select make_time(12 + 2, '30', '45');
+----
+14:30:45
+
+query D
+select make_time(14::bigint, 30::bigint, 45::bigint);
+----
+14:30:45
+
+query D
+select make_time(arrow_cast(14, 'Int64'), arrow_cast(30, 'Int64'), arrow_cast(45, 'Int64'));
+----
+14:30:45
+
+query D
+select make_time(arrow_cast(14, 'Int32'), arrow_cast(30, 'Int32'), arrow_cast(45, 'Int32'));
+----
+14:30:45
+
+statement ok
+create table table_times (hour int, minute int, second int) as values
+ (14, 30, 45),
+ (9, 15, 30);
+
+query D
+select make_time(t.hour, t.minute, t.second) from table_times t;
+----
+14:30:45
+09:15:30
+
+query D
+select make_time(12, t.minute, t.second) from table_times t;
+----
+12:30:45
+12:15:30
+
+query D
+select make_time(t.hour, 0, t.second) from table_times t;
+----
+14:00:45
+09:00:30
+
+query D
+select make_time(t.hour, t.minute, 0) from table_times t;
+----
+14:30:00
+09:15:00
+
+query D
+select make_time('12', t.minute, t.second) from table_times t;
+----
+12:30:45
+12:15:30
+
+query D
+select make_time(t.hour, '0', t.second) from table_times t;
+----
+14:00:45
+09:00:30
+
+query D
+select make_time(t.hour, t.minute, '0') from table_times t;
+----
+14:30:00
+09:15:00
+
+statement ok
+insert into table_times values (14, null, 45);
+
+query error DataFusion error: Execution error: Unable to parse time from 14, 0, 45
Review Comment:
Should this fail ?
Apache Spark returns NULL if any of the arguments is NULL - https://github.com/apache/spark/blob/7251e95e22cc9afd39bcae3ad0ef56b7843ac0fb/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala#L538-L539
--
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] Add time functions [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#issuecomment-3602282892
This PR looks like it was largely written by AI. https://datafusion.apache.org/contributor-guide/index.html#ai-assisted-contributions
If I am mistaken please split this PR into a PR for each function, supporting both Time32 and Time64.
--
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] Add time functions [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#discussion_r2580890302
##########
datafusion/functions/src/datetime/to_time.rs:
##########
@@ -0,0 +1,404 @@
+// 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::sync::Arc;
+
+use crate::datetime::common::*;
+use arrow::array::cast::AsArray;
+use arrow::array::{Array, PrimitiveArray};
+use arrow::datatypes::DataType;
+use arrow::datatypes::DataType::*;
+use arrow::datatypes::Time64NanosecondType;
+use arrow::datatypes::TimeUnit::Nanosecond;
+use chrono::{DateTime, NaiveTime, Timelike};
+use datafusion_common::{exec_err, Result, ScalarValue};
+use datafusion_expr::{
+ ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"
+Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00' or '05:44:00') if no [Chrono formats] are provided. If a full timestamp is provided, only the time component is extracted. Returns the corresponding time.
+"#,
+ syntax_example = "to_time(expression[, ..., format_n])",
+ sql_example = r#"```sql
+> select to_time('14:30:45');
++-------------------+
+|| to_time(Utf8("14:30:45")) |
++-------------------+
+|| 14:30:45 |
++-------------------+
+> select to_time('2023-01-31T14:30:45');
++-----------------------------------+
+|| to_time(Utf8("2023-01-31T14:30:45")) |
++-----------------------------------+
+|| 14:30:45 |
++-----------------------------------+
+> select to_time('14:30:45.123456789', '%H:%M:%S%.f');
++---------------------------------------------------+
+|| to_time(Utf8("14:30:45.123456789"),Utf8("%H:%M:%S%.f")) |
++---------------------------------------------------+
+|| 14:30:45.123456789 |
++---------------------------------------------------+
+```
+Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/builtin_functions/date_time.rs)
+"#,
+ argument(
+ name = "expression",
+ description = "Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators."
+ ),
+ argument(
+ name = "format_n",
+ description = "Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct ToTimeFunc {
+ signature: Signature,
+}
+
+impl Default for ToTimeFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl ToTimeFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::variadic_any(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for ToTimeFunc {
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ "to_time"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ Ok(Time64(Nanosecond))
+ }
+
+ fn invoke_with_args(
+ &self,
+ args: datafusion_expr::ScalarFunctionArgs,
+ ) -> Result<ColumnarValue> {
+ let args = args.args;
+ if args.is_empty() {
+ return exec_err!(
+ "to_time function requires 1 or more arguments, got {}",
+ args.len()
+ );
+ }
+
+ if args.len() > 1 {
+ validate_data_types(&args, "to_time")?;
+ }
+
+ match args[0].data_type() {
+ Null => Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None))),
+ Time64(_) => args[0].cast_to(&Time64(Nanosecond), None),
+ Utf8View | LargeUtf8 | Utf8 => to_time_impl(&args, "to_time"),
+ other => {
+ exec_err!("Unsupported data type {other} for function to_time")
+ }
+ }
+ }
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+fn to_time_impl(args: &[ColumnarValue], name: &str) -> Result<ColumnarValue> {
+ fn time_from_timestamp(nanos: i64) -> i64 {
+ let secs = nanos / 1_000_000_000;
+ let nsecs = (nanos % 1_000_000_000) as u32;
+ if let Some(dt) = DateTime::from_timestamp(secs, nsecs) {
+ let h = dt.hour() as i64;
+ let m = dt.minute() as i64;
+ let s = dt.second() as i64;
+ let ns = dt.nanosecond() as i64;
+ (h * 3600 + m * 60 + s) * 1_000_000_000 + ns
+ } else {
+ 0
+ }
+ }
+
+ fn parse_time_str(s: &str) -> Result<i64> {
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ if let Ok(t) = NaiveTime::parse_from_str(s, "%H:%M:%S%.f") {
+ let nanos =
+ (t.hour() as i64 * 3600 + t.minute() as i64 * 60 + t.second() as i64)
+ * 1_000_000_000
+ + t.nanosecond() as i64;
+ return Ok(nanos);
+ }
+ string_to_timestamp_nanos_shim(s).map(time_from_timestamp)
+ }
+
+ match args.len() {
+ 1 => match &args[0] {
+ ColumnarValue::Array(a) => {
+ let nanos_array = match a.data_type() {
+ Utf8View => {
+ let array = a.as_string_view();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ LargeUtf8 => {
+ let array = a.as_string::<i64>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ Utf8 => {
+ let array = a.as_string::<i32>();
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(array.len());
+ for i in 0..array.len() {
+ if array.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = array.value(i);
+ match parse_time_str(s) {
+ Ok(nanos) => builder.append_value(nanos),
+ Err(e) => return Err(e),
+ }
+ }
+ }
+ builder.finish()
+ }
+ other => {
+ return exec_err!(
+ "Unsupported data type {other:?} for function {name}"
+ )
+ }
+ };
+ Ok(ColumnarValue::Array(Arc::new(nanos_array)))
+ }
+ ColumnarValue::Scalar(scalar) => match scalar {
+ ScalarValue::Utf8(Some(s)) | ScalarValue::LargeUtf8(Some(s)) => {
+ let nanos = parse_time_str(s)?;
+ Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(Some(
+ nanos,
+ ))))
+ }
+ ScalarValue::Utf8(None)
+ | ScalarValue::LargeUtf8(None)
+ | ScalarValue::Utf8View(None) => {
+ Ok(ColumnarValue::Scalar(ScalarValue::Time64Nanosecond(None)))
+ }
+ _ => exec_err!("Unsupported data type {scalar:?} for function {name}"),
+ },
+ },
+ n if n >= 2 => {
+ let mut formats = Vec::new();
+ for arg in &args[1..] {
+ match arg {
+ ColumnarValue::Scalar(ScalarValue::Utf8(Some(f)))
+ | ColumnarValue::Scalar(ScalarValue::LargeUtf8(Some(f))) => {
+ formats.push(f.as_str());
+ }
+ _ => {}
+ }
+ }
+ if formats.is_empty() {
+ return exec_err!(
+ "to_time with formats requires at least one format string"
+ );
+ }
+ match &args[0] {
+ ColumnarValue::Array(a) => {
+ let mut builder =
+ PrimitiveArray::<Time64NanosecondType>::builder(a.len());
+ for i in 0..a.len() {
+ if a.is_null(i) {
+ builder.append_null();
+ } else {
+ let s = match a.data_type() {
+ Utf8View => a.as_string_view().value(i),
+ LargeUtf8 => a.as_string::<i64>().value(i),
+ Utf8 => a.as_string::<i32>().value(i),
+ _ => {
+ return exec_err!(
+ "Unsupported data type for function {name}"
+ )
+ }
+ };
+ let mut parsed = false;
+ for fmt in formats.iter() {
+ if let Ok(nanos) =
+ string_to_timestamp_nanos_formatted(s, fmt)
+ {
+ builder.append_value(time_from_timestamp(nanos));
+ parsed = true;
+ break;
+ }
+ }
+ if !parsed {
+ return exec_err!("Unable to parse time from '{s}' using provided formats");
+ }
+ }
+ }
+ Ok(ColumnarValue::Array(Arc::new(builder.finish())))
+ }
+ ColumnarValue::Scalar(scalar) => {
+ let s = match scalar {
+ ScalarValue::Utf8(Some(s)) | ScalarValue::LargeUtf8(Some(s)) => s,
Review Comment:
```suggestion
ScalarValue::Utf8(Some(s))
| ScalarValue::LargeUtf8(Some(s))
| ScalarValue::Utf8View(Some(s)) => s,
```
--
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] Add time functions [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on PR #19031:
URL: https://github.com/apache/datafusion/pull/19031#issuecomment-3599546342
I think it would be preferable to split this PR up into three; for date_bin, make_time and to_time each as it is currently quite a large PR to review
--
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