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

datafusion_functions/datetime/
to_char.rs
