CODEWITHSUNDEEP
mysqlsql
Rafael
Rafael

Reputation: 7746

MySQL DATE_ADD() With Subtract

MySQL DATE_ADD()

I am trying to add to a date string a couple of days, and subtract a couple hours and minutes from it. Maybe it doesn't make sense but here is what I mean...

INSERT INTO 
    publication (`city`, `open`, `close`, `show`, `hide`) 
VALUES (
    'pensacola', -- CITY
    NOW(),       -- OPEN
    DATE_ADD(NOW(), INTERVAL '7 0:0:-1' DAY_SECOND),  -- CLOSE
    DATE_ADD(NOW(), INTERVAL '10 -3:0:0' DAY_SECOND), -- SHOW
    DATE_ADD(NOW(), INTERVAL '17 -3:0:-1' DAY_SECOND) -- HIDE
)

Outputs

+----+-----------+---------------------+---------------------+---------------------+---------------------+--------+
| id | city      | open                | close               | show                | hide                | active |
+----+-----------+---------------------+---------------------+---------------------+---------------------+--------+
|  6 | pensacola | 2015-03-09 12:00:00 | 2015-03-16 12:00:01 | 2015-03-19 15:00:00 | 2015-03-26 15:00:01 |      0 |
+----+-----------+---------------------+---------------------+---------------------+---------------------+--------+

This is not my intention as I have specified a - in my INTERVAL

Desired Output

+----+-----------+---------------------+---------------------+---------------------+---------------------+--------+
|  6 | pensacola | 2015-03-09 12:00:00 | 2015-03-16 11:59:59 | 2015-03-19 09:00:00 | 2015-03-26 08:59:59 |      0 |
+----+-----------+---------------------+---------------------+---------------------+---------------------+--------+

Upvotes: 0

Views: 841

Answers (1)

eggyal
eggyal

Reputation: 126025

Add the days, then subtract the time?

INSERT INTO 
    publication (`city`, `open`, `close`, `show`, `hide`) 
VALUES (
    'pensacola', -- CITY
    NOW(),       -- OPEN
    NOW() + INTERVAL  7 DAY - INTERVAL 1 SECOND, --CLOSE
    NOW() + INTERVAL 10 DAY - INTERVAL 3 HOUR, -- SHOW
    NOW() + INTERVAL 17 DAY - INTERVAL '3:0:1' HOUR_SECOND -- HIDE
)

Upvotes: 2

Related Questions