Peregring-lk Peregring-lk - 26 days ago 5
MySQL Question

MySQL get date from hour:minute format

For a

where
clause, if I have a
%h:%i
string, how can I get the last existing
datetime
before
now()
with that hour/minute combination?

Some fictional
select
to show the intended behaviour (
comb
is the magical function/combinator I need, which can explicitly depend on
now()
or not):

mysql> select now(), comb('20:20');
+---------------------+---------------------+
| now() | comb('20:20') |
+---------------------+---------------------+
| 2016-11-12 20:43:39 | 2016-11-12 20:20:00 |
+---------------------+---------------------+

mysql> select now(), comb('20:20');
+---------------------+---------------------+
| now() | comb('20:20') |
+---------------------+---------------------+
| 2016-11-11 01:43:39 | 2016-11-10 20:20:00 |
+---------------------+---------------------+

mysql> select now(), comb('20:20');
+---------------------+---------------------+
| now() | comb('20:20') |
+---------------------+---------------------+
| 2016-01-01 01:43:39 | 2015-12-31 20:20:00 |
+---------------------+---------------------+

Answer
select concat(date(if(time(now()) > '20:20', now(), date_sub(now(), INTERVAL 1 day))), ' 20:20');