Mysql日期/时间计算

Question

我有以下数据库结构:

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

这是什么:它是一个调度程序，应该根据当前日期和时间选择使用什么资费。

我正在寻找使用单个MySQL查询获取此信息。

我目前拥有的，由于某种原因不能正常工作:

   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>

不幸的是，这不是一直工作。

例如，在星期三的23:45:23，它没有工作。一个小时后，同样的查询工作(显示星期四)。

关于我如何使它工作得更好(实际上使它工作)有什么想法吗?在单个SQL查询中还有其他方法吗?你还会用别的方法吗?

PS:这里是表信息:

   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

谢谢,丹

Answer 1

一些观察和建议。这将使它工作。

在MySQL中使用本地类型更容易，特别是日期和时间。因此，我建议将开始/结束小时更改为time字段。这样的:

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

接下来，调度程序说工作时间从周四晚上10点开始(例如)，到周四上午12点结束。12点是早上，所以结束时间在开始时间之前!所以我们需要改变结束时间让它大于开始时间(23:59:59)。开始时间和结束时间重叠一秒。让我们通过减少1秒的结束时间使每个部分整齐地连接(但不重叠)。将任何无效时间设置为午夜前1秒。这样的:

UPDATE `scheduler` SET
hourend = SUBTIME(hourend, '00:00:01');
UPDATE `scheduler` SET
hourend = '23:59:59'
WHERE hourend < '00:00:00';

接下来，我们将调整SQL查询以使用我们的本地MySQL时间字段。唯一需要改变的是底部的两个WHERE语句。我们想让它检查小时是否在开始和结束之间:

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;