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 |
+----------------------------+-------------+----------+
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.
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.