如何在日期中添加时间

Question

我有一个日期范围参数，需要将其拆分为多个日期，并且每个日期的时间与该参数相同。

不确定 ADDTIME 的问题在哪里，也许我缺少一个转换？我可以将其转换为字符串并连接解决，但我认为应该更容易。

当前输出

    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)

- Juan Carlos Oropeza

---

尝试这样做。选择转换(concat(selectdate,' ',time)为datetime)。 - Prasanna Kumar J

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Juan Carlos Oropeza

Answer 1

select a.selectDate,
       time(@s_date),
       time(@e_date),
       addtime(concat(a.selectDate,' ','00:00:00'),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)

参见 this link

- Prasanna Kumar J