alamb commented on code in PR #9181:
URL: https://github.com/apache/arrow-datafusion/pull/9181#discussion_r1486814322
##########
datafusion/physical-expr/src/datetime_expressions.rs:
##########
@@ -2820,4 +3021,317 @@ mod tests {
"Arrow error: Cast error: Can't cast value 4294967295 to type
Int32"
);
}
+
+ #[test]
+ fn test_to_char() {
+ let date = "2020-01-02T03:04:05"
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .with_nanosecond(12345)
+ .unwrap();
+ let date2 = "2026-07-08T09:10:11"
+ .parse::<NaiveDateTime>()
+ .unwrap()
+ .with_nanosecond(56789)
+ .unwrap();
+
+ let scalar_data = vec![
+ (
+ ScalarValue::Date32(Some(18506)),
+ ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
+ "2020::09::01".to_string(),
+ ),
+ (
+ ScalarValue::Date64(Some(date.timestamp_millis())),
+ ScalarValue::Utf8(Some("%Y::%m::%d".to_string())),
+ "2020::01::02".to_string(),
+ ),
+ (
+ ScalarValue::Time32Second(Some(31851)),
+ ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
+ "08-50-51".to_string(),
+ ),
+ (
+ ScalarValue::Time32Millisecond(Some(18506000)),
+ ScalarValue::Utf8(Some("%H-%M-%S".to_string())),
+ "05-08-26".to_string(),
+ ),
+ (
+ ScalarValue::Time64Microsecond(Some(12344567000)),
+ ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
+ "03-25-44 567000000".to_string(),
+ ),
+ (
+ ScalarValue::Time64Nanosecond(Some(12344567890000)),
+ ScalarValue::Utf8(Some("%H-%M-%S %f".to_string())),
+ "03-25-44 567890000".to_string(),
+ ),
+ (
+ ScalarValue::TimestampSecond(Some(date.timestamp()), None),
+ ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
+ "2020::01::02 05::04::03".to_string(),
+ ),
+ (
+
ScalarValue::TimestampMillisecond(Some(date.timestamp_millis()), None),
+ ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H".to_string())),
+ "2020::01::02 05::04::03".to_string(),
+ ),
+ (
+
ScalarValue::TimestampMicrosecond(Some(date.timestamp_micros()), None),
+ ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H
%f".to_string())),
+ "2020::01::02 05::04::03 000012000".to_string(),
+ ),
+ (
+ ScalarValue::TimestampNanosecond(
+ Some(date.timestamp_nanos_opt().unwrap()),
+ None,
+ ),
+ ScalarValue::Utf8(Some("%Y::%m::%d %S::%M::%H
%f".to_string())),
+ "2020::01::02 05::04::03 000012345".to_string(),
+ ),
+ ];
+
+ for (value, format, expected) in scalar_data {
+ let result =
+ to_char(&[ColumnarValue::Scalar(value),
ColumnarValue::Scalar(format)])
+ .expect("that to_char parsed values without error");
+
+ if let ColumnarValue::Scalar(ScalarValue::Utf8(date)) = result {
+ assert_eq!(expected, date.unwrap());
+ } else {
+ panic!("Expected a scalar value")
+ }
+ }
+
+ let scalar_array_data = vec![
+ (
+ ScalarValue::Date32(Some(18506)),
+ StringArray::from(vec!["%Y::%m::%d".to_string()]),
+ "2020::09::01".to_string(),
+ ),
+ (
+ ScalarValue::Date64(Some(date.timestamp_millis())),
+ StringArray::from(vec!["%Y::%m::%d".to_string()]),
+ "2020::01::02".to_string(),
+ ),
+ (
+ ScalarValue::Time32Second(Some(31851)),
+ StringArray::from(vec!["%H-%M-%S".to_string()]),
+ "08-50-51".to_string(),
+ ),
+ (
+ ScalarValue::Time32Millisecond(Some(18506000)),
+ StringArray::from(vec!["%H-%M-%S".to_string()]),
+ "05-08-26".to_string(),
+ ),
+ (
+ ScalarValue::Time64Microsecond(Some(12344567000)),
+ StringArray::from(vec!["%H-%M-%S %f".to_string()]),
+ "03-25-44 567000000".to_string(),
+ ),
+ (
+ ScalarValue::Time64Nanosecond(Some(12344567890000)),
+ StringArray::from(vec!["%H-%M-%S %f".to_string()]),
+ "03-25-44 567890000".to_string(),
+ ),
+ (
+ ScalarValue::TimestampSecond(Some(date.timestamp()), None),
+ StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
+ "2020::01::02 05::04::03".to_string(),
+ ),
+ (
+
ScalarValue::TimestampMillisecond(Some(date.timestamp_millis()), None),
+ StringArray::from(vec!["%Y::%m::%d %S::%M::%H".to_string()]),
+ "2020::01::02 05::04::03".to_string(),
+ ),
+ (
+
ScalarValue::TimestampMicrosecond(Some(date.timestamp_micros()), None),
+ StringArray::from(vec!["%Y::%m::%d %S::%M::%H
%f".to_string()]),
+ "2020::01::02 05::04::03 000012000".to_string(),
+ ),
+ (
+ ScalarValue::TimestampNanosecond(
+ Some(date.timestamp_nanos_opt().unwrap()),
+ None,
+ ),
+ StringArray::from(vec!["%Y::%m::%d %S::%M::%H
%f".to_string()]),
Review Comment:
👍
##########
datafusion/sqllogictest/test_files/timestamps.slt:
##########
@@ -2565,3 +2565,113 @@ select make_date(2024, 1, null);
query error DataFusion error: Arrow error: Cast error: Cannot cast string ''
to value of Int32 type
select make_date(2024, 1, '');
+
+
+##########
+## to_char tests
+##########
+
+statement ok
+create table formats (
+ dates date,
+ times time,
+ timestamps timestamp,
+ date_format varchar,
+ time_format varchar,
+ timestamp_format varchar)
+as values
+ ('2000-01-01'::date, '23:45:01'::time, '2024-01-01 06:00:00'::timestamp,
'%d:%m:%Y', '%H-%M-%S', '%d:%m:%Y %H-%M-%S'),
+ ('2003-04-05'::date, '04:56:32'::time, '2025-01-01 23:59:58'::timestamp,
'%d:%m:%Y', '%H::%M::%S', '%d:%m:%Y %H-%M-%S');
+
+
+query T
+select to_char(dates, date_format) from formats;
+----
+01:01:2000
+05:04:2003
+
+query T
+select date_format(dates, date_format) from formats;
+----
+01:01:2000
+05:04:2003
+
+query T
+select to_char(times, time_format) from formats;
+----
+23-45-01
+04::56::32
+
+query T
+select to_char(timestamps, date_format) from formats;
+----
+01:01:2024
+01:01:2025
+
+query T
+select to_char(timestamps, timestamp_format) from formats;
+----
+01:01:2024 06-00-00
+01:01:2025 23-59-58
+
+query T
+select to_char('2000-02-03'::date, '%Y:%d:%m');
+----
+2000:03:02
+
+query T
+select to_char(arrow_cast(12345::int, 'Time32(Second)'), '%H-%M-%S')
+----
+03-25-45
+
+query T
+select to_char(arrow_cast(12344567::int, 'Time32(Millisecond)'), '%H-%M-%S %f')
+----
+03-25-44 567000000
+
+query T
+select to_char(arrow_cast(12344567000, 'Time64(Microsecond)'), '%H-%M-%S %f')
+----
+03-25-44 567000000
+
+query T
+select to_char(arrow_cast(12344567890000, 'Time64(Nanosecond)'), '%H-%M-%S %f')
+----
+03-25-44 567890000
+
+query T
+select to_char(arrow_cast(TIMESTAMP '2023-08-03 14:38:50Z', 'Timestamp(Second,
None)'), '%d-%m-%Y %H-%M-%S')
+----
+03-08-2023 14-38-50
+
+query T
+select to_char(arrow_cast(123456, 'Duration(Second)'), 'pretty');
+----
+1 days 10 hours 17 mins 36 secs
+
+query T
+select to_char(arrow_cast(123456, 'Duration(Second)'), 'iso8601');
+----
+PT123456S
+
+query T
+select to_char(arrow_cast(123456, 'Duration(Second)'), null);
+----
+NULL
+
+query error DataFusion error: Execution error: Cast error: Format error
+SELECT to_char(timestamps, '%X%K') from formats;
+
+query error DataFusion error: Execution error: Cast error: Format error
+SELECT to_char('2000-02-03'::date, '%X%K');
+
+query error DataFusion error: Arrow error: Invalid argument error: column
types must match schema types, expected Utf8 but found Null at column index 0
Review Comment:
this is probably something we could fix in a follow on PR, as a usability
papercut. Might be a good first project for someone 🤔
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
