在MySQL中存储时间范围并确定当前时间是否在时间范围内 [英] Storing Time Ranges in MySQL and Determine if Current Time is within Time Range

问题描述

我有一个数据库表，以时间格式存储商店的营业时间作为时间范围。例如，如果商店的开放时间是上午9点到下午5点，则会有2列hours_open和hours_close，我在hours_open中存储9:00，在hours_close中存储17:00。要确定商店当前是否打开或关闭，我将运行以下查询：

I have a database table storing opening hours of a shop as a time range in TIME format. Eg if the shop's opening hours are '9am-5pm', there will be 2 columns 'hours_open' and 'hours_close' where I store 9:00 in 'hours_open' and 17:00 in 'hours_close'. To determine if the shop is currently open or closed, I will run the query:

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

我现在需要做的，是适应商店的营业时间，过去午夜， - 上午3点。此外，商店在不同的日子可以有不同的开放时间，例如：周一至周四：下午9点至凌晨3点，周五至周日：下午9点至上午5点。

What I need to do now, is to accommodate shops with opening hours that go past midnight, like 9pm - 3am. Additionally, the shops can have different opening hours on different days, eg: Mon-Thurs: 9pm-3am, Fri-Sun: 9pm-5am.

。我应该如何进行？我可以每天有一对'hours_open'和'hours_close'，例如：'mon_hours_open'，'mon_hours_close'，'tue_hours_open'，'tue_hours_close'...

Now I am stumped. How should I proceed? I can have a pair of 'hours_open' and 'hours_close' for each day, eg: 'mon_hours_open', 'mon_hours_close', 'tue_hours_open', 'tue_hours_close' ...

但是如何处理午夜9点到凌晨5点的营业时间？

But how do I handle opening hours that go past midnight like 9pm-5am?

任何见解？我使用PHP与Codeigniter框架如果这有帮助。

Any insights? I'm using PHP with Codeigniter framework if that helps.

推荐答案

如果它们存储在单独的表中，
$ b

It would be easier to check the hours if they were stored 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)
)

这样查询可能是这样：

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 用于同一表中的每一天，即 表，那么过滤条件很可能会更复杂，可能是这样：

If, however, you would rather stick with having multiple columns like *_hours_open and *_hours_close for every day of week in the same table, i.e. the shop table, then the filter condition would most probably have to be more complex, maybe something like this:

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

这篇关于在MySQL中存储时间范围并确定当前时间是否在时间范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！