datafusion_functions/datetime/
to_char.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18use std::any::Any;
19use std::sync::Arc;
20
21use arrow::array::cast::AsArray;
22use arrow::array::{new_null_array, Array, ArrayRef, StringArray};
23use arrow::compute::cast;
24use arrow::datatypes::DataType;
25use arrow::datatypes::DataType::{
26    Date32, Date64, Duration, Time32, Time64, Timestamp, Utf8,
27};
28use arrow::datatypes::TimeUnit::{Microsecond, Millisecond, Nanosecond, Second};
29use arrow::error::ArrowError;
30use arrow::util::display::{ArrayFormatter, DurationFormat, FormatOptions};
31use datafusion_common::{exec_err, utils::take_function_args, Result, ScalarValue};
32use datafusion_expr::TypeSignature::Exact;
33use datafusion_expr::{
34    ColumnarValue, Documentation, ScalarUDFImpl, Signature, Volatility, TIMEZONE_WILDCARD,
35};
36use datafusion_macros::user_doc;
37
38#[user_doc(
39    doc_section(label = "Time and Date Functions"),
40    description = "Returns a string representation of a date, time, timestamp or duration based on a [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html). Unlike the PostgreSQL equivalent of this function numerical formatting is not supported.",
41    syntax_example = "to_char(expression, format)",
42    sql_example = r#"```sql
43> select to_char('2023-03-01'::date, '%d-%m-%Y');
44+----------------------------------------------+
45| to_char(Utf8("2023-03-01"),Utf8("%d-%m-%Y")) |
46+----------------------------------------------+
47| 01-03-2023                                   |
48+----------------------------------------------+
49```
50
51Additional examples can be found [here](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/date_time_functions.rs)
52"#,
53    argument(
54        name = "expression",
55        description = "Expression to operate on. Can be a constant, column, or function that results in a date, time, timestamp or duration."
56    ),
57    argument(
58        name = "format",
59        description = "A [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) string to use to convert the expression."
60    ),
61    argument(
62        name = "day",
63        description = "Day to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators."
64    )
65)]
66#[derive(Debug, PartialEq, Eq, Hash)]
67pub struct ToCharFunc {
68    signature: Signature,
69    aliases: Vec<String>,
70}
71
72impl Default for ToCharFunc {
73    fn default() -> Self {
74        Self::new()
75    }
76}
77
78impl ToCharFunc {
79    pub fn new() -> Self {
80        Self {
81            signature: Signature::one_of(
82                vec![
83                    Exact(vec![Date32, Utf8]),
84                    Exact(vec![Date64, Utf8]),
85                    Exact(vec![Time64(Nanosecond), Utf8]),
86                    Exact(vec![Time64(Microsecond), Utf8]),
87                    Exact(vec![Time32(Millisecond), Utf8]),
88                    Exact(vec![Time32(Second), Utf8]),
89                    Exact(vec![
90                        Timestamp(Nanosecond, Some(TIMEZONE_WILDCARD.into())),
91                        Utf8,
92                    ]),
93                    Exact(vec![Timestamp(Nanosecond, None), Utf8]),
94                    Exact(vec![
95                        Timestamp(Microsecond, Some(TIMEZONE_WILDCARD.into())),
96                        Utf8,
97                    ]),
98                    Exact(vec![Timestamp(Microsecond, None), Utf8]),
99                    Exact(vec![
100                        Timestamp(Millisecond, Some(TIMEZONE_WILDCARD.into())),
101                        Utf8,
102                    ]),
103                    Exact(vec![Timestamp(Millisecond, None), Utf8]),
104                    Exact(vec![
105                        Timestamp(Second, Some(TIMEZONE_WILDCARD.into())),
106                        Utf8,
107                    ]),
108                    Exact(vec![Timestamp(Second, None), Utf8]),
109                    Exact(vec![Duration(Nanosecond), Utf8]),
110                    Exact(vec![Duration(Microsecond), Utf8]),
111                    Exact(vec![Duration(Millisecond), Utf8]),
112                    Exact(vec![Duration(Second), Utf8]),
113                ],
114                Volatility::Immutable,
115            ),
116            aliases: vec![String::from("date_format")],
117        }
118    }
119}
120
121impl ScalarUDFImpl for ToCharFunc {
122    fn as_any(&self) -> &dyn Any {
123        self
124    }
125
126    fn name(&self) -> &str {
127        "to_char"
128    }
129
130    fn signature(&self) -> &Signature {
131        &self.signature
132    }
133
134    fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
135        Ok(Utf8)
136    }
137
138    fn invoke_with_args(
139        &self,
140        args: datafusion_expr::ScalarFunctionArgs,
141    ) -> Result<ColumnarValue> {
142        let args = args.args;
143        let [date_time, format] = take_function_args(self.name(), &args)?;
144
145        match format {
146            ColumnarValue::Scalar(ScalarValue::Utf8(None))
147            | ColumnarValue::Scalar(ScalarValue::Null) => {
148                to_char_scalar(date_time.clone(), None)
149            }
150            // constant format
151            ColumnarValue::Scalar(ScalarValue::Utf8(Some(format))) => {
152                // invoke to_char_scalar with the known string, without converting to array
153                to_char_scalar(date_time.clone(), Some(format))
154            }
155            ColumnarValue::Array(_) => to_char_array(&args),
156            _ => {
157                exec_err!(
158                    "Format for `to_char` must be non-null Utf8, received {:?}",
159                    format.data_type()
160                )
161            }
162        }
163    }
164
165    fn aliases(&self) -> &[String] {
166        &self.aliases
167    }
168
169    fn documentation(&self) -> Option<&Documentation> {
170        self.doc()
171    }
172}
173
174fn build_format_options<'a>(
175    data_type: &DataType,
176    format: Option<&'a str>,
177) -> Result<FormatOptions<'a>, Result<ColumnarValue>> {
178    let Some(format) = format else {
179        return Ok(FormatOptions::new());
180    };
181    let format_options = match data_type {
182        Date32 => FormatOptions::new()
183            .with_date_format(Some(format))
184            .with_datetime_format(Some(format)),
185        Date64 => FormatOptions::new().with_datetime_format(Some(format)),
186        Time32(_) => FormatOptions::new().with_time_format(Some(format)),
187        Time64(_) => FormatOptions::new().with_time_format(Some(format)),
188        Timestamp(_, _) => FormatOptions::new()
189            .with_timestamp_format(Some(format))
190            .with_timestamp_tz_format(Some(format)),
191        Duration(_) => FormatOptions::new().with_duration_format(
192            if "ISO8601".eq_ignore_ascii_case(format) {
193                DurationFormat::ISO8601
194            } else {
195                DurationFormat::Pretty
196            },
197        ),
198        other => {
199            return Err(exec_err!(
200                "to_char only supports date, time, timestamp and duration data types, received {other:?}"
201            ));
202        }
203    };
204    Ok(format_options)
205}
206
207/// Special version when arg\[1] is a scalar
208fn to_char_scalar(
209    expression: ColumnarValue,
210    format: Option<&str>,
211) -> Result<ColumnarValue> {
212    // it's possible that the expression is a scalar however because
213    // of the implementation in arrow-rs we need to convert it to an array
214    let data_type = &expression.data_type();
215    let is_scalar_expression = matches!(&expression, ColumnarValue::Scalar(_));
216    let array = expression.clone().into_array(1)?;
217
218    if format.is_none() {
219        return if is_scalar_expression {
220            Ok(ColumnarValue::Scalar(ScalarValue::Utf8(None)))
221        } else {
222            Ok(ColumnarValue::Array(new_null_array(&Utf8, array.len())))
223        };
224    }
225
226    let format_options = match build_format_options(data_type, format) {
227        Ok(value) => value,
228        Err(value) => return value,
229    };
230
231    let formatter = ArrayFormatter::try_new(array.as_ref(), &format_options)?;
232    let formatted: Result<Vec<Option<String>>, ArrowError> = (0..array.len())
233        .map(|i| {
234            if array.is_null(i) {
235                Ok(None)
236            } else {
237                formatter.value(i).try_to_string().map(Some)
238            }
239        })
240        .collect();
241
242    if let Ok(formatted) = formatted {
243        if is_scalar_expression {
244            Ok(ColumnarValue::Scalar(ScalarValue::Utf8(
245                formatted.first().unwrap().clone(),
246            )))
247        } else {
248            Ok(ColumnarValue::Array(
249                Arc::new(StringArray::from(formatted)) as ArrayRef
250            ))
251        }
252    } else {
253        // if the data type was a Date32, formatting could have failed because the format string
254        // contained datetime specifiers, so we'll retry by casting the date array as a timestamp array
255        if data_type == &Date32 {
256            return to_char_scalar(expression.clone().cast_to(&Date64, None)?, format);
257        }
258
259        exec_err!("{}", formatted.unwrap_err())
260    }
261}
262
263fn to_char_array(args: &[ColumnarValue]) -> Result<ColumnarValue> {
264    let arrays = ColumnarValue::values_to_arrays(args)?;
265    let mut results: Vec<Option<String>> = vec![];
266    let format_array = arrays[1].as_string::<i32>();
267    let data_type = arrays[0].data_type();
268
269    for idx in 0..arrays[0].len() {
270        let format = if format_array.is_null(idx) {
271            None
272        } else {
273            Some(format_array.value(idx))
274        };
275        if format.is_none() {
276            results.push(None);
277            continue;
278        }
279        let format_options = match build_format_options(data_type, format) {
280            Ok(value) => value,
281            Err(value) => return value,
282        };
283        // this isn't ideal but this can't use ValueFormatter as it isn't independent
284        // from ArrayFormatter
285        let formatter = ArrayFormatter::try_new(arrays[0].as_ref(), &format_options)?;
286        let result = formatter.value(idx).try_to_string();
287        match result {
288            Ok(value) => results.push(Some(value)),
289            Err(e) => {
290                // if the data type was a Date32, formatting could have failed because the format string
291                // contained datetime specifiers, so we'll treat this specific date element as a timestamp
292                if data_type == &Date32 {
293                    let failed_date_value = arrays[0].slice(idx, 1);
294
295                    match retry_date_as_timestamp(failed_date_value, &format_options) {
296                        Ok(value) => {
297                            results.push(Some(value));
298                            continue;
299                        }
300                        Err(e) => {
301                            return exec_err!("{}", e);
302                        }
303                    }
304                }
305
306                return exec_err!("{}", e);
307            }
308        }
309    }
310
311    match args[0] {
312        ColumnarValue::Array(_) => Ok(ColumnarValue::Array(Arc::new(StringArray::from(
313            results,
314        )) as ArrayRef)),
315        ColumnarValue::Scalar(_) => match results.first().unwrap() {
316            Some(value) => Ok(ColumnarValue::Scalar(ScalarValue::Utf8(Some(
317                value.to_string(),
318            )))),
319            None => Ok(ColumnarValue::Scalar(ScalarValue::Utf8(None))),
320        },
321    }
322}
323
324fn retry_date_as_timestamp(
325    array_ref: ArrayRef,
326    format_options: &FormatOptions,
327) -> Result<String> {
328    let target_data_type = Date64;
329
330    let date_value = cast(&array_ref, &target_data_type)?;
331    let formatter = ArrayFormatter::try_new(date_value.as_ref(), format_options)?;
332    let result = formatter.value(0).try_to_string()?;
333
334    Ok(result)
335}
336
337#[cfg(test)]
338mod tests {
339    use crate::datetime::to_char::ToCharFunc;
340    use arrow::array::{
341        Array, ArrayRef, Date32Array, Date64Array, StringArray, Time32MillisecondArray,
342        Time32SecondArray, Time64MicrosecondArray, Time64NanosecondArray,
343        TimestampMicrosecondArray, TimestampMillisecondArray, TimestampNanosecondArray,
344        TimestampSecondArray,
345    };
346    use arrow::datatypes::{DataType, Field, TimeUnit};
347    use chrono::{NaiveDateTime, Timelike};
348    use datafusion_common::config::ConfigOptions;
349    use datafusion_common::ScalarValue;
350    use datafusion_expr::{ColumnarValue, ScalarUDFImpl};
351    use std::sync::Arc;
352
353    #[test]
354    fn test_array_array() {
355        let array_array_data = vec![(
356            Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
357            StringArray::from(vec!["%Y::%m::%d", "%Y::%m::%d %S::%M::%H %f"]),
358            StringArray::from(vec!["2020::09::01", "2020::09::02 00::00::00 000000000"]),
359        )];
360
361        for (value, format, expected) in array_array_data {
362            let batch_len = value.len();
363            let value_data_type = value.data_type().clone();
364            let format_data_type = format.data_type().clone();
365
366            let args = datafusion_expr::ScalarFunctionArgs {
367                args: vec![
368                    ColumnarValue::Array(value),
369                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
370                ],
371                arg_fields: vec![
372                    Field::new("a", value_data_type, true).into(),
373                    Field::new("b", format_data_type, true).into(),
374                ],
375                number_rows: batch_len,
376                return_field: Field::new("f", DataType::Utf8, true).into(),
377                config_options: Arc::clone(&Arc::new(ConfigOptions::default())),
378            };
379            let result = ToCharFunc::new()
380                .invoke_with_args(args)
381                .expect("that to_char parsed values without error");
382
383            if let ColumnarValue::Array(result) = result {
384                assert_eq!(result.len(), 2);
385                assert_eq!(&expected as &dyn Array, result.as_ref());
386            } else {
387                panic!("Expected an array value")
388            }
389        }
390    }
391
392    #[test]
393    fn test_to_char() {
394        let date = "2020-01-02T03:04:05"
395            .parse::<NaiveDateTime>()
396            .unwrap()
397            .with_nanosecond(12345)
398            .unwrap();
399        let date2 = "2026-07-08T09:10:11"
400            .parse::<NaiveDateTime>()
401            .unwrap()
402            .with_nanosecond(56789)
403            .unwrap();
404
405        let scalar_data = vec![
406            (
407                ScalarValue::Date32(Some(18506)),
408                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
409                "2020::09::01".to_string(),
410            ),
411            (
412                ScalarValue::Date32(Some(18506)),
413                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
414                "2020::09::01 00::00::00 000000000".to_string(),
415            ),
416            (
417                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
418                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
419                "2020::01::02".to_string(),
420            ),
421            (
422                ScalarValue::Time32Second(Some(31851)),
423                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
424                "08-50-51".to_string(),
425            ),
426            (
427                ScalarValue::Time32Millisecond(Some(18506000)),
428                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
429                "05-08-26".to_string(),
430            ),
431            (
432                ScalarValue::Time64Microsecond(Some(12344567000)),
433                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
434                "03-25-44 567000000".to_string(),
435            ),
436            (
437                ScalarValue::Time64Nanosecond(Some(12344567890000)),
438                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
439                "03-25-44 567890000".to_string(),
440            ),
441            (
442                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
443                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
444                "2020::01::02 05::04::03".to_string(),
445            ),
446            (
447                ScalarValue::TimestampMillisecond(
448                    Some(date.and_utc().timestamp_millis()),
449                    None,
450                ),
451                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
452                "2020::01::02 05::04::03".to_string(),
453            ),
454            (
455                ScalarValue::TimestampMicrosecond(
456                    Some(date.and_utc().timestamp_micros()),
457                    None,
458                ),
459                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
460                "2020::01::02 05::04::03 000012000".to_string(),
461            ),
462            (
463                ScalarValue::TimestampNanosecond(
464                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
465                    None,
466                ),
467                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
468                "2020::01::02 05::04::03 000012345".to_string(),
469            ),
470        ];
471
472        for (value, format, expected) in scalar_data {
473            let arg_fields = vec![
474                Field::new("a", value.data_type(), false).into(),
475                Field::new("a", format.data_type(), false).into(),
476            ];
477            let args = datafusion_expr::ScalarFunctionArgs {
478                args: vec![ColumnarValue::Scalar(value), ColumnarValue::Scalar(format)],
479                arg_fields,
480                number_rows: 1,
481                return_field: Field::new("f", DataType::Utf8, true).into(),
482                config_options: Arc::new(ConfigOptions::default()),
483            };
484            let result = ToCharFunc::new()
485                .invoke_with_args(args)
486                .expect("that to_char parsed values without error");
487
488            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
489                assert_eq!(expected, date.unwrap());
490            } else {
491                panic!("Expected a scalar value")
492            }
493        }
494
495        let scalar_array_data = vec![
496            (
497                ScalarValue::Date32(Some(18506)),
498                StringArray::from(vec!["%Y::%m::%d".to_string()]),
499                "2020::09::01".to_string(),
500            ),
501            (
502                ScalarValue::Date32(Some(18506)),
503                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
504                "2020::09::01 00::00::00 000000000".to_string(),
505            ),
506            (
507                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
508                StringArray::from(vec!["%Y::%m::%d".to_string()]),
509                "2020::01::02".to_string(),
510            ),
511            (
512                ScalarValue::Time32Second(Some(31851)),
513                StringArray::from(vec!["%H-%M-%S".to_string()]),
514                "08-50-51".to_string(),
515            ),
516            (
517                ScalarValue::Time32Millisecond(Some(18506000)),
518                StringArray::from(vec!["%H-%M-%S".to_string()]),
519                "05-08-26".to_string(),
520            ),
521            (
522                ScalarValue::Time64Microsecond(Some(12344567000)),
523                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
524                "03-25-44 567000000".to_string(),
525            ),
526            (
527                ScalarValue::Time64Nanosecond(Some(12344567890000)),
528                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
529                "03-25-44 567890000".to_string(),
530            ),
531            (
532                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
533                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
534                "2020::01::02 05::04::03".to_string(),
535            ),
536            (
537                ScalarValue::TimestampMillisecond(
538                    Some(date.and_utc().timestamp_millis()),
539                    None,
540                ),
541                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
542                "2020::01::02 05::04::03".to_string(),
543            ),
544            (
545                ScalarValue::TimestampMicrosecond(
546                    Some(date.and_utc().timestamp_micros()),
547                    None,
548                ),
549                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
550                "2020::01::02 05::04::03 000012000".to_string(),
551            ),
552            (
553                ScalarValue::TimestampNanosecond(
554                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
555                    None,
556                ),
557                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
558                "2020::01::02 05::04::03 000012345".to_string(),
559            ),
560        ];
561
562        for (value, format, expected) in scalar_array_data {
563            let batch_len = format.len();
564            let arg_fields = vec![
565                Field::new("a", value.data_type(), false).into(),
566                Field::new("a", format.data_type().to_owned(), false).into(),
567            ];
568            let args = datafusion_expr::ScalarFunctionArgs {
569                args: vec![
570                    ColumnarValue::Scalar(value),
571                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
572                ],
573                arg_fields,
574                number_rows: batch_len,
575                return_field: Field::new("f", DataType::Utf8, true).into(),
576                config_options: Arc::new(ConfigOptions::default()),
577            };
578            let result = ToCharFunc::new()
579                .invoke_with_args(args)
580                .expect("that to_char parsed values without error");
581
582            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
583                assert_eq!(expected, date.unwrap());
584            } else {
585                panic!("Expected a scalar value")
586            }
587        }
588
589        let array_scalar_data = vec![
590            (
591                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
592                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
593                StringArray::from(vec!["2020::09::01", "2020::09::02"]),
594            ),
595            (
596                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
597                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
598                StringArray::from(vec![
599                    "2020::09::01 00::00::00 000000000",
600                    "2020::09::02 00::00::00 000000000",
601                ]),
602            ),
603            (
604                Arc::new(Date64Array::from(vec![
605                    date.and_utc().timestamp_millis(),
606                    date2.and_utc().timestamp_millis(),
607                ])) as ArrayRef,
608                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
609                StringArray::from(vec!["2020::01::02", "2026::07::08"]),
610            ),
611        ];
612
613        let array_array_data = vec![
614            (
615                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
616                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
617                StringArray::from(vec!["2020::09::01", "02::09::2020"]),
618            ),
619            (
620                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
621                StringArray::from(vec![
622                    "%Y::%m::%d %S::%M::%H %f",
623                    "%Y::%m::%d %S::%M::%H %f",
624                ]),
625                StringArray::from(vec![
626                    "2020::09::01 00::00::00 000000000",
627                    "2020::09::02 00::00::00 000000000",
628                ]),
629            ),
630            (
631                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
632                StringArray::from(vec!["%Y::%m::%d", "%Y::%m::%d %S::%M::%H %f"]),
633                StringArray::from(vec![
634                    "2020::09::01",
635                    "2020::09::02 00::00::00 000000000",
636                ]),
637            ),
638            (
639                Arc::new(Date64Array::from(vec![
640                    date.and_utc().timestamp_millis(),
641                    date2.and_utc().timestamp_millis(),
642                ])) as ArrayRef,
643                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
644                StringArray::from(vec!["2020::01::02", "08::07::2026"]),
645            ),
646            (
647                Arc::new(Time32MillisecondArray::from(vec![1850600, 1860700]))
648                    as ArrayRef,
649                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
650                StringArray::from(vec!["00:30:50", "00::31::00"]),
651            ),
652            (
653                Arc::new(Time32SecondArray::from(vec![18506, 18507])) as ArrayRef,
654                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
655                StringArray::from(vec!["05:08:26", "05::08::27"]),
656            ),
657            (
658                Arc::new(Time64MicrosecondArray::from(vec![12344567000, 22244567000]))
659                    as ArrayRef,
660                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
661                StringArray::from(vec!["03:25:44", "06::10::44"]),
662            ),
663            (
664                Arc::new(Time64NanosecondArray::from(vec![
665                    1234456789000,
666                    2224456789000,
667                ])) as ArrayRef,
668                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
669                StringArray::from(vec!["00:20:34", "00::37::04"]),
670            ),
671            (
672                Arc::new(TimestampSecondArray::from(vec![
673                    date.and_utc().timestamp(),
674                    date2.and_utc().timestamp(),
675                ])) as ArrayRef,
676                StringArray::from(vec!["%Y::%m::%d %S::%M::%H", "%d::%m::%Y %S-%M-%H"]),
677                StringArray::from(vec![
678                    "2020::01::02 05::04::03",
679                    "08::07::2026 11-10-09",
680                ]),
681            ),
682            (
683                Arc::new(TimestampMillisecondArray::from(vec![
684                    date.and_utc().timestamp_millis(),
685                    date2.and_utc().timestamp_millis(),
686                ])) as ArrayRef,
687                StringArray::from(vec![
688                    "%Y::%m::%d %S::%M::%H %f",
689                    "%d::%m::%Y %S-%M-%H %f",
690                ]),
691                StringArray::from(vec![
692                    "2020::01::02 05::04::03 000000000",
693                    "08::07::2026 11-10-09 000000000",
694                ]),
695            ),
696            (
697                Arc::new(TimestampMicrosecondArray::from(vec![
698                    date.and_utc().timestamp_micros(),
699                    date2.and_utc().timestamp_micros(),
700                ])) as ArrayRef,
701                StringArray::from(vec![
702                    "%Y::%m::%d %S::%M::%H %f",
703                    "%d::%m::%Y %S-%M-%H %f",
704                ]),
705                StringArray::from(vec![
706                    "2020::01::02 05::04::03 000012000",
707                    "08::07::2026 11-10-09 000056000",
708                ]),
709            ),
710            (
711                Arc::new(TimestampNanosecondArray::from(vec![
712                    date.and_utc().timestamp_nanos_opt().unwrap(),
713                    date2.and_utc().timestamp_nanos_opt().unwrap(),
714                ])) as ArrayRef,
715                StringArray::from(vec![
716                    "%Y::%m::%d %S::%M::%H %f",
717                    "%d::%m::%Y %S-%M-%H %f",
718                ]),
719                StringArray::from(vec![
720                    "2020::01::02 05::04::03 000012345",
721                    "08::07::2026 11-10-09 000056789",
722                ]),
723            ),
724        ];
725
726        for (value, format, expected) in array_scalar_data {
727            let batch_len = value.len();
728            let arg_fields = vec![
729                Field::new("a", value.data_type().clone(), false).into(),
730                Field::new("a", format.data_type(), false).into(),
731            ];
732            let args = datafusion_expr::ScalarFunctionArgs {
733                args: vec![
734                    ColumnarValue::Array(value as ArrayRef),
735                    ColumnarValue::Scalar(format),
736                ],
737                arg_fields,
738                number_rows: batch_len,
739                return_field: Field::new("f", DataType::Utf8, true).into(),
740                config_options: Arc::new(ConfigOptions::default()),
741            };
742            let result = ToCharFunc::new()
743                .invoke_with_args(args)
744                .expect("that to_char parsed values without error");
745
746            if let ColumnarValue::Array(result) = result {
747                assert_eq!(result.len(), 2);
748                assert_eq!(&expected as &dyn Array, result.as_ref());
749            } else {
750                panic!("Expected an array value")
751            }
752        }
753
754        for (value, format, expected) in array_array_data {
755            let batch_len = value.len();
756            let arg_fields = vec![
757                Field::new("a", value.data_type().clone(), false).into(),
758                Field::new("a", format.data_type().clone(), false).into(),
759            ];
760            let args = datafusion_expr::ScalarFunctionArgs {
761                args: vec![
762                    ColumnarValue::Array(value),
763                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
764                ],
765                arg_fields,
766                number_rows: batch_len,
767                return_field: Field::new("f", DataType::Utf8, true).into(),
768                config_options: Arc::new(ConfigOptions::default()),
769            };
770            let result = ToCharFunc::new()
771                .invoke_with_args(args)
772                .expect("that to_char parsed values without error");
773
774            if let ColumnarValue::Array(result) = result {
775                assert_eq!(result.len(), 2);
776                assert_eq!(&expected as &dyn Array, result.as_ref());
777            } else {
778                panic!("Expected an array value")
779            }
780        }
781
782        //
783        // Fallible test cases
784        //
785
786        // invalid number of arguments
787        let arg_field = Field::new("a", DataType::Int32, true).into();
788        let args = datafusion_expr::ScalarFunctionArgs {
789            args: vec![ColumnarValue::Scalar(ScalarValue::Int32(Some(1)))],
790            arg_fields: vec![arg_field],
791            number_rows: 1,
792            return_field: Field::new("f", DataType::Utf8, true).into(),
793            config_options: Arc::new(ConfigOptions::default()),
794        };
795        let result = ToCharFunc::new().invoke_with_args(args);
796        assert_eq!(
797            result.err().unwrap().strip_backtrace(),
798            "Execution error: to_char function requires 2 arguments, got 1"
799        );
800
801        // invalid type
802        let arg_fields = vec![
803            Field::new("a", DataType::Utf8, true).into(),
804            Field::new("a", DataType::Timestamp(TimeUnit::Nanosecond, None), true).into(),
805        ];
806        let args = datafusion_expr::ScalarFunctionArgs {
807            args: vec![
808                ColumnarValue::Scalar(ScalarValue::Int32(Some(1))),
809                ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(Some(1), None)),
810            ],
811            arg_fields,
812            number_rows: 1,
813            return_field: Field::new("f", DataType::Utf8, true).into(),
814            config_options: Arc::new(ConfigOptions::default()),
815        };
816        let result = ToCharFunc::new().invoke_with_args(args);
817        assert_eq!(
818            result.err().unwrap().strip_backtrace(),
819            "Execution error: Format for `to_char` must be non-null Utf8, received Timestamp(Nanosecond, None)"
820        );
821    }
822}
datafusion_functions/datetime/to_char.rs

datafusion_functions/datetime/
to_char.rs
