php - 基于数据库中时间记录的打开/关闭状态

Question

我有一个 mySQL 表，可以根据时间范围指示商店开门或关门的天气:

 shift table
--------------
 shift_id
 day_of_week    (1-7 : Monday-Sunday)
 open_time      (time, default NULL)
 close_time     (time, default NULL)
 type           (1 || 2)

字段类型说明:1表示当天的第一个时间段，2表示该时间段是当天的第二个时间段。例如，商店营业时间为 13:00 - 15:00 和 18:00 - 01:00。

我使用以下方法获取当天:

$jd = cal_to_jd(CAL_GREGORIAN,date("m"),date("d"),date("Y"));
    $day = jddayofweek($jd, 0);

    switch($day){
        case 0:
            $curDay = 7;
        break;
        default:
            $curDay = $day;
        break;
    }
    return $curDay;

我正在使用以下方法获取当前时间:

$timeString = "%H:%i:%s";
$curTime = mdate($timeString, time());

复杂的例子如下:
当前日期:星期一
当前时间:02:00。
周日时段:15:00 - 18:00 和 21:00 - 02:30。
周一时段:08:30 - 15:30。

Clearly the shift is OPEN but based on Sunday s timeframe and not on Monday s which is the current day.

在给定日期的给定时间查询我的表(或根据需要更改我的表)或 100% 准确定义每次轮类是打开还是关闭的最佳方法是什么？

Answer 1

这就是我的想法:

CREATE TABLE Shifts (
  shift_id INT AUTO_INCREMENT PRIMARY KEY,
  start_day CHAR(9) NOT NULL,
  start_time TIME NOT NULL,
  end_day CHAR(9) NOT NULL,
  end_time TIME NOT NULL
) ENGINE = InnoDB;

现在我想起来了，我真的不确定您是否需要 end_day。但是，使用:

INSERT INTO Shifts (
  start_day,
  start_time,
  end_day,
  end_time
) VALUES
( LOWER('Sunday'), '15:00:00', LOWER('Sunday'), '18:30:00' ),
( LOWER('Sunday'), '21:00:00', LOWER('Monday'), '26:30:00' ),
( LOWER('Monday'), '12:00:00', LOWER('Monday'), '18:00:00' ),
( LOWER('Tuesday'), '10:00:00', LOWER('Tuesday'), '20:45:00' ),
( LOWER('Wednesday'), '16:00:00', LOWER('Wednesday'), '19:30:00' ),
( LOWER('Thursday'), '10:00:00', LOWER('Thursday'), '17:00:00' ),
( LOWER('Thursday'), '19:00:00', LOWER('Friday'), '25:30:00' ),
( LOWER('Friday'), '16:00:00', LOWER('Saturday'), '24:30:00' ),
( LOWER('Saturday'), '15:00:00', LOWER('Saturday'), '20:00:00' ),
( LOWER('Saturday'), '18:30:00', LOWER('Sunday'), '27:00:00' )

然后您可以使用如下内容:

SELECT DAYNAME(NOW()), start_day
FROM Shifts
WHERE (start_day = LOWER(DAYNAME(NOW()))
       AND start_time < CURTIME()
       AND end_time > CURTIME())
   OR (start_day = LOWER(DAYNAME(DATE_SUB(NOW(), INTERVAL 1 DAY)))
       AND start_time < ADDTIME('24:00:00', CURTIME())
       AND end_time > ADDTIME('24:00:00', CURTIME()))

你可以在这里测试:

http://sqlfiddle.com/#!2/41a26/34

并尝试使用不同的测试值:

SELECT DAYNAME(NOW()), start_day
FROM Shifts
WHERE (start_day = LOWER('Friday')
       AND start_time < '01:12:35'
       AND end_time > '01:12:35')
   OR (start_day = LOWER('Thursday')
       AND start_time < ADDTIME('24:00:00', '01:12:35')
       AND end_time > ADDTIME('24:00:00', '01:12:35'))

http://sqlfiddle.com/#!2/41a26/33