MySQL 将时间转换为 HH :MM

Question

为什么SELECT STR_TO_DATE('07:30 PM','%H:%I %p');返回NULL ？

我希望看到19:30

更多背景:

我正在尝试查询一个表，其中时间列值存储为 HH:MM PM 。示例:

SELECT * FROM table where (STR_TO_DATE(table.time, '%H:%I %p') < NOW())

Answer 1

您破坏了一些格式代码。应为'07:30 PM','%h:%i %p'。此外，您需要一个允许无效日期的 SQL 模式，因为 this :

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0

mysql> SET @@SESSION.sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> STR_TO_DATE('07:30 PM','%H:%I %p') as original,
    -> STR_TO_DATE('07:30 PM','%h:%i %p') as fixed;
+----------+-------+
| original | fixed |
+----------+-------+
| NULL     | NULL  |
+----------+-------+
1 row in set, 3 warnings (0.00 sec)

mysql> SET @@SESSION.sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> STR_TO_DATE('07:30 PM','%H:%I %p') as original,
    -> STR_TO_DATE('07:30 PM','%h:%i %p') as fixed;
+----------+----------+
| original | fixed    |
+----------+----------+
| NULL     | 19:30:00 |
+----------+----------+
1 row in set, 1 warning (0.00 sec)