考虑下面的SQL查询:
SELECT * FROM opening_hours
WHERE week_day = WEEKDAY(NOW()) + 1
AND open_hour =< date_format(now(),'%H:%i')
AND close_hour >= date_format(now(),'%H:%i')
open_hour
/close_hour
字段为TIME类型。
我正在使用MySQL
假设open_time为"18:00",close_time为"02:00",当前时间为"22:41"。我们有一个单独的DB记录的close_time(因为它是在午夜之后),但我们永远不会得到它的结果,因为close_time "02:00"不大于当前时间"22:41"。
如果当前时间是"01:00",我们将得到下一天的值,因为工作日不匹配。
解决方案吗?
您是否愿意将这些值存储为整数(分钟),以便php能够直接处理这些值,而无需进行任何转换?
例如…当前时间:
$timearr = explode(':',date("w:H:i"));
$currenttime = ($timearr[0]) * 1440 + $timearr[1] * 60 + $timearr[2]
当前时间的最小值= 0(太阳,00:00),最大值为10079(星期六,23:59)
在数据库中,存储的打开/关闭时间值可能在0到11519 (Sat, 47:59)之间
现在假设,当前时间为"Sun, 01:00"(一周的第一天),执行上述转换,该值为60;一周最后一天(星期六)的开放/关闭时间设置为"17:00"answers"02:00"(实际上是星期天),在数据库中存储为9660和10200(星期六,26:00)。在这种情况下,上面的查询不会找到我们需要的记录(Sat, 17:00, 02:00),因为我们可能没有任何小于"02:00"(120)的open_time。为了解决这个问题,我们将"Sun, 01:00"转换为"Sat, 25:00",方法是在$currenttime上加上7*1440(整整一周),结果是10140。然后DB查询如下:
SELECT open_time,clos_time FROM open_hours
WHERE (open_time <= $currenttime
AND close_time >= $currenttime)
OR (open_time <= $currenttime +10080
AND close_time >= $currenttime + 10080);
或者有什么替代的更整洁的解决方案?
我从经验中学到,总是按照事物本来的样子存储事物,并适当地使用数据类型,因此我建议使用time或date-time选项。当你开始用它做其他事情的时候,最好是用正确的格式来转换它,以便显示、排序等。
所有我知道的SQL数据库都支持日期/日期/时间/时间格式,当然mysql。
如果你需要移动它,即导出到另一个数据库或格式,你将使用一个工具(即使只是一个sql脚本),将有能力将其转换为另一种格式或类型。
使用INT
,因为不是所有数据库都支持DATE
字段,或者以相同的方式支持它们。INT
更加便携,如果您选择采用这种方法,那么使用整数时间戳就可以很容易地计算出日期。