Comment ajouter une heure à une date

Question

J'ai un paramètre de plage de dates, qui doit être divisé en plusieurs dates, et chacune d'entre elles aura la même heure que le paramètre.

Je ne suis pas sûr de savoir quel est le problème avec ADDTIME Il me manque peut-être un moulage ? Je peux résoudre le problème en transformant la chaîne de caractères en chaîne de caractères et en concat, mais je pense que cela devrait être plus facile.

Sortie de courant

    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

Désir de sortie

    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

Solution possible :

CONCAT(DATE_FORMAT(a.selectDate, '%Y-%m-%d '), 
       DATE_FORMAT(@s_date, '%H:%i:%s')) as start_time,

Mon grand interrogation

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)

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)

renvoyer ce lien