mysql date/time calculations

Question

I have the following database structure:

mysql> select * from scheduler;

   +----+-------------------+-----------+-----------+-----------+---------+----------+
   | id | name              | daystart  | hourstart | dayend    | hourend | tariff   |
   +----+-------------------+-----------+-----------+-----------+---------+----------+
   |  6 | Monday morning    | Monday    | 00:00     | Monday    | 06:00   | economic |
   |  8 | Monday night      | Monday    | 22:00     | Monday    | 00:00   | economic |
   | 10 | Tuesday morning   | Tuesday   | 00:00     | Tuesday   | 06:00   | economic |
   | 12 | Tuesday night     | Tuesday   | 22:00     | Tuesday   | 00:00   | economic |
   | 14 | Wednesday morning | Wednesday | 00:00     | Wednesday | 06:00   | economic |
   | 16 | Wednesday night   | Wednesday | 22:00     | Wednesday | 00:00   | economic |
   | 18 | Thursday morning  | Thursday  | 00:00     | Thursday  | 06:00   | economic |
   | 20 | Thursday night    | Thursday  | 22:00     | Thursday  | 00:00   | economic |
   | 22 | Friday morning    | Friday    | 00:00     | Friday    | 06:00   | economic |
   | 24 | Friday night      | Friday    | 22:00     | Friday    | 00:00   | economic |
   | 26 | Saturday          | Saturday  | 00:00     | Saturday  | 00:00   | economic | 
   | 28 | Saturday          | Sunday    | 00:00     | Sunday    | 00:00   | economic |
   | 30 | Monday regular    | Monday    | 06:00     | Monday    | 22:00   | regular  |
   | 32 | Tuesday regular   | Tuesday   | 06:00     | Tuesday   | 22:00   | regular  |
   | 34 | Wednesday regular | Wednesday | 06:00     | Wednesday | 22:00   | regular  |
   | 36 | Thursday regular  | Thursday  | 06:00     | Thursday  | 22:00   | regular  |
   | 38 | Friday regular    | Friday    | 06:00     | Friday    | 22:00   | regular  |
   +----+-------------------+-----------+-----------+-----------+---------+----------+

   mysql> select * from rate_tariffs;
   +----+----------+----------+-------+----------+
   | id | name     | group_id | price | currency |
   +----+----------+----------+-------+----------+
   |  2 | economic |        1 | 15.30 | UTT      |
   |  4 | economic |        3 | 23.40 | UTT      |
   |  6 | economic |        5 | 29.70 | UTT      |
   |  8 | regular  |        1 | 18.00 | UTT      |
   | 10 | regular  |        3 | 27.00 | UTT      |
   | 12 | regular  |        5 | 34.20 | UTT      |
   | 14 | economic |        7 | 3.00  | UTT      |
   | 16 | regular  |        7 | 4.00  | UTT      |
   +----+----------+----------+-------+----------+

What this is: Its a scheduler which should select what tariff to use based on the current date and time.

I'm looking to obtain this information using a single MySQL query.

What I have so far, which is not working properly for some reason:

   mysql> select scheduler.name, rate_tariffs.name, scheduler.id, rate_tariffs.id,
   CURTIME(), ( EXTRACT(HOUR_MINUTE FROm CURTIME())  - REPLACE(hourstart,':',"")) as 
   diff , daystart, hourstart,dayend,hourend from rate_tariffs inner join scheduler 
   on rate_tariffs.name=tariff where daystart=DATE_FORMAT(CURDATE(),'%W') and 
   dayend=DATE_FORMAT(CURDATE(),'%W') and (EXTRACT(HOUR_MINUTE FROm CURTIME()) - 
   REPLACE(hourstart,':',"")) >0 and ( REPLACE(hourend,':','') - EXTRACT(HOUR_MINUTE 
   FROM CURTIME())) >0 and group_id=1;
   
   +------------------+----------+----+----+-----------+------+----------+-----------+----------+---------+
   | name             | name     | id | id | CURTIME() | diff | daystart | hourstart | dayend   | hourend |
   +------------------+----------+----+----+-----------+------+----------+-----------+----------+---------+
   | Thursday morning | economic | 18 |  2 | 01:01:44  |  101 | Thursday | 00:00     | Thursday | 06:00   |
   +------------------+----------+----+----+-----------+------+----------+-----------+----------+---------+
   1 row in set (0.00 sec)
   
   mysql>

Unfortunately this is not working all the time.

For example, at hour 23:45:23, Wednesday, it was not working. One hour later, same query is working (showing Thursday).

Any ideas on how I could make this work better (actually make it work)? Any other ways you would do that in a single SQL query? Would you do this in any other way?

PS: here's the table info:

   INSERT INTO `scheduler` VALUES (6,'Monday morning','Monday','00:00','Monday','06:00','economic'),(8,'Monday night','Monday','22:00','Monday','00:00','economic'),(10,'Tuesday morning','Tuesday','00:00','Tuesday','06:00','economic'),(12,'Tuesday night','Tuesday','22:00','Tuesday','00:00','economic'),(14,'Wednesday morning','Wednesday','00:00','Wednesday','06:00','economic'),(16,'Wednesday night','Wednesday','22:00','Wednesday','00:00','economic'),(18,'Thursday morning','Thursday','00:00','Thursday','06:00','economic'),(20,'Thursday night','Thursday','22:00','Thursday','00:00','economic'),(22,'Friday morning','Friday','00:00','Friday','06:00','economic'),(24,'Friday night','Friday','22:00','Friday','00:00','economic'),(26,'Saturday','Saturday','00:00','Saturday','00:00','economic'),(28,'Saturday','Sunday','00:00','Sunday','00:00','economic'),(30,'Monday regular','Monday','06:00','Monday','22:00','regular'),(32,'Tuesday regular','Tuesday','06:00','Tuesday','22:00','regular'),(34,'Wednesday regular','Wednesday','06:00','Wednesday','22:00','regular'),(36,'Thursday regular','Thursday','06:00','Thursday','22:00','regular'),(38,'Friday regular','Friday','06:00','Friday','22:00','regular');

   INSERT INTO `rate_tariffs` VALUES (2,'economic',1,'15.30','UTT'),(4,'economic',3,'23.40','UTT'),(6,'economic',5,'29.70','UTT'),(8,'regular',1,'18.00','UTT'),(10,'regular',3,'27.00','UTT'),(12,'regular',5,'34.20','UTT'),(14,'economic',7,'3.00','UTT'),(16,'regular',7,'4.00','UTT');

  CREATE TABLE `scheduler` (
   `id` int(3) NOT NULL AUTO_INCREMENT,
   `name` varchar(30) DEFAULT NULL,
   `daystart` varchar(15) DEFAULT NULL,
   `hourstart` varchar(6) DEFAULT NULL,
   `dayend` varchar(15) DEFAULT NULL,
   `hourend` varchar(6) DEFAULT NULL,
   `tariff` varchar(16) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=latin1;

   CREATE TABLE `rate_tariffs` (
     `id` int(3) NOT NULL AUTO_INCREMENT,
     `name` varchar(25) DEFAULT NULL,
     `group_id` int(3) DEFAULT NULL,
     `price` varchar(10) DEFAULT NULL,
     `currency` varchar(10) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1

Thanks, Dan

Answer 1

A couple of observations and suggestions. This will get it working.

It can be easier to work with native types in MySQL, especially dates and times. So I'd recommend changing the start/end hours to time fields. Like this:

ALTER TABLE `scheduler` CHANGE `hourstart` `hourstart` TIME NULL DEFAULT NULL;
ALTER TABLE `scheduler` CHANGE `hourend` `hourend` TIME NULL DEFAULT NULL;

Next, the scheduler says that the hours start at 10pm on Thurs (for example) and end at 12am on Thurs. 12am is in the morning, so the end time is before the start time! So we need to change the end time to become greater than the start time (23:59:59). Also the start and end times overlap for one second. Lets make each part join neatly (but not overlap) by reducing the end time by 1 second. Set any invalid times to 1 second before midnight. Like this:

UPDATE `scheduler` SET
hourend = SUBTIME(hourend, '00:00:01');

UPDATE `scheduler` SET
hourend = '23:59:59'
WHERE hourend < '00:00:00';

Next, we'll adjust the SQL query to use our native MySQL time fields. The only change needed is the two WHERE statements at the bottom. We want it to check if the hour is between the start and end:

SELECT scheduler.name, rate_tariffs.name, scheduler.id, rate_tariffs.id, CURTIME(), ( EXTRACT(HOUR_MINUTE FROm CURTIME())  - REPLACE(hourstart,':',"")) as diff , daystart, hourstart,dayend,hourend from rate_tariffs
INNER JOIN scheduler on rate_tariffs.name=tariff
WHERE daystart = DATE_FORMAT(CURDATE(),'%W')
    AND dayend = DATE_FORMAT(CURDATE(),'%W')
    AND CURTIME() >= hourstart
    AND CURTIME() <= hourend
    AND group_id = 1;