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

datafusion_functions/datetime/
to_char.rs
