Skip to content

Commit 196b718

Browse files
Omega359alamb
andauthored
Add to_char function implementation using chrono formats (#9181)
* initial to_char impl #9147 * fleshed out to_char impl, added tests and docs. * adding sqllogictests * adding time support, improved tests in datatime_expressions.rs, fixed scalar doc example * Doc fix. * Prettier * Update datafusion/physical-expr/src/datetime_expressions.rs Co-authored-by: Andrew Lamb <[email protected]> * Updating the chrono dependency since Chrono 0.4.34 changed Display for the Duration type (which is now an alias for TimeDelta) and this broke the timestamp tests. * Updates and fixes based on PR feedback. * Add escape to rustdoc. * Fixed example * Handled null formats better - now any null formats will result in a default format being used. --------- Co-authored-by: Andrew Lamb <[email protected]>
1 parent 60fbca9 commit 196b718

19 files changed

Lines changed: 1078 additions & 8 deletions

File tree

‎Cargo.toml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ arrow-string = { version = "50.0.0", default-features = false }
4343
async-trait = "0.1.73"
4444
bigdecimal = "0.4.1"
4545
bytes = "1.4"
46-
chrono = { version = "0.4.31", default-features = false }
46+
chrono = { version = "0.4.34", default-features = false }
4747
ctor = "0.2.0"
4848
dashmap = "5.4.0"
4949
datafusion = { path = "datafusion/core", version = "35.0.0" }

‎datafusion-cli/Cargo.lock‎

Lines changed: 2 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎datafusion-examples/README.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@ cargo run --example csv_sql
6363
- [`query-http-csv.rs`](examples/query-http-csv.rs): Configure `object_store` and run a query against files vi HTTP
6464
- [`regexp.rs`](examples/regexp.rs): Examples of using regular expression functions
6565
- [`rewrite_expr.rs`](examples/rewrite_expr.rs): Define and invoke a custom Query Optimizer pass
66+
- [`to_char.rs`](examples/to_char.rs): Examples of using the to_char function
6667
- [`to_timestamp.rs`](examples/to_timestamp.rs): Examples of using to_timestamp functions
6768
- [`simple_udf.rs`](examples/simple_udf.rs): Define and invoke a User Defined Scalar Function (UDF)
6869
- [`advanced_udf.rs`](examples/advanced_udf.rs): Define and invoke a more complicated User Defined Scalar Function (UDF)
Lines changed: 197 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,197 @@
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+
18+
use arrow::array::Date32Array;
19+
use datafusion::arrow::array::StringArray;
20+
use datafusion::arrow::datatypes::{DataType, Field, Schema};
21+
use datafusion::arrow::record_batch::RecordBatch;
22+
use datafusion::assert_batches_eq;
23+
use datafusion::error::Result;
24+
use datafusion::prelude::*;
25+
use std::sync::Arc;
26+
27+
/// This example demonstrates how to use the to_char function via sql
28+
///
29+
/// This function accepts date, time, timestamp and duration values
30+
/// in the first argument and string values for the second
31+
#[tokio::main]
32+
async fn main() -> Result<()> {
33+
let schema = Arc::new(Schema::new(vec![
34+
Field::new("values", DataType::Date32, false),
35+
Field::new("patterns", DataType::Utf8, false),
36+
]));
37+
38+
let batch = RecordBatch::try_new(
39+
schema,
40+
vec![
41+
Arc::new(Date32Array::from(vec![18506, 18507, 18508, 18509])),
42+
Arc::new(StringArray::from(vec![
43+
"%Y-%m-%d", "%Y:%m:%d", "%Y%m%d", "%d-%m-%Y",
44+
])),
45+
],
46+
)?;
47+
48+
// declare a new context. In spark API, this corresponds to a new spark SQLsession
49+
let ctx = SessionContext::new();
50+
51+
// declare a table in memory. In spark API, this corresponds to createDataFrame(...).
52+
ctx.register_batch("t", batch)?;
53+
let _ = ctx.table("t").await?;
54+
55+
// use to_char function to convert col 'values' to timestamp type using
56+
// patterns stored in col 'patterns'
57+
let result = ctx
58+
.sql("SELECT to_char(values, patterns) from t")
59+
.await?
60+
.collect()
61+
.await?;
62+
63+
assert_batches_eq!(
64+
&[
65+
"+------------------------------+",
66+
"| to_char(t.values,t.patterns) |",
67+
"+------------------------------+",
68+
"| 2020-09-01 |",
69+
"| 2020:09:02 |",
70+
"| 20200903 |",
71+
"| 04-09-2020 |",
72+
"+------------------------------+",
73+
],
74+
&result
75+
);
76+
77+
// the date_format alias for the to_char function can be used as well
78+
let result = ctx
79+
.sql("SELECT date_format(values, patterns) from t")
80+
.await?
81+
.collect()
82+
.await?;
83+
84+
assert_batches_eq!(
85+
&[
86+
"+------------------------------+",
87+
"| to_char(t.values,t.patterns) |",
88+
"+------------------------------+",
89+
"| 2020-09-01 |",
90+
"| 2020:09:02 |",
91+
"| 20200903 |",
92+
"| 04-09-2020 |",
93+
"+------------------------------+",
94+
],
95+
&result
96+
);
97+
98+
// use to_char function to convert col 'values' with a fixed format
99+
let result = ctx
100+
.sql("SELECT to_char(values, '%m-%d-%Y') FROM t")
101+
.await?
102+
.collect()
103+
.await?;
104+
105+
assert_batches_eq!(
106+
&[
107+
"+------------------------------------+",
108+
"| to_char(t.values,Utf8(\"%m-%d-%Y\")) |",
109+
"+------------------------------------+",
110+
"| 09-01-2020 |",
111+
"| 09-02-2020 |",
112+
"| 09-03-2020 |",
113+
"| 09-04-2020 |",
114+
"+------------------------------------+",
115+
],
116+
&result
117+
);
118+
119+
// if you want to just use the default format cast to a string
120+
let result = ctx
121+
.sql("SELECT arrow_cast(values, 'Utf8') from t")
122+
.await?
123+
.collect()
124+
.await?;
125+
126+
assert_batches_eq!(
127+
&[
128+
"+------------+",
129+
"| t.values |",
130+
"+------------+",
131+
"| 2020-09-01 |",
132+
"| 2020-09-02 |",
133+
"| 2020-09-03 |",
134+
"| 2020-09-04 |",
135+
"+------------+",
136+
],
137+
&result
138+
);
139+
140+
// use can use literals as well (note the use of timestamp here)
141+
let result = ctx
142+
.sql("SELECT to_char(arrow_cast(TIMESTAMP '2023-08-03 14:38:50Z', 'Timestamp(Second, None)'), '%d-%m-%Y %H:%M:%S')")
143+
.await?
144+
.collect()
145+
.await?;
146+
147+
assert_batches_eq!(
148+
&[
149+
"+-----------------------------------------------------------------+",
150+
"| to_char(Utf8(\"2023-08-03 14:38:50Z\"),Utf8(\"%d-%m-%Y %H:%M:%S\")) |",
151+
"+-----------------------------------------------------------------+",
152+
"| 03-08-2023 14:38:50 |",
153+
"+-----------------------------------------------------------------+",
154+
],
155+
&result
156+
);
157+
158+
// durations are supported though the output format is limited to two formats
159+
// 'pretty' and 'ISO8601'
160+
let result = ctx
161+
.sql("SELECT to_char(arrow_cast(123456, 'Duration(Second)'), 'pretty')")
162+
.await?
163+
.collect()
164+
.await?;
165+
166+
assert_batches_eq!(
167+
&[
168+
"+---------------------------------------+",
169+
"| to_char(Int64(123456),Utf8(\"pretty\")) |",
170+
"+---------------------------------------+",
171+
"| 1 days 10 hours 17 mins 36 secs |",
172+
"+---------------------------------------+",
173+
],
174+
&result
175+
);
176+
177+
// durations are supported though the output format is limited to two formats
178+
// 'pretty' and 'ISO8601'
179+
let result = ctx
180+
.sql("SELECT to_char(arrow_cast(123456, 'Duration(Second)'), 'iso8601')")
181+
.await?
182+
.collect()
183+
.await?;
184+
185+
assert_batches_eq!(
186+
&[
187+
"+----------------------------------------+",
188+
"| to_char(Int64(123456),Utf8(\"iso8601\")) |",
189+
"+----------------------------------------+",
190+
"| PT123456S |",
191+
"+----------------------------------------+",
192+
],
193+
&result
194+
);
195+
196+
Ok(())
197+
}

‎datafusion/execution/Cargo.toml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ path = "src/lib.rs"
3434

3535
[dependencies]
3636
arrow = { workspace = true }
37-
chrono = { version = "0.4", default-features = false }
37+
chrono = { workspace = true }
3838
dashmap = { workspace = true }
3939
datafusion-common = { workspace = true }
4040
datafusion-expr = { workspace = true }

‎datafusion/expr/src/built_in_function.rs‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -315,6 +315,8 @@ pub enum BuiltinScalarFunction {
315315
SubstrIndex,
316316
/// find_in_set
317317
FindInSet,
318+
/// to_char
319+
ToChar,
318320
}
319321

320322
/// Maps the sql function name to `BuiltinScalarFunction`
@@ -479,6 +481,7 @@ impl BuiltinScalarFunction {
479481
BuiltinScalarFunction::Strpos => Volatility::Immutable,
480482
BuiltinScalarFunction::Substr => Volatility::Immutable,
481483
BuiltinScalarFunction::ToHex => Volatility::Immutable,
484+
BuiltinScalarFunction::ToChar => Volatility::Immutable,
482485
BuiltinScalarFunction::ToTimestamp => Volatility::Immutable,
483486
BuiltinScalarFunction::ToTimestampMillis => Volatility::Immutable,
484487
BuiltinScalarFunction::ToTimestampMicros => Volatility::Immutable,
@@ -799,6 +802,7 @@ impl BuiltinScalarFunction {
799802
BuiltinScalarFunction::FindInSet => {
800803
utf8_to_int_type(&input_expr_types[0], "find_in_set")
801804
}
805+
BuiltinScalarFunction::ToChar => Ok(Utf8),
802806
BuiltinScalarFunction::ToTimestamp
803807
| BuiltinScalarFunction::ToTimestampNanos => Ok(Timestamp(Nanosecond, None)),
804808
BuiltinScalarFunction::ToTimestampMillis => Ok(Timestamp(Millisecond, None)),
@@ -1059,6 +1063,41 @@ impl BuiltinScalarFunction {
10591063
vec![Exact(vec![Utf8, Int64]), Exact(vec![LargeUtf8, Int64])],
10601064
self.volatility(),
10611065
),
1066+
BuiltinScalarFunction::ToChar => Signature::one_of(
1067+
vec![
1068+
Exact(vec![Date32, Utf8]),
1069+
Exact(vec![Date64, Utf8]),
1070+
Exact(vec![Time32(Millisecond), Utf8]),
1071+
Exact(vec![Time32(Second), Utf8]),
1072+
Exact(vec![Time64(Microsecond), Utf8]),
1073+
Exact(vec![Time64(Nanosecond), Utf8]),
1074+
Exact(vec![Timestamp(Second, None), Utf8]),
1075+
Exact(vec![
1076+
Timestamp(Second, Some(TIMEZONE_WILDCARD.into())),
1077+
Utf8,
1078+
]),
1079+
Exact(vec![Timestamp(Millisecond, None), Utf8]),
1080+
Exact(vec![
1081+
Timestamp(Millisecond, Some(TIMEZONE_WILDCARD.into())),
1082+
Utf8,
1083+
]),
1084+
Exact(vec![Timestamp(Microsecond, None), Utf8]),
1085+
Exact(vec![
1086+
Timestamp(Microsecond, Some(TIMEZONE_WILDCARD.into())),
1087+
Utf8,
1088+
]),
1089+
Exact(vec![Timestamp(Nanosecond, None), Utf8]),
1090+
Exact(vec![
1091+
Timestamp(Nanosecond, Some(TIMEZONE_WILDCARD.into())),
1092+
Utf8,
1093+
]),
1094+
Exact(vec![Duration(Second), Utf8]),
1095+
Exact(vec![Duration(Millisecond), Utf8]),
1096+
Exact(vec![Duration(Microsecond), Utf8]),
1097+
Exact(vec![Duration(Nanosecond), Utf8]),
1098+
],
1099+
self.volatility(),
1100+
),
10621101
BuiltinScalarFunction::ToTimestamp
10631102
| BuiltinScalarFunction::ToTimestampSeconds
10641103
| BuiltinScalarFunction::ToTimestampMillis
@@ -1517,6 +1556,7 @@ impl BuiltinScalarFunction {
15171556
BuiltinScalarFunction::DateBin => &["date_bin"],
15181557
BuiltinScalarFunction::DateTrunc => &["date_trunc", "datetrunc"],
15191558
BuiltinScalarFunction::DatePart => &["date_part", "datepart"],
1559+
BuiltinScalarFunction::ToChar => &["to_char", "date_format"],
15201560
BuiltinScalarFunction::ToTimestamp => &["to_timestamp"],
15211561
BuiltinScalarFunction::ToTimestampMillis => &["to_timestamp_millis"],
15221562
BuiltinScalarFunction::ToTimestampMicros => &["to_timestamp_micros"],

‎datafusion/expr/src/expr_fn.rs‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -890,6 +890,12 @@ nary_scalar_expr!(
890890
scalar_expr!(DatePart, date_part, part date, "extracts a subfield from the date");
891891
scalar_expr!(DateTrunc, date_trunc, part date, "truncates the date to a specified level of precision");
892892
scalar_expr!(DateBin, date_bin, stride source origin, "coerces an arbitrary timestamp to the start of the nearest specified interval");
893+
scalar_expr!(
894+
ToChar,
895+
to_char,
896+
datetime format,
897+
"converts a date, time, timestamp or duration to a string based on the provided format"
898+
);
893899
nary_scalar_expr!(
894900
ToTimestamp,
895901
to_timestamp,

‎datafusion/physical-expr/Cargo.toml‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,3 +94,7 @@ name = "to_timestamp"
9494
[[bench]]
9595
harness = false
9696
name = "regexp"
97+
98+
[[bench]]
99+
harness = false
100+
name = "to_char"

0 commit comments

Comments
 (0)