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                lambdas: None,
379            };
380            let result = ToCharFunc::new()
381                .invoke_with_args(args)
382                .expect("that to_char parsed values without error");
383
384            if let ColumnarValue::Array(result) = result {
385                assert_eq!(result.len(), 2);
386                assert_eq!(&expected as &dyn Array, result.as_ref());
387            } else {
388                panic!("Expected an array value")
389            }
390        }
391    }
392
393    #[test]
394    fn test_to_char() {
395        let date = "2020-01-02T03:04:05"
396            .parse::<NaiveDateTime>()
397            .unwrap()
398            .with_nanosecond(12345)
399            .unwrap();
400        let date2 = "2026-07-08T09:10:11"
401            .parse::<NaiveDateTime>()
402            .unwrap()
403            .with_nanosecond(56789)
404            .unwrap();
405
406        let scalar_data = vec![
407            (
408                ScalarValue::Date32(Some(18506)),
409                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
410                "2020::09::01".to_string(),
411            ),
412            (
413                ScalarValue::Date32(Some(18506)),
414                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
415                "2020::09::01 00::00::00 000000000".to_string(),
416            ),
417            (
418                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
419                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
420                "2020::01::02".to_string(),
421            ),
422            (
423                ScalarValue::Time32Second(Some(31851)),
424                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
425                "08-50-51".to_string(),
426            ),
427            (
428                ScalarValue::Time32Millisecond(Some(18506000)),
429                ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
430                "05-08-26".to_string(),
431            ),
432            (
433                ScalarValue::Time64Microsecond(Some(12344567000)),
434                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
435                "03-25-44 567000000".to_string(),
436            ),
437            (
438                ScalarValue::Time64Nanosecond(Some(12344567890000)),
439                ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
440                "03-25-44 567890000".to_string(),
441            ),
442            (
443                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
444                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
445                "2020::01::02 05::04::03".to_string(),
446            ),
447            (
448                ScalarValue::TimestampMillisecond(
449                    Some(date.and_utc().timestamp_millis()),
450                    None,
451                ),
452                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
453                "2020::01::02 05::04::03".to_string(),
454            ),
455            (
456                ScalarValue::TimestampMicrosecond(
457                    Some(date.and_utc().timestamp_micros()),
458                    None,
459                ),
460                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
461                "2020::01::02 05::04::03 000012000".to_string(),
462            ),
463            (
464                ScalarValue::TimestampNanosecond(
465                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
466                    None,
467                ),
468                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
469                "2020::01::02 05::04::03 000012345".to_string(),
470            ),
471        ];
472
473        for (value, format, expected) in scalar_data {
474            let arg_fields = vec![
475                Field::new("a", value.data_type(), false).into(),
476                Field::new("a", format.data_type(), false).into(),
477            ];
478            let args = datafusion_expr::ScalarFunctionArgs {
479                args: vec![ColumnarValue::Scalar(value), ColumnarValue::Scalar(format)],
480                arg_fields,
481                number_rows: 1,
482                return_field: Field::new("f", DataType::Utf8, true).into(),
483                config_options: Arc::new(ConfigOptions::default()),
484                lambdas: None,
485            };
486            let result = ToCharFunc::new()
487                .invoke_with_args(args)
488                .expect("that to_char parsed values without error");
489
490            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
491                assert_eq!(expected, date.unwrap());
492            } else {
493                panic!("Expected a scalar value")
494            }
495        }
496
497        let scalar_array_data = vec![
498            (
499                ScalarValue::Date32(Some(18506)),
500                StringArray::from(vec!["%Y::%m::%d".to_string()]),
501                "2020::09::01".to_string(),
502            ),
503            (
504                ScalarValue::Date32(Some(18506)),
505                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
506                "2020::09::01 00::00::00 000000000".to_string(),
507            ),
508            (
509                ScalarValue::Date64(Some(date.and_utc().timestamp_millis())),
510                StringArray::from(vec!["%Y::%m::%d".to_string()]),
511                "2020::01::02".to_string(),
512            ),
513            (
514                ScalarValue::Time32Second(Some(31851)),
515                StringArray::from(vec!["%H-%M-%S".to_string()]),
516                "08-50-51".to_string(),
517            ),
518            (
519                ScalarValue::Time32Millisecond(Some(18506000)),
520                StringArray::from(vec!["%H-%M-%S".to_string()]),
521                "05-08-26".to_string(),
522            ),
523            (
524                ScalarValue::Time64Microsecond(Some(12344567000)),
525                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
526                "03-25-44 567000000".to_string(),
527            ),
528            (
529                ScalarValue::Time64Nanosecond(Some(12344567890000)),
530                StringArray::from(vec!["%H-%M-%S %f".to_string()]),
531                "03-25-44 567890000".to_string(),
532            ),
533            (
534                ScalarValue::TimestampSecond(Some(date.and_utc().timestamp()), None),
535                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
536                "2020::01::02 05::04::03".to_string(),
537            ),
538            (
539                ScalarValue::TimestampMillisecond(
540                    Some(date.and_utc().timestamp_millis()),
541                    None,
542                ),
543                StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
544                "2020::01::02 05::04::03".to_string(),
545            ),
546            (
547                ScalarValue::TimestampMicrosecond(
548                    Some(date.and_utc().timestamp_micros()),
549                    None,
550                ),
551                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
552                "2020::01::02 05::04::03 000012000".to_string(),
553            ),
554            (
555                ScalarValue::TimestampNanosecond(
556                    Some(date.and_utc().timestamp_nanos_opt().unwrap()),
557                    None,
558                ),
559                StringArray::from(vec!["%Y::%m::%d %S::%M::%H %f".to_string()]),
560                "2020::01::02 05::04::03 000012345".to_string(),
561            ),
562        ];
563
564        for (value, format, expected) in scalar_array_data {
565            let batch_len = format.len();
566            let arg_fields = vec![
567                Field::new("a", value.data_type(), false).into(),
568                Field::new("a", format.data_type().to_owned(), false).into(),
569            ];
570            let args = datafusion_expr::ScalarFunctionArgs {
571                args: vec![
572                    ColumnarValue::Scalar(value),
573                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
574                ],
575                arg_fields,
576                number_rows: batch_len,
577                return_field: Field::new("f", DataType::Utf8, true).into(),
578                config_options: Arc::new(ConfigOptions::default()),
579                lambdas: None,
580            };
581            let result = ToCharFunc::new()
582                .invoke_with_args(args)
583                .expect("that to_char parsed values without error");
584
585            if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
586                assert_eq!(expected, date.unwrap());
587            } else {
588                panic!("Expected a scalar value")
589            }
590        }
591
592        let array_scalar_data = vec![
593            (
594                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
595                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
596                StringArray::from(vec!["2020::09::01", "2020::09::02"]),
597            ),
598            (
599                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
600                ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H %f".to_string())),
601                StringArray::from(vec![
602                    "2020::09::01 00::00::00 000000000",
603                    "2020::09::02 00::00::00 000000000",
604                ]),
605            ),
606            (
607                Arc::new(Date64Array::from(vec![
608                    date.and_utc().timestamp_millis(),
609                    date2.and_utc().timestamp_millis(),
610                ])) as ArrayRef,
611                ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
612                StringArray::from(vec!["2020::01::02", "2026::07::08"]),
613            ),
614        ];
615
616        let array_array_data = vec![
617            (
618                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
619                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
620                StringArray::from(vec!["2020::09::01", "02::09::2020"]),
621            ),
622            (
623                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
624                StringArray::from(vec![
625                    "%Y::%m::%d %S::%M::%H %f",
626                    "%Y::%m::%d %S::%M::%H %f",
627                ]),
628                StringArray::from(vec![
629                    "2020::09::01 00::00::00 000000000",
630                    "2020::09::02 00::00::00 000000000",
631                ]),
632            ),
633            (
634                Arc::new(Date32Array::from(vec![18506, 18507])) as ArrayRef,
635                StringArray::from(vec!["%Y::%m::%d", "%Y::%m::%d %S::%M::%H %f"]),
636                StringArray::from(vec![
637                    "2020::09::01",
638                    "2020::09::02 00::00::00 000000000",
639                ]),
640            ),
641            (
642                Arc::new(Date64Array::from(vec![
643                    date.and_utc().timestamp_millis(),
644                    date2.and_utc().timestamp_millis(),
645                ])) as ArrayRef,
646                StringArray::from(vec!["%Y::%m::%d", "%d::%m::%Y"]),
647                StringArray::from(vec!["2020::01::02", "08::07::2026"]),
648            ),
649            (
650                Arc::new(Time32MillisecondArray::from(vec![1850600, 1860700]))
651                    as ArrayRef,
652                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
653                StringArray::from(vec!["00:30:50", "00::31::00"]),
654            ),
655            (
656                Arc::new(Time32SecondArray::from(vec![18506, 18507])) as ArrayRef,
657                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
658                StringArray::from(vec!["05:08:26", "05::08::27"]),
659            ),
660            (
661                Arc::new(Time64MicrosecondArray::from(vec![12344567000, 22244567000]))
662                    as ArrayRef,
663                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
664                StringArray::from(vec!["03:25:44", "06::10::44"]),
665            ),
666            (
667                Arc::new(Time64NanosecondArray::from(vec![
668                    1234456789000,
669                    2224456789000,
670                ])) as ArrayRef,
671                StringArray::from(vec!["%H:%M:%S", "%H::%M::%S"]),
672                StringArray::from(vec!["00:20:34", "00::37::04"]),
673            ),
674            (
675                Arc::new(TimestampSecondArray::from(vec![
676                    date.and_utc().timestamp(),
677                    date2.and_utc().timestamp(),
678                ])) as ArrayRef,
679                StringArray::from(vec!["%Y::%m::%d %S::%M::%H", "%d::%m::%Y %S-%M-%H"]),
680                StringArray::from(vec![
681                    "2020::01::02 05::04::03",
682                    "08::07::2026 11-10-09",
683                ]),
684            ),
685            (
686                Arc::new(TimestampMillisecondArray::from(vec![
687                    date.and_utc().timestamp_millis(),
688                    date2.and_utc().timestamp_millis(),
689                ])) as ArrayRef,
690                StringArray::from(vec![
691                    "%Y::%m::%d %S::%M::%H %f",
692                    "%d::%m::%Y %S-%M-%H %f",
693                ]),
694                StringArray::from(vec![
695                    "2020::01::02 05::04::03 000000000",
696                    "08::07::2026 11-10-09 000000000",
697                ]),
698            ),
699            (
700                Arc::new(TimestampMicrosecondArray::from(vec![
701                    date.and_utc().timestamp_micros(),
702                    date2.and_utc().timestamp_micros(),
703                ])) as ArrayRef,
704                StringArray::from(vec![
705                    "%Y::%m::%d %S::%M::%H %f",
706                    "%d::%m::%Y %S-%M-%H %f",
707                ]),
708                StringArray::from(vec![
709                    "2020::01::02 05::04::03 000012000",
710                    "08::07::2026 11-10-09 000056000",
711                ]),
712            ),
713            (
714                Arc::new(TimestampNanosecondArray::from(vec![
715                    date.and_utc().timestamp_nanos_opt().unwrap(),
716                    date2.and_utc().timestamp_nanos_opt().unwrap(),
717                ])) as ArrayRef,
718                StringArray::from(vec![
719                    "%Y::%m::%d %S::%M::%H %f",
720                    "%d::%m::%Y %S-%M-%H %f",
721                ]),
722                StringArray::from(vec![
723                    "2020::01::02 05::04::03 000012345",
724                    "08::07::2026 11-10-09 000056789",
725                ]),
726            ),
727        ];
728
729        for (value, format, expected) in array_scalar_data {
730            let batch_len = value.len();
731            let arg_fields = vec![
732                Field::new("a", value.data_type().clone(), false).into(),
733                Field::new("a", format.data_type(), false).into(),
734            ];
735            let args = datafusion_expr::ScalarFunctionArgs {
736                args: vec![
737                    ColumnarValue::Array(value as ArrayRef),
738                    ColumnarValue::Scalar(format),
739                ],
740                arg_fields,
741                number_rows: batch_len,
742                return_field: Field::new("f", DataType::Utf8, true).into(),
743                config_options: Arc::new(ConfigOptions::default()),
744                lambdas: None,
745            };
746            let result = ToCharFunc::new()
747                .invoke_with_args(args)
748                .expect("that to_char parsed values without error");
749
750            if let ColumnarValue::Array(result) = result {
751                assert_eq!(result.len(), 2);
752                assert_eq!(&expected as &dyn Array, result.as_ref());
753            } else {
754                panic!("Expected an array value")
755            }
756        }
757
758        for (value, format, expected) in array_array_data {
759            let batch_len = value.len();
760            let arg_fields = vec![
761                Field::new("a", value.data_type().clone(), false).into(),
762                Field::new("a", format.data_type().clone(), false).into(),
763            ];
764            let args = datafusion_expr::ScalarFunctionArgs {
765                args: vec![
766                    ColumnarValue::Array(value),
767                    ColumnarValue::Array(Arc::new(format) as ArrayRef),
768                ],
769                arg_fields,
770                number_rows: batch_len,
771                return_field: Field::new("f", DataType::Utf8, true).into(),
772                config_options: Arc::new(ConfigOptions::default()),
773                lambdas: None,
774            };
775            let result = ToCharFunc::new()
776                .invoke_with_args(args)
777                .expect("that to_char parsed values without error");
778
779            if let ColumnarValue::Array(result) = result {
780                assert_eq!(result.len(), 2);
781                assert_eq!(&expected as &dyn Array, result.as_ref());
782            } else {
783                panic!("Expected an array value")
784            }
785        }
786
787        //
788        // Fallible test cases
789        //
790
791        // invalid number of arguments
792        let arg_field = Field::new("a", DataType::Int32, true).into();
793        let args = datafusion_expr::ScalarFunctionArgs {
794            args: vec![ColumnarValue::Scalar(ScalarValue::Int32(Some(1)))],
795            arg_fields: vec![arg_field],
796            number_rows: 1,
797            return_field: Field::new("f", DataType::Utf8, true).into(),
798            config_options: Arc::new(ConfigOptions::default()),
799            lambdas: None,
800        };
801        let result = ToCharFunc::new().invoke_with_args(args);
802        assert_eq!(
803            result.err().unwrap().strip_backtrace(),
804            "Execution error: to_char function requires 2 arguments, got 1"
805        );
806
807        // invalid type
808        let arg_fields = vec![
809            Field::new("a", DataType::Utf8, true).into(),
810            Field::new("a", DataType::Timestamp(TimeUnit::Nanosecond, None), true).into(),
811        ];
812        let args = datafusion_expr::ScalarFunctionArgs {
813            args: vec![
814                ColumnarValue::Scalar(ScalarValue::Int32(Some(1))),
815                ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(Some(1), None)),
816            ],
817            arg_fields,
818            number_rows: 1,
819            return_field: Field::new("f", DataType::Utf8, true).into(),
820            config_options: Arc::new(ConfigOptions::default()),
821            lambdas: None,
822        };
823        let result = ToCharFunc::new().invoke_with_args(args);
824        assert_eq!(
825            result.err().unwrap().strip_backtrace(),
826            "Execution error: Format for `to_char` must be non-null Utf8, received Timestamp(Nanosecond, None)"
827        );
828    }
829}
datafusion_functions/datetime/to_char.rs

datafusion_functions/datetime/
to_char.rs
