Skip to content

How to Distinguish Confused Time Format in SUBTIME() or ADDTIME()

  • by
  • August 28, 2013March 29, 2018
MySQL provides subtime(expr1, expr2) 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.

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.

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)

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

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)

Try to deduct 1 day, you can do this:
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)

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)

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. 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)

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)

Oppositely, you can add a time interval by adding a minus sign to the value, 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().
Tags:

Leave a Reply

Your email address will not be published.