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::{Array, ArrayRef, StringArray, new_null_array};
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::{Result, ScalarValue, exec_err, utils::take_function_args};
32use datafusion_expr::TypeSignature::Exact;
33use datafusion_expr::{
34    ColumnarValue, Documentation, ScalarUDFImpl, Signature, TIMEZONE_WILDCARD, Volatility,
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/builtin_functions/date_time.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) => to_char_scalar(date_time, None),
148            // constant format
149            ColumnarValue::Scalar(ScalarValue::Utf8(Some(format))) => {
150                // invoke to_char_scalar with the known string, without converting to array
151                to_char_scalar(date_time, Some(format))
152            }
153            ColumnarValue::Array(_) => to_char_array(&args),
154            _ => {
155                exec_err!(
156                    "Format for `to_char` must be non-null Utf8, received {:?}",
157                    format.data_type()
158                )
159            }
160        }
161    }
162
163    fn aliases(&self) -> &[String] {
164        &self.aliases
165    }
166
167    fn documentation(&self) -> Option<&Documentation> {
168        self.doc()
169    }
170}
171
172fn build_format_options<'a>(
173    data_type: &DataType,
174    format: Option<&'a str>,
175) -> Result<FormatOptions<'a>, Result<ColumnarValue>> {
176    let Some(format) = format else {
177        return Ok(FormatOptions::new());
178    };
179    let format_options = match data_type {
180        Date32 => FormatOptions::new()
181            .with_date_format(Some(format))
182            .with_datetime_format(Some(format)),
183        Date64 => FormatOptions::new().with_datetime_format(Some(format)),
184        Time32(_) => FormatOptions::new().with_time_format(Some(format)),
185        Time64(_) => FormatOptions::new().with_time_format(Some(format)),
186        Timestamp(_, _) => FormatOptions::new()
187            .with_timestamp_format(Some(format))
188            .with_timestamp_tz_format(Some(format)),
189        Duration(_) => FormatOptions::new().with_duration_format(
190            if "ISO8601".eq_ignore_ascii_case(format) {
191                DurationFormat::ISO8601
192            } else {
193                DurationFormat::Pretty
194            },
195        ),
196        other => {
197            return Err(exec_err!(
198                "to_char only supports date, time, timestamp and duration data types, received {other:?}"
199            ));
200        }
201    };
202    Ok(format_options)
203}
204
205/// Special version when arg\[1] is a scalar
206fn to_char_scalar(
207    expression: &ColumnarValue,
208    format: Option<&str>,
209) -> Result<ColumnarValue> {
210    // it's possible that the expression is a scalar however because
211    // of the implementation in arrow-rs we need to convert it to an array
212    let data_type = &expression.data_type();
213    let is_scalar_expression = matches!(&expression, ColumnarValue::Scalar(_));
214    let array = expression.clone().into_array(1)?;
215
216    if format.is_none() {
217        return if is_scalar_expression {
218            Ok(ColumnarValue::Scalar(ScalarValue::Utf8(None)))
219        } else {
220            Ok(ColumnarValue::Array(new_null_array(&Utf8, array.len())))
221        };
222    }
223
224    let format_options = match build_format_options(data_type, format) {
225        Ok(value) => value,
226        Err(value) => return value,
227    };
228
229    let formatter = ArrayFormatter::try_new(array.as_ref(), &format_options)?;
230    let formatted: Result<Vec<Option<String>>, ArrowError> = (0..array.len())
231        .map(|i| {
232            if array.is_null(i) {
233                Ok(None)
234            } else {
235                formatter.value(i).try_to_string().map(Some)
236            }
237        })
238        .collect();
239
240    if let Ok(formatted) = formatted {
241        if is_scalar_expression {
242            Ok(ColumnarValue::Scalar(ScalarValue::Utf8(
243                formatted.first().unwrap().clone(),
244            )))
245        } else {
246            Ok(ColumnarValue::Array(
247                Arc::new(StringArray::from(formatted)) as ArrayRef
248            ))
249        }
250    } else {
251        // if the data type was a Date32, formatting could have failed because the format string
252        // contained datetime specifiers, so we'll retry by casting the date array as a timestamp array
253        if data_type == &Date32 {
254            return to_char_scalar(&expression.cast_to(&Date64, None)?, format);
255        }
256
257        exec_err!("{}", formatted.unwrap_err())
258    }
259}
260
261fn to_char_array(args: &[ColumnarValue]) -> Result<ColumnarValue> {
262    let arrays = ColumnarValue::values_to_arrays(args)?;
263    let mut results: Vec<Option<String>> = vec![];
264    let format_array = arrays[1].as_string::<i32>();
265    let data_type = arrays[0].data_type();
266
267    for idx in 0..arrays[0].len() {
268        let format = if format_array.is_null(idx) {
269            None
270        } else {
271            Some(format_array.value(idx))
272        };
273        if format.is_none() {
274            results.push(None);
275            continue;
276        }
277        let format_options = match build_format_options(data_type, format) {
278            Ok(value) => value,
279            Err(value) => return value,
280        };
281        // this isn't ideal but this can't use ValueFormatter as it isn't independent
282        // from ArrayFormatter
283        let formatter = ArrayFormatter::try_new(arrays[0].as_ref(), &format_options)?;
284        let result = formatter.value(idx).try_to_string();
285        match result {
286            Ok(value) => results.push(Some(value)),
287            Err(e) => {
288                // if the data type was a Date32, formatting could have failed because the format string
289                // contained datetime specifiers, so we'll treat this specific date element as a timestamp
290                if data_type == &Date32 {
291                    let failed_date_value = arrays[0].slice(idx, 1);
292
293                    match retry_date_as_timestamp(&failed_date_value, &format_options) {
294                        Ok(value) => {
295                            results.push(Some(value));
296                            continue;
297                        }
298                        Err(e) => {
299                            return exec_err!("{}", e);
300                        }
301                    }
302                }
303
304                return exec_err!("{}", e);
305            }
306        }
307    }
308
309    match args[0] {
310        ColumnarValue::Array(_) => Ok(ColumnarValue::Array(Arc::new(StringArray::from(
311            results,
312        )) as ArrayRef)),
313        ColumnarValue::Scalar(_) => match results.first().unwrap() {
314            Some(value) => Ok(ColumnarValue::Scalar(ScalarValue::Utf8(Some(
315                value.to_string(),
316            )))),
317            None => Ok(ColumnarValue::Scalar(ScalarValue::Utf8(None))),
318        },
319    }
320}
321
322fn retry_date_as_timestamp(
323    array_ref: &ArrayRef,
324    format_options: &FormatOptions,
325) -> Result<String> {
326    let target_data_type = Date64;
327
328    let date_value = cast(&array_ref, &target_data_type)?;
329    let formatter = ArrayFormatter::try_new(date_value.as_ref(), format_options)?;
330    let result = formatter.value(0).try_to_string()?;
331
332    Ok(result)
333}
334
335#[cfg(test)]
336mod tests {
337    use crate::datetime::to_char::ToCharFunc;
338    use arrow::array::{
339        Array, ArrayRef, Date32Array, Date64Array, StringArray, Time32MillisecondArray,
340        Time32SecondArray, Time64MicrosecondArray, Time64NanosecondArray,
341        TimestampMicrosecondArray, TimestampMillisecondArray, TimestampNanosecondArray,
342        TimestampSecondArray,
343    };
344    use arrow::datatypes::{DataType, Field, TimeUnit};
345    use chrono::{NaiveDateTime, Timelike};
346    use datafusion_common::ScalarValue;
347    use datafusion_common::config::ConfigOptions;
348    use datafusion_expr::{ColumnarValue, ScalarUDFImpl};
349    use std::sync::Arc;
350
351    #[test]
352    fn test_array_array() {
353        let array_array_data = vec![(
354            Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
355            StringArray::from(vec!["%Y::%m::%d", "%Y::%m::%d %S::%M::%H %f"]),
356            StringArray::from(vec!["2020::09::01", "2020::09::02 00::00::00 000000000"]),
357        )];
358
359        for (value, format, expected) in array_array_data {
360            let batch_len = value.len();
361            let value_data_type = value.data_type().clone();
362            let format_data_type = format.data_type().clone();
363
364            let args = datafusion_expr::ScalarFunctionArgs {
365                args: vec![
366                    ColumnarValue::Array(value),
367                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
368                ],
369                arg_fields: vec![
370                    Field::new("a", value_data_type, true).into(),
371                    Field::new("b", format_data_type, true).into(),
372                ],
373                number_rows: batch_len,
374                return_field: Field::new("f", DataType::Utf8, true).into(),
375                config_options: Arc::clone(&Arc::new(ConfigOptions::default())),
376            };
377            let result = ToCharFunc::new()
378                .invoke_with_args(args)
379                .expect("that to_char parsed values without error");
380
381            if let ColumnarValue::Array(result) = result {
382                assert_eq!(result.len(), 2);
383                assert_eq!(&expected as &dyn Array, result.as_ref());
384            } else {
385                panic!("Expected an array value")
386            }
387        }
388    }
389
390    #[test]
391    fn test_to_char() {
392        let date = "2020-01-02T03:04:05"
393            .parse::<NaiveDateTime>()
394            .unwrap()
395            .with_nanosecond(12345)
396            .unwrap();
397        let date2 = "2026-07-08T09:10:11"
398            .parse::<NaiveDateTime>()
399            .unwrap()
400            .with_nanosecond(56789)
401            .unwrap();
402
403        let scalar_data = vec![
404            (
405                ScalarValue::Date32(Some(18506)),
406                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
407                "2020::09::01".to_string(),
408            ),
409            (
410                ScalarValue::Date32(Some(18506)),
411                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
412                "2020::09::01 00::00::00 000000000".to_string(),
413            ),
414            (
415                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
416                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
417                "2020::01::02".to_string(),
418            ),
419            (
420                ScalarValue::Time32Second(Some(31851)),
421                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
422                "08-50-51".to_string(),
423            ),
424            (
425                ScalarValue::Time32Millisecond(Some(18506000)),
426                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
427                "05-08-26".to_string(),
428            ),
429            (
430                ScalarValue::Time64Microsecond(Some(12344567000)),
431                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
432                "03-25-44 567000000".to_string(),
433            ),
434            (
435                ScalarValue::Time64Nanosecond(Some(12344567890000)),
436                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
437                "03-25-44 567890000".to_string(),
438            ),
439            (
440                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
441                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
442                "2020::01::02 05::04::03".to_string(),
443            ),
444            (
445                ScalarValue::TimestampMillisecond(
446                    Some(date.and_utc().timestamp_millis()),
447                    None,
448                ),
449                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
450                "2020::01::02 05::04::03".to_string(),
451            ),
452            (
453                ScalarValue::TimestampMicrosecond(
454                    Some(date.and_utc().timestamp_micros()),
455                    None,
456                ),
457                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
458                "2020::01::02 05::04::03 000012000".to_string(),
459            ),
460            (
461                ScalarValue::TimestampNanosecond(
462                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
463                    None,
464                ),
465                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
466                "2020::01::02 05::04::03 000012345".to_string(),
467            ),
468        ];
469
470        for (value, format, expected) in scalar_data {
471            let arg_fields = vec![
472                Field::new("a", value.data_type(), false).into(),
473                Field::new("a", format.data_type(), false).into(),
474            ];
475            let args = datafusion_expr::ScalarFunctionArgs {
476                args: vec![ColumnarValue::Scalar(value), ColumnarValue::Scalar(format)],
477                arg_fields,
478                number_rows: 1,
479                return_field: Field::new("f", DataType::Utf8, true).into(),
480                config_options: Arc::new(ConfigOptions::default()),
481            };
482            let result = ToCharFunc::new()
483                .invoke_with_args(args)
484                .expect("that to_char parsed values without error");
485
486            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
487                assert_eq!(expected, date.unwrap());
488            } else {
489                panic!("Expected a scalar value")
490            }
491        }
492
493        let scalar_array_data = vec![
494            (
495                ScalarValue::Date32(Some(18506)),
496                StringArray::from(vec!["%Y::%m::%d".to_string()]),
497                "2020::09::01".to_string(),
498            ),
499            (
500                ScalarValue::Date32(Some(18506)),
501                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
502                "2020::09::01 00::00::00 000000000".to_string(),
503            ),
504            (
505                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
506                StringArray::from(vec!["%Y::%m::%d".to_string()]),
507                "2020::01::02".to_string(),
508            ),
509            (
510                ScalarValue::Time32Second(Some(31851)),
511                StringArray::from(vec!["%H-%M-%S".to_string()]),
512                "08-50-51".to_string(),
513            ),
514            (
515                ScalarValue::Time32Millisecond(Some(18506000)),
516                StringArray::from(vec!["%H-%M-%S".to_string()]),
517                "05-08-26".to_string(),
518            ),
519            (
520                ScalarValue::Time64Microsecond(Some(12344567000)),
521                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
522                "03-25-44 567000000".to_string(),
523            ),
524            (
525                ScalarValue::Time64Nanosecond(Some(12344567890000)),
526                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
527                "03-25-44 567890000".to_string(),
528            ),
529            (
530                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
531                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
532                "2020::01::02 05::04::03".to_string(),
533            ),
534            (
535                ScalarValue::TimestampMillisecond(
536                    Some(date.and_utc().timestamp_millis()),
537                    None,
538                ),
539                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
540                "2020::01::02 05::04::03".to_string(),
541            ),
542            (
543                ScalarValue::TimestampMicrosecond(
544                    Some(date.and_utc().timestamp_micros()),
545                    None,
546                ),
547                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
548                "2020::01::02 05::04::03 000012000".to_string(),
549            ),
550            (
551                ScalarValue::TimestampNanosecond(
552                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
553                    None,
554                ),
555                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
556                "2020::01::02 05::04::03 000012345".to_string(),
557            ),
558        ];
559
560        for (value, format, expected) in scalar_array_data {
561            let batch_len = format.len();
562            let arg_fields = vec![
563                Field::new("a", value.data_type(), false).into(),
564                Field::new("a", format.data_type().to_owned(), false).into(),
565            ];
566            let args = datafusion_expr::ScalarFunctionArgs {
567                args: vec![
568                    ColumnarValue::Scalar(value),
569                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
570                ],
571                arg_fields,
572                number_rows: batch_len,
573                return_field: Field::new("f", DataType::Utf8, true).into(),
574                config_options: Arc::new(ConfigOptions::default()),
575            };
576            let result = ToCharFunc::new()
577                .invoke_with_args(args)
578                .expect("that to_char parsed values without error");
579
580            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
581                assert_eq!(expected, date.unwrap());
582            } else {
583                panic!("Expected a scalar value")
584            }
585        }
586
587        let array_scalar_data = vec![
588            (
589                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
590                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
591                StringArray::from(vec!["2020::09::01", "2020::09::02"]),
592            ),
593            (
594                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
595                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
596                StringArray::from(vec![
597                    "2020::09::01 00::00::00 000000000",
598                    "2020::09::02 00::00::00 000000000",
599                ]),
600            ),
601            (
602                Arc::new(Date64Array::from(vec![
603                    date.and_utc().timestamp_millis(),
604                    date2.and_utc().timestamp_millis(),
605                ])) as ArrayRef,
606                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
607                StringArray::from(vec!["2020::01::02", "2026::07::08"]),
608            ),
609        ];
610
611        let array_array_data = vec![
612            (
613                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
614                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
615                StringArray::from(vec!["2020::09::01", "02::09::2020"]),
616            ),
617            (
618                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
619                StringArray::from(vec![
620                    "%Y::%m::%d %S::%M::%H %f",
621                    "%Y::%m::%d %S::%M::%H %f",
622                ]),
623                StringArray::from(vec![
624                    "2020::09::01 00::00::00 000000000",
625                    "2020::09::02 00::00::00 000000000",
626                ]),
627            ),
628            (
629                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
630                StringArray::from(vec!["%Y::%m::%d", "%Y::%m::%d %S::%M::%H %f"]),
631                StringArray::from(vec![
632                    "2020::09::01",
633                    "2020::09::02 00::00::00 000000000",
634                ]),
635            ),
636            (
637                Arc::new(Date64Array::from(vec![
638                    date.and_utc().timestamp_millis(),
639                    date2.and_utc().timestamp_millis(),
640                ])) as ArrayRef,
641                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
642                StringArray::from(vec!["2020::01::02", "08::07::2026"]),
643            ),
644            (
645                Arc::new(Time32MillisecondArray::from(vec![1850600, 1860700]))
646                    as ArrayRef,
647                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
648                StringArray::from(vec!["00:30:50", "00::31::00"]),
649            ),
650            (
651                Arc::new(Time32SecondArray::from(vec![18506, 18507])) as ArrayRef,
652                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
653                StringArray::from(vec!["05:08:26", "05::08::27"]),
654            ),
655            (
656                Arc::new(Time64MicrosecondArray::from(vec![12344567000, 22244567000]))
657                    as ArrayRef,
658                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
659                StringArray::from(vec!["03:25:44", "06::10::44"]),
660            ),
661            (
662                Arc::new(Time64NanosecondArray::from(vec![
663                    1234456789000,
664                    2224456789000,
665                ])) as ArrayRef,
666                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
667                StringArray::from(vec!["00:20:34", "00::37::04"]),
668            ),
669            (
670                Arc::new(TimestampSecondArray::from(vec![
671                    date.and_utc().timestamp(),
672                    date2.and_utc().timestamp(),
673                ])) as ArrayRef,
674                StringArray::from(vec!["%Y::%m::%d %S::%M::%H", "%d::%m::%Y %S-%M-%H"]),
675                StringArray::from(vec![
676                    "2020::01::02 05::04::03",
677                    "08::07::2026 11-10-09",
678                ]),
679            ),
680            (
681                Arc::new(TimestampMillisecondArray::from(vec![
682                    date.and_utc().timestamp_millis(),
683                    date2.and_utc().timestamp_millis(),
684                ])) as ArrayRef,
685                StringArray::from(vec![
686                    "%Y::%m::%d %S::%M::%H %f",
687                    "%d::%m::%Y %S-%M-%H %f",
688                ]),
689                StringArray::from(vec![
690                    "2020::01::02 05::04::03 000000000",
691                    "08::07::2026 11-10-09 000000000",
692                ]),
693            ),
694            (
695                Arc::new(TimestampMicrosecondArray::from(vec![
696                    date.and_utc().timestamp_micros(),
697                    date2.and_utc().timestamp_micros(),
698                ])) as ArrayRef,
699                StringArray::from(vec![
700                    "%Y::%m::%d %S::%M::%H %f",
701                    "%d::%m::%Y %S-%M-%H %f",
702                ]),
703                StringArray::from(vec![
704                    "2020::01::02 05::04::03 000012000",
705                    "08::07::2026 11-10-09 000056000",
706                ]),
707            ),
708            (
709                Arc::new(TimestampNanosecondArray::from(vec![
710                    date.and_utc().timestamp_nanos_opt().unwrap(),
711                    date2.and_utc().timestamp_nanos_opt().unwrap(),
712                ])) as ArrayRef,
713                StringArray::from(vec![
714                    "%Y::%m::%d %S::%M::%H %f",
715                    "%d::%m::%Y %S-%M-%H %f",
716                ]),
717                StringArray::from(vec![
718                    "2020::01::02 05::04::03 000012345",
719                    "08::07::2026 11-10-09 000056789",
720                ]),
721            ),
722        ];
723
724        for (value, format, expected) in array_scalar_data {
725            let batch_len = value.len();
726            let arg_fields = vec![
727                Field::new("a", value.data_type().clone(), false).into(),
728                Field::new("a", format.data_type(), false).into(),
729            ];
730            let args = datafusion_expr::ScalarFunctionArgs {
731                args: vec![
732                    ColumnarValue::Array(value as ArrayRef),
733                    ColumnarValue::Scalar(format),
734                ],
735                arg_fields,
736                number_rows: batch_len,
737                return_field: Field::new("f", DataType::Utf8, true).into(),
738                config_options: Arc::new(ConfigOptions::default()),
739            };
740            let result = ToCharFunc::new()
741                .invoke_with_args(args)
742                .expect("that to_char parsed values without error");
743
744            if let ColumnarValue::Array(result) = result {
745                assert_eq!(result.len(), 2);
746                assert_eq!(&expected as &dyn Array, result.as_ref());
747            } else {
748                panic!("Expected an array value")
749            }
750        }
751
752        for (value, format, expected) in array_array_data {
753            let batch_len = value.len();
754            let arg_fields = vec![
755                Field::new("a", value.data_type().clone(), false).into(),
756                Field::new("a", format.data_type().clone(), false).into(),
757            ];
758            let args = datafusion_expr::ScalarFunctionArgs {
759                args: vec![
760                    ColumnarValue::Array(value),
761                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
762                ],
763                arg_fields,
764                number_rows: batch_len,
765                return_field: Field::new("f", DataType::Utf8, true).into(),
766                config_options: Arc::new(ConfigOptions::default()),
767            };
768            let result = ToCharFunc::new()
769                .invoke_with_args(args)
770                .expect("that to_char parsed values without error");
771
772            if let ColumnarValue::Array(result) = result {
773                assert_eq!(result.len(), 2);
774                assert_eq!(&expected as &dyn Array, result.as_ref());
775            } else {
776                panic!("Expected an array value")
777            }
778        }
779
780        //
781        // Fallible test cases
782        //
783
784        // invalid number of arguments
785        let arg_field = Field::new("a", DataType::Int32, true).into();
786        let args = datafusion_expr::ScalarFunctionArgs {
787            args: vec![ColumnarValue::Scalar(ScalarValue::Int32(Some(1)))],
788            arg_fields: vec![arg_field],
789            number_rows: 1,
790            return_field: Field::new("f", DataType::Utf8, true).into(),
791            config_options: Arc::new(ConfigOptions::default()),
792        };
793        let result = ToCharFunc::new().invoke_with_args(args);
794        assert_eq!(
795            result.err().unwrap().strip_backtrace(),
796            "Execution error: to_char function requires 2 arguments, got 1"
797        );
798
799        // invalid type
800        let arg_fields = vec![
801            Field::new("a", DataType::Utf8, true).into(),
802            Field::new("a", DataType::Timestamp(TimeUnit::Nanosecond, None), true).into(),
803        ];
804        let args = datafusion_expr::ScalarFunctionArgs {
805            args: vec![
806                ColumnarValue::Scalar(ScalarValue::Int32(Some(1))),
807                ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(Some(1), None)),
808            ],
809            arg_fields,
810            number_rows: 1,
811            return_field: Field::new("f", DataType::Utf8, true).into(),
812            config_options: Arc::new(ConfigOptions::default()),
813        };
814        let result = ToCharFunc::new().invoke_with_args(args);
815        assert_eq!(
816            result.err().unwrap().strip_backtrace(),
817            "Execution error: Format for `to_char` must be non-null Utf8, received Timestamp(Nanosecond, None)"
818        );
819    }
820}
datafusion_functions/datetime/to_char.rs

datafusion_functions/datetime/
to_char.rs
