mysql怎么添加时间_mysql – 如何添加时间

我有一个日期范围参数,需要拆分成多个日期,每个日期与参数的时间相同.

不确定ADDTIME有什么问题,也许我错过了演员？我可以解决它转换为字符串和concat但我想应该更容易.

电流输出

selectDate time(@s_date) time(@e_date) addtime(date(a.selectDate), time(@s_date))

1 2016-09-03 08:00:00 16:00:00 08:00:00

2 2016-09-04 08:00:00 16:00:00 08:00:00

欲望输出

selectDate time(@s_date) time(@e_date) addtime(date(a.selectDate), time(@s_date))

1 2016-09-03 08:00:00 16:00:00 2016-09-03 08:00:00

2 2016-09-04 08:00:00 16:00:00 2016-09-04 08:00:00

可能的方法：

CONCAT(DATE_FORMAT(a.selectDate, '%Y-%m-%d '),

DATE_FORMAT(@s_date, '%H:%i:%s')) as start_time,

select a.selectDate,

time(@s_date),

time(@e_date),

addtime(date(a.selectDate), time(@s_date))

from (

select '1900-01-01' + INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a)) DAY as selectDate

from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e

) a

CROSS JOIN (SELECT @s_date := '2016-09-03 08:00:00', @e_date := '2016-09-04 16:00:00') par

WHERE selectDate BETWEEN date(@s_date)

AND date(@e_date)