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