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

datafusion_functions/datetime/
to_char.rs
