mysql日期/时间计算
我有以下数据库结构：
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分，它不起作用了。一小时后，同样的查询开始工作(显示在星期四)。
有什么想法可以让我的工作更好(实际上使它起作用)？在一个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
谢谢你，丹
回答 1
Stack Overflow用户
发布于 2013-01-09 18:02:29
一些意见和建议。这会让它发挥作用的。
可以更容易地在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;https://stackoverflow.com/questions/14249292
复制
腾讯云开发者
