Parsing SQL AM/PM for comparison

Question

Is it possible to parse AM & PM times in SQL and use them for comparison?

For example, a statement like the following:

STR_TO_DATE(pack_time_min) <= DATE_ADD(NOW(), INTERVAL 1 HOUR)

With data like so: 1:30pm

Answer 1

You can parse 12-hour times this way:

STR_TO_DATE('1:30pm', '%l:%i%p')

Result:

+----------------------------------+
| str_to_date('1:30pm', '%l:%i%p') |
+----------------------------------+
| 13:30:00                         |
+----------------------------------+

That's a TIME value, not a DATETIME. The NOW() function will return a DATETIME, so you should use CURRENT_TIME() instead.

mysql> select current_time() + interval 1 hour;
+----------------------------------+
| current_time() + interval 1 hour |
+----------------------------------+
| 02:57:13                         |
+----------------------------------+

Then you can compare a TIME to a TIME.

mysql> select str_to_date('1:30pm', '%l:%i%p') <= current_time() + interval 1 hour;
+----------------------------------------------------------------------+
| str_to_date('1:30pm', '%l:%i%p') <= current_time() + interval 1 hour |
+----------------------------------------------------------------------+
|                                                                    0 |
+----------------------------------------------------------------------+