我有一个表,里面有这样的员工工作程序:
| column | type |
| id | {int} |
| day_of_week | (int 1{Mon}-7{Sun}) |
| from_time | {time H:i:s} |
| to_time | {time H:i:s} |
| nd | {enum 0,1} |
我需要做一个搜索,当时间范围从00:00到23:59时,会返回当前时间的工作人员,这一切都很好,但我的问题是,当一个人选择周一23:00到04:00时,我如何存储这些字段,以便我可以从mysql中查询它?
我用php破解解决了这个问题,对于感兴趣的人来说,这里是解决方案:
for ($i=0;$i<=1000000;$i++){
$to_h = rand(0,23);
if($to_h<10) $to_h = '0'.$to_h;
$to_m = rand(0,59);
if($to_m<10) $to_m = '0'.$to_m;
$from_h = rand(0,23);
if($from_h<10) $from_h = '0'.$from_h;
$from_m = rand(0,59);
if($from_m<10) $from_m = '0'.$from_m;
if($to_h<=$from_h){
if($to_h<$from_h){
$next=1;
}elseif($to_h==$from_h){
if($to_m<=$from_m){
$next=1;
}else{
$next=0;
}
}
}else{
$next = 0;
}
$db->query("insert into test set day_of_week='".rand(1,7)."', from_time='".$from_h.':'.$from_m."', to_time='".$to_h.':'.$to_m."', nd='".$next."'");
}
和mysql查询来搜索现在工作的人:
SELECT *, date_format(now(), '%H:%i:%s') FROM test where day_of_week=2 and ((from_time<=date_format(now(), '%H:%i:%s') and to_time>=date_format(now(), '%H:%i:%s') and nd=0) OR (to_time<=date_format(now(), '%H:%i:%s') and nd=1)) limit 30;
这个查询有一个非常好的响应时间,我没有注意到基准测试中有任何错误。注:"nd"表示次日(当前工作日午夜以上的工作时间)
使用DATETIME
作为开始和结束。
因为就这么简单:
SELECT TIMESTAMPDIFF(HOUR, start, end) -- time difference in hours
获取时间点的工作人员:
SELECT * FROM table WHERE NOW() BETWEEN start AND end
获取当前工作人员(假设end
的NULL
值):
SELECT * FROM table WHERE NOW() > start AND end IS NULL
因为你把所有东西都存储为一个数字,所以你可以用类似CASE WHEN to_hour < from_hour THEN day + 1 ELSE day
的东西派生一个to_date。然后你可以把你的to和for连接在一起,并测试它们之间是否有NOW(其中现在是相同形式的连接:CONCAT(day,hour,minute)
类似
SELECT *
FROM <table>
WHERE
CONCAT(<currentday>,<currenthour>,<currentminute>) BETWEEN CONCAT(day, from_hour, from_minute) AND CONCAT(CASE WHEN to_hour < from_hour THEN day + 1 ELSE day END CASE, to_hour, to_min)
为了连接,可能需要将这些int转换为chars(我不记得mysql是否在乎),但只要稍微调整一下,这应该可以让你跨越几天。
已编辑以添加:当我进一步思考这个问题时,因为时间和小时是两位数,所以当它们是一位数时,有必要用0来填充它们,这样CONCAT就不会把所有东西都放屁了。这看起来有点像CASE WHEN LENGTH(from_hour) = 1 then CONCAT('0', from_hour) else from_hour END CASE
等等……它导致了一个丑陋的WHERE语句,但它应该都能工作。
如果我需要这样做,我会存储一个持续时间,而不是终点。我假设"天"代表午夜,并存储从午夜开始和结束的小时和分钟数。
| column | type | comment
| id | {int} |
| day_of_week | (int 1{Mon}-7{Sun}) |
| from_time | TIME | time past midnight of dow
| to_time | TIME | time past midnight of dow
我可以使用HOUR和MINUTE函数从TIME数据类型中提取小时和分钟。我可以很容易地将TIME值添加到DATE值中,以获得DATETIME值。
例如,对于代表周二晚上10点到周三早上6点的行:
id, day_of_week, from_time, to_time
42, 2, '22:00', '30:00'
例如,如果我确定我的日期值是"星期二"
SELECT ADDTIME('2014-09-30',t.from_time) AS from_dt
, ADDTIME('2014-09-30',t.from_time) AS to_dt
FROM t
WHERE t.day_of_week = 2
否则,我可以只使用上一个"星期日"的日期值
SELECT ADDTIME('2014-09-28' + INTERVAL t.day_of_week DAY,t.from_time) AS from_dt
, ADDTIME('2014-09-28' + INTERVAL t.day_of_week DAY,t.from_time) AS to_dt
FROM t
跟进
问:选择查询会是什么样子?比方说现在我们是星期三01:00,我需要检查一下现在工作的人吗
这个问题只会稍微复杂一点。从今天的日期开始,我们需要在前一个星期天,加上星期几,然后加上持续时间。
要获得"星期日"日期,我们可以使用这样的表达式:
DATE(NOW()) - INTERVAL WEEKDAY(NOW())+1 DAY
我们可以使用该值,并添加天数和持续时间
类似这样的东西:
SELECT t.id
, t.day_of_week
, t.from_time
, t.to_time
FROM t
JOIN (DATE(NOW()) - INTERVAL WEEKDAY(NOW())+1 DAY AS d) d
ON NOW() >= ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.from_time)
AND NOW() < ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.to_time)
我认为周日7比0的存储值可能是个问题,我们必须做一些测试并处理它。
对于测试,我们可以删除对NOW()
的引用,并使用一个返回要测试的DATETIME的表达式。我们可以使用用户定义的变量,例如@now
。(我们可能需要确保使用STR_to_DATE函数(例如STR_TO_DATE(@now,'%Y-%m-%d %H:%i')
)将其转换为DATETIME
(我认为这些问题将出现在边缘案例中,很可能是周日的0和7。)
为了进行测试,我会将DATETIME表达式添加到SELECT列表中,以及我们想用作谓词的布尔表达式(在ON或WHERE子句中),这样我们就可以看到发生了什么。
SET @now = '2014-10-01 04:00'
SELECT t.id
, t.day_of_week
, t.from_time
, t.to_time
, @now
, ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.from_time) AS from_dt
, ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.to_time) AS to_dt
, ( @now >= ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.from_time) AND
@now < ADDTIME(d.d + INTERVAL t.day_of_week DAY, t.to_time)
) AS is_match
FROM t
JOIN (SELECT DATE( @now - INTERVAL WEEKDAY(@now)+1 DAY AS d) d
就我个人而言,我认为我会将Sunday day_of_week值存储为0(就像我们在Unix上所做的那样),但结果可能是7可以正常工作。我们可以使用DAYOFWEEK
或WEEKDAY
函数来计算表达式,可能需要进行一些调整。
还需要测试周一午夜的时段。day_of_week=7,to_time>24小时,以确保这些也能被提取。。。我们希望在生成DATETIME的方式上保持一致,我们希望确保始终"向前添加",并且不要回滚一周,to_time必须大于from_time。
SUBTIME函数可以减去,以获得持续时间,如果需要的话,