jonmmease opened a new issue, #13125:
URL: https://github.com/apache/datafusion/issues/13125
### Describe the bug
The `to_char` function for formatting time values as strings seems to drop
the milliseconds portion of the timestamp prior to formatting when the
timestamp has a timezone.
### To Reproduce
First, the working example for a timestamp without a timezone. Here the
milliseconds component (`.123`) is displayed as expected.
```
> select to_char('2020-01-01 00:10:20.123'::timestamp, '%Y-%m-%d
%H:%M:%S.%3f');
+------------------------------------------------------------------------+
| to_char(Utf8("2020-01-01 00:10:20.123"),Utf8("%Y-%m-%d %H:%M:%S.%3f")) |
+------------------------------------------------------------------------+
| 2020-01-01 00:10:20.123 |
+------------------------------------------------------------------------+
```
When a timezone is added, the milliseconds display as zeros.
```
> select to_char('2020-01-01 00:10:20.123'::timestamp at time zone
'America/New_York', '%Y-%m-%d %H:%M:%S.%3f');
+------------------------------------------------------------------------+
| to_char(Utf8("2020-01-01 00:10:20.123"),Utf8("%Y-%m-%d %H:%M:%S.%3f")) |
+------------------------------------------------------------------------+
| 2020-01-01 00:10:20.000 |
+------------------------------------------------------------------------+
```
### Expected behavior
I expect milliseconds to be represented in the format string when the
timestamp has a timezone.
### Additional context
After poking around in the debugger, I think this is an issue with signature
selection, and that the original value of type `Timestamp(Milliseconds,
Some("America/New_York"))` is getting coerced to `Timestamp(Seconds,
Some("America/New_York"))` prior to evaluating .
The signature for `to_char` represents timezones using the
[`TIMEZONE_WILDCARD`](https://github.com/apache/datafusion/blob/146f16a0c14f0fe65e2bd8b7226508f27ced3f13/datafusion/expr-common/src/signature.rs#L23-L31)
placeholder.
https://github.com/apache/datafusion/blob/62b063cd36653b92a9f0cd53a358231be8c3e848/datafusion/functions/src/datetime/to_char.rs#L53-L87
Because of this, the exact signature equality test here is never satisfied:
https://github.com/apache/datafusion/blob/146f16a0c14f0fe65e2bd8b7226508f27ced3f13/datafusion/expr/src/type_coercion/functions.rs#L61
because `Timestamp(Millisecond, Some("America/New_York") !=
Timestamp(Millisecond, Some(TIMEZONE_WILDCARD)`. And so instead we head down
the coercion path which (still surprisingly to me) decides to reduce the
precision from Milliseconds to Seconds.
Does it make sense to replace this signature equality test with a version
that considers `TIMEZONE_WILDCARD` to match any timezone?
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
