CODEWITHSUNDEEP
phpmysqltimetimespan
Lille_skutt
Lille_skutt

Reputation: 139

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.

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.

Upvotes: 1

Views: 874

Answers (2)

StudyOfCrying
StudyOfCrying

Reputation: 530

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 |
+--------+---------+-------------+------+

Upvotes: 2

Marcus Adams
Marcus Adams

Reputation: 53830

To make this really simple, you could have something like the following schema:

rate_by_hour
--------------------------------------
day     hour     rate
--------------------------------------
1       0        1.00
1       1        1.00
...
1       23       1.50
2       0        1.10
2       1        1.10
...

So, you have the rate separated by hour, for each day of the week.

Also, for each billable item, you have one row per hour:

charge_per_hour
----------------------------------------
user_id     date        hour   hour_used
----------------------------------------
1           2012/02/01  23      0.25
1           2012/02/02  0       1.00
1           2012/02/02  1       0.25

Here's the query to calculate the total:

SELECT SUM(c.hour_used * r.rate)
FROM charge_per_hour c
LEFT JOIN rate_by_hour r
ON r.day = DAYOFWEEK(c.date)
  AND r.hour = c.hour
WHERE user_id = 1

Upvotes: 1

Related Questions