STR_TO_DATE can't work with generated TIME formats and values needs to be entered manually, onl-CodePudding

Here I have date1 and time1 as -

SELECT @date1;
 ---------- 
| @date1   |
 ---------- 
| %D %b %Y |
 ----------

SELECT @time1;
 -------------------- 
| @time1             |
 -------------------- 
| %Ih : %im : %ss %p |
 --------------------

Now, I have the following valid queries -

SELECT NOW(6) AS output1, DATE_FORMAT(NOW(6), @date1) AS output2, STR_TO_DATE(DATE_FORMAT(NOW(6), @date1), @date1) AS output3;
 ---------------------------- --------------- ---------------------------- 
| output1                    | output2       | output3                    |
 ---------------------------- --------------- ---------------------------- 
| 2021-12-27 02:08:18.282722 | 27th Dec 2021 | 2021-12-27 00:00:00.000000 |
 ---------------------------- --------------- ----------------------------

SELECT NOW(6) AS output1, DATE_FORMAT(NOW(6), CONCAT_WS(SPACE(2), @date1, @time1)) AS output2, STR_TO_DATE(DATE_FORMAT(NOW(6), CONCAT_WS(SPACE(2), @date1, @time1)), CONCAT_WS(SPACE(2), @date1, @time1)) AS output3;
 ---------------------------- ----------------------------------- ---------------------------- 
| output1                    | output2                           | output3                    |
 ---------------------------- ----------------------------------- ---------------------------- 
| 2021-12-27 02:12:48.713278 | 27th Dec 2021  02h : 12m : 48s AM | 2021-12-27 02:12:48.000000 |
 ---------------------------- ----------------------------------- ----------------------------

Now I try the following -

SELECT NOW(6) AS output1, DATE_FORMAT(NOW(6), @time1) AS output2, STR_TO_DATE(DATE_FORMAT(NOW(6), @time1), @time1) AS output3;
 ---------------------------- -------------------- --------- 
| output1                    | output2            | output3 |
 ---------------------------- -------------------- --------- 
| 2021-12-27 02:20:12.335700 | 02h : 20m : 12s AM | NULL    |
 ---------------------------- -------------------- ---------

SELECT CURTIME(6) AS output1, DATE_FORMAT(CURTIME(6), @time1) AS output2, STR_TO_DATE(DATE_FORMAT(CURTIME(6), @time1), @time1) AS output3;
 ----------------- -------------------- --------- 
| output1         | output2            | output3 |
 ----------------- -------------------- --------- 
| 02:16:53.952314 | 02h : 16m : 53s AM | NULL    |
 ----------------- -------------------- ---------

SELECT CURTIME(6) AS output1, TIME_FORMAT(CURTIME(6), @time1) AS output2, STR_TO_DATE(TIME_FORMAT(CURTIME(6), @time1), @time1) AS output3;
 ----------------- -------------------- --------- 
| output1         | output2            | output3 |
 ----------------- -------------------- --------- 
| 02:18:33.676080 | 02h : 18m : 33s AM | NULL    |
 ----------------- -------------------- ---------

In all the above cases I get output3 as NULL

It seems I can generate the desired result only if I manually enter the values -

SELECT STR_TO_DATE("02h : 26m : 26s AM", "%Ih : %im : %ss %p") AS output;
 ---------- 
| output   |
 ---------- 
| 02:26:26 |
 ----------

Even the following query doesn't seem to work -

SELECT STR_TO_DATE("02h : 26m : 26s AM", @time1) AS output;
 -------- 
| output |
 -------- 
| NULL   |
 --------

Now, this is strange. If there is any presence of date format with or without time format, STR_TO_DATE works fine with generated values. But if there is only time format without any presence of date format, STR_TO_DATE no longer works with generated values. In such cases STR_TO_DATE works only with manually entered values.

This is obviously a huge inconvenience since rarely one only enters values manually and will generally work with generated values in practical applications.

Now there is nothing like STR_TO_TIME in MySQL.

So is there any workaround? Or is it that the only way forward is to raise a feature request and wait for a new improved release in future?

UPDATE:

Interestingly, the following works perfectly -

SELECT NOW(6) AS output1, DATE_FORMAT(NOW(6), GET_FORMAT(TIME, "USA")) AS output2, STR_TO_DATE(DATE_FORMAT(NOW(6), GET_FORMAT(TIME, "USA")), GET_FORMAT(TIME, "USA")) AS output3;
 ---------------------------- ------------- ---------- 
| output1                    | output2     | output3  |
 ---------------------------- ------------- ---------- 
| 2021-12-27 03:26:04.787166 | 03:26:04 AM | 03:26:04 |
 ---------------------------- ------------- ----------

CodePudding user response：

I've encountered something like this before and it seems like in MySQL v5.7, you can't STR_TO_DATE on time value alone, you'll need the date value as well. This works:

SET @time1 = '%Y-%m-%d %Ih : %im : %ss %p';

SELECT TIME(STR_TO_DATE(DATE_FORMAT(NOW(6), @time1), '%Y-%m-%d %Ih : %im : %ss %p'))

But since the final output of STR_TO_DATE() include date, then the TIME() function is necessary to extract the time value only.

Demo fiddle.

Probably because the function is "named" as STR_TO_DATE, it makes no sense to convert the time value only. Which is kind of understandable but quite strange as well because on MySQL 8.0, it doesn't behave like that.