I am running into an inconsistent behaviour across two different Docker image with the STR_TO_DATE function. On running on a ubuntu box and the other one running on my Mac.
On Linux:
SELECT STR_TO_DATE('203005','%H%i%s');
+--------------------------------+
| STR_TO_DATE('203005','%H%i%s') |
+--------------------------------+
| 20:30:05 |
+--------------------------------+
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19 |
+-----------+
On my Mac:
SELECT STR_TO_DATE('203005','%H%i%s');
+--------------------------------+
| STR_TO_DATE('203005','%H%i%s') |
+--------------------------------+
| NULL |
+--------------------------------+
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.12 |
+-----------+
show warnings;
+---------+------+-------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '203005' for function str_to_date |
+---------+------+-------------------------------------------------------------+
Both running Mysql on a Docker image:
docker_container:
name: mysql
image: mysql:8.0
STR_TO_DATE will return NULL if you have an incomplete datetime value (in your case, there's no date) and SQL MODE NO_ZERO_DATE is set (see the manual). For example:
SET @@sql_mode = ''
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date
;
SET @@sql_mode = 'NO_ZERO_IN_DATE'
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date
;
SET @@sql_mode = 'NO_ZERO_DATE'
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date
Output:
@@sql_mode date
20:30:05
NO_ZERO_IN_DATE 20:30:05
NO_ZERO_DATE null