Skip to content

Correctly Use SUBTIME() and ADDTIME()

Time Format in SUBTIME()

MySQL provides subtime(expr1, expr2) functions for developers to subtract a specific interval of time expr2 from expr1. The time format you should provide in expr2 basically are:

  • "%e %H:%i:%s.%f" (with colon delimiters) or
  • "%e %H%i%s.%f" (without colon delimiters)
But there are a little differences in practice.

Second

For example, if you'd like to deduct 5 seconds from now, you can do this:

mysql> select now() now, subtime(now(), '00:00:05') sub1,  subtime(now(), '5') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 00:48:00 | 2013-08-28 00:47:55 | 2013-08-28 00:47:55 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Both "00:00:05" and "5" are all correct format.

Minute

Let's try to deduct 60 seconds, the things change:

mysql> select now() now, subtime(now(), '00:00:60') sub1,  subtime(now(), '60') sub2;
+---------------------+------+------+
| now                 | sub1 | sub2 |
+---------------------+------+------+
| 2013-08-28 00:56:06 | NULL | NULL |
+---------------------+------+------+
1 row in set, 2 warnings (0.00 sec)

Since the valid number for %s (the last two digits) is from 00 to 59, both above formats are confined by the range. If you'd like to deduct 60 seconds which is equal to 1 minute, you can do this:

mysql> select now() now, subtime(now(), '00:01:00') sub1,  subtime(now(), '100') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 00:57:39 | 2013-08-28 00:56:39 | 2013-08-28 00:56:39 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Please note that, in the above result of sub2, the input "100" does NOT mean 100 seconds, it means 1 minutes and 0 second.

Hour

Same reason applies to %i, you should use "01:00:00" instead of the wrong format "00:60:00":

mysql> select now() now, subtime(now(), '00:60:00') sub1,  subtime(now(), '6000') sub2;
+---------------------+------+------+
| now                 | sub1 | sub2 |
+---------------------+------+------+
| 2013-08-28 01:22:06 | NULL | NULL |
+---------------------+------+------+
1 row in set, 2 warnings (0.00 sec)

mysql> select now() now, subtime(now(), '01:00:00') sub1,  subtime(now(), '10000') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 01:22:26 | 2013-08-28 00:22:26 | 2013-08-28 00:22:26 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

The reason is the same, the valid values are from 00 to 59 for minutes.

Day

Try to deduct 1 day, you can do this:

24 Hours

mysql> select now() now, subtime(now(), '24:00:00') sub1,  subtime(now(), '240000') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 01:38:36 | 2013-08-27 01:38:36 | 2013-08-27 01:38:36 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

1 Day (Long Form)

mysql> select now() now, subtime(now(), '1 00:00:00') sub1,  subtime(now(), '1 000000') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 01:38:41 | 2013-08-27 01:38:41 | 2013-08-27 01:38:41 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

1 Day (Short Form)

mysql> select now() now, subtime(now(), '1 00') sub1;
+---------------------+---------------------+
| now                 | sub1                |
+---------------------+---------------------+
| 2013-08-28 01:38:50 | 2013-08-27 01:38:50 |
+---------------------+---------------------+
1 row in set (0.00 sec)

All the five formats are correct. You might have noticed that, 24 is supposed to be an invalid value at the hourly position for the specifier %H. In fact, it's a valid value.

10 Days = 240 Hours

Furthermore, you can have 3 digits in hours, e.g. 240 hours (10 days).

mysql> select now() now, subtime(now(), '240:00:00') sub1,  subtime(now(), '2400000') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 01:43:27 | 2013-08-18 01:43:27 | 2013-08-18 01:43:27 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Max Value, 34 Days = 816 Hours

At most, you can have 816 hours or 34 days in this function.

mysql> select now() now, subtime(now(), '34 00') sub1;
+---------------------+---------------------+
| now                 | sub1                |
+---------------------+---------------------+
| 2013-08-28 01:51:49 | 2013-07-25 01:51:49 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now() now, subtime(now(), '816:00:00') sub1,  subtime(now(), '8160000') sub2;
+---------------------+---------------------+---------------------+
| now                 | sub1                | sub2                |
+---------------------+---------------------+---------------------+
| 2013-08-28 01:51:52 | 2013-07-25 01:51:52 | 2013-07-25 01:51:52 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Minus Value

By adding a minus sign to the value, we means the opposite way of the function, either for subtime() or addtime(). For example, add 3 days to now:

mysql> select now() now, subtime(now(), '-3 00') add1;
+---------------------+---------------------+
| now                 | add1                |
+---------------------+---------------------+
| 2013-08-28 04:45:04 | 2013-08-31 04:45:04 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The input arguments of addtime() has the same format with subtime().

Leave a Reply

Your email address will not be published. Required fields are marked *