mysql日期/时间计算

Question

我有以下数据库结构:

mysql> 从调度程序中选择 *;

   +----+-------------------+-----------+-----------+-----------+---------+----------+
   | 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 中使用 native 类型会更容易，尤其是日期和时间。所以我建议将开始/结束时间更改为 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 查询以使用我们的 native 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;