CODEWITHSUNDEEP
mysqldatetime
Frederik Hoeft
Frederik Hoeft

Reputation: 1440

MySQL DateTime calculations for opening hours

So we have the following MySQL table with opening hours:

+------+---------------------+---------------------+--------+
| Id   | StartTime           | EndTime             | ClubId |
+------+---------------------+---------------------+--------+
|    1 | 1970-01-04 20:00:00 | 1970-01-05 01:00:00 |  17424 |
|    2 | 1970-01-05 20:00:00 | 1970-01-06 01:00:00 |  17424 |
|    3 | 1970-01-06 20:00:00 | 1970-01-07 05:00:00 |  17424 |
|    4 | 1970-01-07 20:00:00 | 1970-01-01 05:00:00 |  17424 |
|    5 | 1970-01-01 22:00:00 | 1970-01-02 05:00:00 |  17426 |
|    6 | 1970-01-04 22:00:00 | 1970-01-05 05:00:00 |  17426 |
|    7 | 1970-01-05 22:00:00 | 1970-01-06 05:00:00 |  17426 |
|    8 | 1970-01-06 22:00:00 | 1970-01-07 05:00:00 |  17426 |
|    9 | 1970-01-07 22:00:00 | 1970-01-01 05:00:00 |  17426 |
|   10 | 1970-01-01 20:00:00 | 1970-01-02 04:00:00 |  17427 |
|   11 | 1970-01-03 20:00:00 | 1970-01-04 05:00:00 |  17427 |
|   12 | 1970-01-04 20:00:00 | 1970-01-05 05:00:00 |  17427 |
|   13 | 1970-01-05 20:00:00 | 1970-01-06 05:00:00 |  17427 |
|   14 | 1970-01-02 18:00:00 | 1970-01-03 03:00:00 |  17428 |

...

where StartTime is when the business opens and EndTime when it closes. We don't care about the year and month of the dates but the day represents the day of the week (even though it may not match the historical data. 1970-01-01 may have been a Thursday, but as we don't care about year and month we don't care about the historical context either. We simply defined that the day 01 represents a Sunday) and that's all we care about. So after 1970-01-07 (Saturday) comes 1970-01-01 (Sunday). The times are just what you'd expect from opening hours.

Let's take a look at where Id = 1 for example: StartTime 1970-01-04 20:00:00 and EndTime 1970-01-05 01:00:00 means that this club opens on Wednesday 8 PM and closes on Thursday 1 AM.

What we need to do (and what's giving us trouble) is to filter these entries to only show businesses that are currently open. I tried using the INBETWEEN keyword but entries like Id = 9 where the day of the week goes from 7 (Saturday) to 1 (Sunday) are giving me trouble.

Any help would be greatly appreciated :) Has anyone any experience with stuff like this and has an idea on how to put this into a MySQL query? I've seen this question however they are not using datetime objects. If it can't be avoided we could alter the tables and use the proposed solution from the question above but we would prefer using the DATETIME datatype (if possible).

Upvotes: 1

Views: 73

Answers (1)

GMB
GMB

Reputation: 222482

The most efficient option probably is to transform the current date/time to a value that is consistent with your representation of dates in the table. Then you can do direct filtering.

As I understand your question, that would be:

from_unixtime(unix_timestamp(now() + interval 3 day) % (60 * 60 * 24 * 7))

The logic is to use modulo to project the current date/time as the equivalent date in the first week of epoch. Additional logic is needed to remap the January 1st, 1970 as a Sunday (while in reality it was a Thursday): that's what the + interval 3 day does.

We can test that the mapping works correctly like so:

select now(), 
    from_unixtime(unix_timestamp(now() + interval 3 day) % (60 * 60 * 24 * 7)) ts_now

Yields:

now()               | ts_now             
:------------------ | :------------------
2020-10-23 09:52:59 | 1970-01-05 10:52:59

Then you can then put that in the where clause of a query. Using a subquery is handy to avoid repeating the expression:

select t.clubid
from mytable t
inner join (select from_unixtime(unix_timestamp(now() + interval 3 day) % (60 * 60 * 24 * 7)) ts_now) x
where t.starttime >= x.ts_now and t.endtime < x.ts_now

Upvotes: 1

Related Questions