Bug #110093 | STR_TO_DATE %p does not work | ||
---|---|---|---|
Submitted: | 16 Feb 2023 6:47 | Modified: | 17 Feb 2023 10:17 |
Reporter: | Derek Main | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S4 (Feature request) |
Version: | 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[16 Feb 2023 6:47]
Derek Main
[16 Feb 2023 7:25]
Derek Main
This works : SELECT STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %h.%i.%s.%f000 %p'); Returns: 2020-03-26 21:01:00.000000
[16 Feb 2023 14:18]
MySQL Verification Team
Hi Mr. Main, Thank you for your bug report. There are several functions that are supposed to support %p, but none is supporting it ....... Seems that it was never implemented. Thank you for your report. This report is now a verified feature request.
[16 Feb 2023 14:19]
MySQL Verification Team
.
[17 Feb 2023 10:13]
Norvald Ryeng
Posted by developer: Hi Derek, I'm closing this as not a bug. Let's take this query by query (I'm using 8.0, but 5.7 should be the same): mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f'); +------------------------------------------------------------------------+ | STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f') | +------------------------------------------------------------------------+ | 2020-03-26 09:01:00.000000 | +------------------------------------------------------------------------+ As the manual states, "Extra characters at the end of str are ignored" (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date). So parsing ends after microseconds, and the suffix "000 PM" is ignored. This is correct. Next query: mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f %p'); +---------------------------------------------------------------------------+ | STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f %p') | +---------------------------------------------------------------------------+ | NULL | +---------------------------------------------------------------------------+ In this case, the format is not matched. There format matches up to %f, but the remaining string "000 PM" does not match the rest of the pattern (" %p"). Even if we reduce the fractional part to microseconds, we still get NULL: mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %H.%i.%s.%f %p'); +------------------------------------------------------------------------+ | STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %H.%i.%s.%f %p') | +------------------------------------------------------------------------+ | NULL | +------------------------------------------------------------------------+ This is because the pattern contains two conflicting hour specifications. %H means hours in 24 hour format, while %p says if it is AM or PM. If we change the pattern to use a 12 hour format (%h), we get the result you expect: mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %h.%i.%s.%f %p'); +------------------------------------------------------------------------+ | STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %h.%i.%s.%f %p') | +------------------------------------------------------------------------+ | 2020-03-26 21:01:00.000000 | +------------------------------------------------------------------------+ Don't combine 24-hour hours (%H or %k) with AM/PM (%p). Instead, if you need AM/PM matching, use 12-hour hours (%h, %I or %l), or use %r to match the entire string of hours, minutes, seconds and AM/PM. Best regards, Norvald
[17 Feb 2023 10:17]
Derek Main
Thanks for the explanation Norvald. I had missed the limit of 6 for microseconds in the documentation. That was the root of my problem.
[17 Feb 2023 13:19]
MySQL Verification Team
Thank you, Norvald ........