我有一张交易时间表,如下所示:
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;
3条答案
按热度按时间9gm1akwq1#
你可以使用
TIME_TO_SEC
和SEC_TO_TIME
来得到你想要的结果;关键是做模86,400(一天中的秒数)的加法:输出
Demo on dbfiddle
ktca8awb2#
您可以使用
addtime
函数,如下所述:https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/date-and-time-functions.html#function_addtime
0yycz8jy3#
我的是在MySQL中工作,如