使用MySQL增加几分钟的时间

Question

我有一个交易时间表，如下所示：

CREATE TABLE trading_hours(
        id INT NOT NULL AUTO_INCREMENT,
        time_open TIME NOT NULL,
        duration INTEGER NOT NULL, -- In minutes
        day_of_week INTEGER NOT NULL,
        PRIMARY KEY (id)
) ENGINE=INNODB;
INSERT INTO trading_hours(id, time_open, duration, day_of_week) VALUES (1, '6:00', 720, 1); -- sun 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week) VALUES (2, '6:10', 720, 2); -- mon 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week) VALUES (3, '6:20', 720, 3); -- tue 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week) VALUES (4, '16:30', 780, 4); -- wed 13 hours

我用来创建关闭时间，但将持续时间添加到time_open字段。我尝试过使用SEC_TO_TIME()和TIMESTAMPADD()，但是，如果结果时间大于23:59:59，则结果返回时间类似于27:00:00。

而不是27:00:00，我希望看到它是03:00:00，以反映商店关闭的时间。

我尝试过的：

SELECT th.time_open, duration, 
       ADDTIME(th.time_open, SEC_TO_TIME(th.duration*60)) AS time_close 
FROM   trading_hours th;

SELECT th.time_open, th.duration, 
       TIMESTAMPADD(MINUTE,th.duration, th.time_open) AS close_time 
FROM   trading_hours th;

Answer 1

您可以使用TIME_TO_SEC和SEC_TO_TIME来获得您想要的结果；关键是进行模数为86,400的加法(一天中的秒数)：

SELECT *, 
       SEC_TO_TIME((TIME_TO_SEC(time_open) + duration * 60) % 86400) AS time_closed
FROM trading_hours

输出

id  time_open   duration    day_of_week     time_closed
1   06:00:00    720         1               18:00:00
2   06:10:00    720         2               18:10:00
3   06:20:00    720         3               18:20:00
4   16:30:00    780         4               05:30:00

Demo on dbfiddle