Geek asks and answers

Saving time ranges in MySQL and determining the current time within a time range

I have a database table that stores store hours in the form of a time range in TIME format. For example, if the store’s hours of operation are 9 a.m. to 5 p.m., there will be two columns “hours_op” and “hours”, where I store 9:00 at opening hours and 17:00 at “hours”. To determine if the store is currently open or closed, I launched a query:

SELECT * 
  FROM shop 
 WHERE CAST(NOW() AS TIME) BETWEEN hours_open AND hours_close;

What I need to do now is to place stores with watches that run past midnight, for example, 9pm - 3am. In addition, stores may have different opening hours on different days, for example: Mon-Thu: 9 pm 3 nights, Fri-Sun: 9 pm 5 am.

Now I'm at a standstill. How do I proceed? I can have a couple of hours and days of hours, for example: "mon_hours_open", "mon_hours_close", "tue_hours_open", "tue_hours_close" ...

But how do I handle hours of work that go past midnight, for example, 9pm 5am?

Any ideas? I am using PHP with a Codeigniter map if this helps.

+3
source share
4 answers

It would be easier to check the clock if it was saved in a separate table, something like

shop_working_hours (
  shop_id int FK,
  day_of_week int,
  hours_open time,
  hours close time,
  PK (shop_id, day_of_week)
)

Thus, the query may look like this:

SELECT s.*
FROM shop s
  INNER JOIN shop_working_hours h ON s.id = h.shop_id
WHERE
  CASE
    WHEN h.hours_close > h.hours_open THEN
      h.day_of_week = DAYOFWEEK(NOW()) AND
      CAST(NOW() AS time) >= h.hours_open AND CAST(NOW() AS time) < h.hours_close
    ELSE
      h.day_of_week = DAYOFWEEK(NOW() - 1) AND
      CAST(NOW() AS time) >= h.hours_open OR CAST(NOW() AS time) < h.hours_close
  END

, , , *_hours_open *_hours_close , shop, , , , , - :

SELECT s.*
FROM shop s
  CROSS JOIN (
    SELECT 1 AS day_of_week
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
  ) h
WHERE
  CASE h.day_of_week
    WHEN 1 THEN
      CASE
        WHEN s.mon_hours_close > s.mon_hours_open THEN
          h.day_of_week = DAYOFWEEK(NOW()) AND
          CAST(NOW() AS time) >= s.mon_hours_open AND
          CAST(NOW() AS time) < s.mon_hours_close
        ELSE
          h.day_of_week = DAYOFWEEK(NOW() - INTERVAL 1 DAY) AND
          CAST(NOW() AS time) >= s.mon_hours_open OR
          CAST(NOW() AS time) < s.mon_hours_close
      END
    WHEN 2 THEN
      CASE
        WHEN s.tue_hours_close > s.tue_hours_open THEN
        …  /* same for Tuesday */
      END
    …  /* and for all the other days of week */
  END
+3

, 09:00:00 11:59:59, - 12:00:00 - 03:00:00?

+1

opening :

opening
    day_opens       <--- what day the shop opens
    hour_opens      <--- what hour the shop opens
    duration_open   <--- how many hours it stays open

, , ( ), :

WHERE (   day_opens = Day(NOW()) 
      AND hourOpens <= Hour(NOW())
      AND Hour(NOW()) < hourOpens + duration_open 
      )
   OR (   day_opens + 1 = Day(NOW())
      AND Hour(NOW()) < hourOpens + duration_open - 24
      )

:

WHERE day_opens * 24 + hourOpens    <= Day(NOW()) * 24 + Hour(NOW())
  AND Day(NOW()) * 24 + Hour(NOW()) <  day_opens * 24 + hourOpens + hourOpens
+1

More articles:


All Articles