mysql timespan_Timespan-在mysql中检查工作日和一天中的时间

I need to have a database with different rates at different time of day and on different weekdays.

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

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?

Edit:

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 |

+--------+---------+-------------+------+