Adding Seconds to datetime in MySQL

Question

I'm trying to add seconds to a date based on certain events that occur. Often times, if these events occur at the same time, too many seconds get added on. Here's how I am currently doing it in PHP and MySQL

$time_add_on = 15 - $seconds_left;
DATE_ADD(STR_TO_DATE(end_dt,'%Y-%m-%d %H:%i:%s'), INTERVAL '".$time_add_on."' SECOND

What this is doing is taking the current seconds left from the 'end_dt' subtracting 15 and adding it to the 'end_dt', basically giving you 15 seconds left.

The goal here is basically too add 15 seconds or reset the date to where only 15 seconds are left. This is causing issues, where instead of resetting to 15 seconds left, it'll add 30 seconds or 45 seconds.

Would anyone know the best way to do this?

Answer 1

what about using timestamps?

Answer 2

UPDATE table end_dt = DATE_ADD(end_dt, INTERVAL 15 second)
WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()

I think that's what you want, basically adds 15 seconds to end_dt when end_dt is 15 seconds away from now

EDIT NEW QUERY This query should work:

UPDATE `table`
    SET end_dt = DATE_ADD(end_dt, INTERVAL (15 - TIMESTAMPDIFF(SECOND, NOW(), end_dt)) SECOND)
WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()