emaNoN emaNoN - 4 months ago 31
MySQL Question

MySQL's STR_TO_DATE() returns NULL if called with only a time component

I came across some weirdness with MySQL's function

STR_TO_DATE()
. It seems to not be able to parse times, like so:

It works on a full string:

mysql> SELECT STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p');
+-------------------------------------------------------+
| STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p') |
+-------------------------------------------------------+
| 2016-08-07 11:59:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)


Date only works:

mysql> SELECT STR_TO_DATE('08/07/16', '%m/%d/%y');
+-------------------------------------+
| STR_TO_DATE('08/07/16', '%m/%d/%y') |
+-------------------------------------+
| 2016-08-07 |
+-------------------------------------+
1 row in set (0.00 sec)


Time only doesn't:

mysql> SELECT STR_TO_DATE('11:59 AM', '%l:%i %p');
+-------------------------------------+
| STR_TO_DATE('11:59 AM', '%l:%i %p') |
+-------------------------------------+
| NULL |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)


Formatting time works:

mysql> SELECT DATE_FORMAT(NOW(), '%l:%i %p');
+--------------------------------+
| DATE_FORMAT(NOW(), '%l:%i %p') |
+--------------------------------+
| 1:58 PM |
+--------------------------------+
1 row in set (0.00 sec)


But formatting the time then turning it back with the same format string doesn't.

mysql> SELECT STR_TO_DATE(DATE_FORMAT(NOW(), '%l:%i %p'), '%l:%i %p');
+---------------------------------------------------------+
| STR_TO_DATE(DATE_FORMAT(NOW(), '%l:%i %p'), '%l:%i %p') |
+---------------------------------------------------------+
| NULL |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)


As a workaround, I wound up doing:

mysql> SELECT TIME(STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p'));
+-------------------------------------------------------------+
| TIME(STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p')) |
+-------------------------------------------------------------+
| 11:59:00 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)


The data is stored that way in a huge csv file, so editing it would be a pain. I am just wondering if this is normal or if I'm overlooking something simple.

Server info if it matters:

mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

Connection id: 19
Current database:
Current user: michael@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.13-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 29 min 10 sec

Answer

Keep in mind that there is a special NO_ZERO_DATE and NO_ZERO_IN_DATE SQL mode which could disallow zero dates and thus let STR_TO_DATE() return NULL if you only parse a time.

Try running the following command before running STR_TO_DATE() with only a time:

mysql> SET sql_mode = '';