CODEWITHSUNDEEP
mysqldatedatetimeintervals
Forest
Forest

Reputation: 938

How do I increment a value in MySQL to a certain time in the future?

I am trying to update a DATETIME field in a table. What I want for it is to always have three hours before it expires.

My current code is:

UPDATE mindcore_sessions
  SET session_time = DATE_ADD(session_time, INTERVAL 3 HOUR)
  WHERE session_id = '$sessionId';

An example value would be: 2013-02-11 00:00:00. If I run this query, it will change to: 2013-02-11 03:00:00. Which is correct. But, if it is run again, it changes to 2013-02-11 06:00:00 and so on.

What I want for it is to always be only three hours ahead. This is hard to explain, but I just want it to stay the same if it is run again. And if it is run again 1 minute later, I want it to increment by just one minute.

Sorry if the question is verbose, but I am utterly confused (and it is late!).

Thanks :)

Upvotes: 1

Views: 2413

Answers (3)

bpoiss
bpoiss

Reputation: 14003

There is no way in mysql to know if the same query was executed before.

The only possible solution would be to save the original value in an extra column, and use this column to check if the value was changed in the past.

If you want the value to be ahead 3 hours from now, you can simply do this:

DATE_ADD(NOW(), INTERVAL 3 HOUR)

Upvotes: 1

Olexa
Olexa

Reputation: 587

Add your interval to NOW() instead of adding to the current value:

UPDATE mindcore_sessions SET session_time = DATE_ADD(NOW(), INTERVAL 3 HOUR) WHERE session_id = '$sessionId';

Upvotes: 2

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Instead of adding three(3) hours to the last session_time, add three(3) hours to NOW():

update mindcore_sessions
set session_time = DATE_ADD(NOW(), INTERVAL 3 HOUR)
where session_id = '$sessionId';`

Upvotes: 3

Related Questions