Timespan-在mysql中检查工作日和一天中的时间 [英] Timespan - Check for weekday and time of day in mysql
问题描述
我需要一个在一天中的不同时间和工作日具有不同费率的数据库.
I need to have a database with different rates at different time of day and on different weekdays.
例如:
Between 10:00 and 16:00 monday to friday I have one rate.
Between 16:00 and 10:00 monday to friday I have another. And on the weekends there is another rate.
For example:
Between 10:00 and 16:00 monday to friday I have one rate.
Between 16:00 and 10:00 monday to friday I have another. And on the weekends there is another rate.
问题在于时间在午夜时分结束.
The problem is when the time goes over midnight.
我一直在寻找解决方案.我发现我以为该线程对我有用，但事实并非如此.至少我不能让它按我的意愿工作. 处理时间和午夜之后
I have looked around for a solution. I have found this thread that I thought would work for me but it doesn't. At least I can't get it to work as I want it to. Dealing with times and after midnight
我尝试过的其他解决方案也不起作用.它将时间存储为mysql中的TIME，并检查时间和日期是否在数据库的时间跨度之间.这也没有解决，因为我没有找到解决时间跨度过午夜的好方法.
The other solution I tried didn't work either. It was to store time as TIME in mysql and check if the time and day was between the timespan in the database. This didn't work out either because I didn't find a good solution for when the timespan goes over midnight.
我应该如何解决这个问题?数据库应该是什么样子以及要获取数据的查询?
How should I try to solve this? What should the database look like and the query to get the data?
这是到目前为止的结果. starttid =开始时间，sluttid =结束时间. Ovrig_tid不在任何特定时间使用. startdag =星期几，slutdag =星期几. 0 =星期一，6 =星期日.因此结果应该是每个bolag_id中的最大值.
Here is what it got so far. starttid = start time, sluttid = end time. Ovrig_tid is used when it's not any of the specific times. startdag = start day of week, slutdag = end day of week. 0 = monday, 6 = sunday. So the result should be max one from every bolag_id.
推荐答案
编辑-修改为符合注释中指定的标准(结束时间):
EDIT - Modified to meet criteria (end time) specified in comments:
我相信您要做的是分别存储每天的费率.至少存储一个值，将给定日期的最后一分钟存储为最终的全包价格(这将是整天唯一具有单一费率的日期行).在任何给定的日期/时间，只需查阅此表即可确定该时间段内的给定费率.见下文:
I believe what you'll want to do is store each day's rate separately. Store at least one value with the last minute of a given day as a final catch-all rate (this will be the only row for days with a single rate all day). At any given day/time, just consult this table to determine the given rate for that period of time. See below:
DROP TABLE IF EXISTS tRate;
CREATE TABLE tRate (
rateId INT(11) UNSIGNED NOT NULL auto_increment,
rateDay TINYINT(1),
rateEndTime TIME,
rate DECIMAL(9,2),
PRIMARY KEY (rateId)
)
;
INSERT INTO tRate VALUES
(NULL, 0, '00:10:00', '0.80'),
(NULL, 0, '23:59:59', '0.90'),
(NULL, 1, '00:10:00', '0.90'),
(NULL, 1, '00:16:00', '0.75'),
(NULL, 1, '23:59:59', '0.90')
-- (etc. for all days 0-6)
;
SET @execDay = DATE_FORMAT(NOW(), '%w'); -- 1 in the case of today for the resultset below
SET @execTime = DATE_FORMAT(NOW(), '%H:%m:%s'); -- 14:02:33 at the time this example was run
鉴于此数据，请执行以下查询:
Given this data, the following query:
SELECT *
FROM
tRate
WHERE
rateDay = @execDay
and @execTime < rateEndTime
;
返回特定执行时间的结果集:
Returns the resultset for the particular execution time:
+--------+---------+-------------+------+
| rateId | rateDay | rateEndTime | rate |
+--------+---------+-------------+------+
| 5 | 1 | 23:59:59 | 0.90 |
+--------+---------+-------------+------+
这篇关于Timespan-在mysql中检查工作日和一天中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
