Reputation: 109

Mysql Time Arithmetic - Time only

This should be so easy... but is driving me mad.

UPDATE time SET time = (time - interval 130 minute) WHERE stuff=whatever;

Time is a time column only, i.e. 09:00:00.

Upvotes: 1

Answers (4)

Reputation: 4689

There is a DATE_SUB method that works like the DATE_ADD method you are looking for.

DATE_SUB(NOW(),INTERVAL 130 MINUTE)

Check this link for more information:

Upvotes: 0

Reputation: 489

If time is a datetime or a timestmap, you must use a date_sub funktion

SELECT date_sub(time, interval 130 minute) FROM ....

Otherwise you can also convert your time with UNIX_TIMESTAMP, sub it and convert with FROM_TIMESTAMP into a mysql timestamp back

Upvotes: 0

Reputation: 727047

Assuming that you would like to subtract 130 minutes from the current time, you can use addtime, like this:

UPDATE time SET time = addtime(time, '-02:10') where stuff=whatever

130 minutes is 2 hours and 10 minutes, hence the -02:10 constant.

Here is a quick demo on sqlfiddle.

Upvotes: 1

Reputation: 2640

Change - to , and it will work. The correct Query is:

UPDATE time SET time = (time, interval 130 minute) where stuff=whatever

Upvotes: 0