- DATEDIFF 计算日期差
–注:返回值是相差的天数,不能定位到小时、分钟和秒
mysql> select DATEDIFF(A.modtime,NOW()),A.* from SYS_AREA A;
+---------------------------+--------+---------+------------+---------+---------+---------------------+
| DATEDIFF(A.modtime,NOW()) | AREAID | PAREAID | AREAPY | ORDERID | ISAPPLY | MODTIME |
+---------------------------+--------+---------+------------+---------+---------+---------------------+
| -2739 | 0 | NULL | top | 0 | 1 | 2012/7/4 |
| -2171 | 330000 | 0 | zhejiang | 5 | 1 | 2014/1/23 |
| -79 | 330100 | 330000 | hangzhou | 99 | 1 | 2019/10/16 10:53:55 |
| -697 | 330102 | 330100 | sahgncheng | 1 | 1 | 2018/2/5 |
| -697 | 330103 | 330100 | xiacheng | 2 | 1 | 2018/2/5 |
| -697 | 330104 | 330100 | jianggan | 3 | 1 | 2018/2/5 |
| -80 | 330106 | 330100 | xihu | 5 | 1 | 2019/10/15 8:46:42 |
| -80 | 330108 | 330100 | binjiang | 6 | 1 | 2019/10/15 8:47:21 |
| -80 | 330184 | 330100 | yuhang | 11 | 1 | 2019/10/15 8:57:27 |
| -80 | 330181 | 330100 | xiaoshan | 10 | 1 | 2019/10/15 8:57:02 |
| -80 | 330187 | 330100 | fuyang | 12 | 1 | 2019/10/15 8:57:41 |
| -80 | 330105 | 330100 | gongshu | 4 | 1 | 2019/10/15 8:46:34 |
| -80 | 330110 | 330100 | zhijiang | 9 | 1 | 2019/10/15 8:56:50 |
| -80 | 330186 | 330100 | xiasha | 7 | 1 | 2019/10/15 8:47:37 |
| -80 | 330188 | 330100 | tonglu | 14 | 1 | 2019/10/15 8:58:11 |
| -80 | 330189 | 330100 | jiande | 16 | 1 | 2019/10/15 8:58:42 |
| -80 | 330190 | 330100 | chunan | 15 | 1 | 2019/10/15 8:58:27 |
| -1 | 330191 | 330100 | linan | 13 | 1 | 2020-01-02 11:36:31 |
| 0 | 330231 | 330100 | dajiangdo | 8 | 1 | 2020-01-03 11:29:35 |
+---------------------------+--------+---------+------------+---------+---------+---------------------+
19 rows in set (0.00 sec)
- TIMESTAMPDIFF
– 可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND)
mysql> select A.modtime,now(),DATEDIFF(A.modtime,NOW()),TIMESTAMPDIFF(DAY,A.modtime,NOW()) DAY,TIMESTAMPDIFF(HOUR,A.modtime,NOW()) HOUR ,TIMESTAMPDIFF(MINUTE,A.modtime,NA;
+---------------------+---------------------+---------------------------+------+-------+---------+-----------+
| modtime | now() | DATEDIFF(A.modtime,NOW()) | DAY | HOUR | MINUTE | SECOND |
+---------------------+---------------------+---------------------------+------+-------+---------+-----------+
| 2012/7/4 | 2020-01-03 11:44:33 | -2739 | 2739 | 65747 | 3944864 | 236691873 |
| 2014/1/23 | 2020-01-03 11:44:33 | -2171 | 2171 | 52115 | 3126944 | 187616673 |
| 2019/10/16 10:53:55 | 2020-01-03 11:44:33 | -79 | 79 | 1896 | 113810 | 6828638 |
| 2018/2/5 | 2020-01-03 11:44:33 | -697 | 697 | 16739 | 1004384 | 60263073 |
| 2018/2/5 | 2020-01-03 11:44:33 | -697 | 697 | 16739 | 1004384 | 60263073 |
| 2018/2/5 | 2020-01-03 11:44:33 | -697 | 697 | 16739 | 1004384 | 60263073 |
| 2019/10/15 8:46:42 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115377 | 6922671 |
| 2019/10/15 8:47:21 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115377 | 6922632 |
| 2019/10/15 8:57:27 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115367 | 6922026 |
| 2019/10/15 8:57:02 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115367 | 6922051 |
| 2019/10/15 8:57:41 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115366 | 6922012 |
| 2019/10/15 8:46:34 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115377 | 6922679 |
| 2019/10/15 8:56:50 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115367 | 6922063 |
| 2019/10/15 8:47:37 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115376 | 6922616 |
| 2019/10/15 8:58:11 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115366 | 6921982 |
| 2019/10/15 8:58:42 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115365 | 6921951 |
| 2019/10/15 8:58:27 | 2020-01-03 11:44:33 | -80 | 80 | 1922 | 115366 | 6921966 |
| 2020-01-02 11:36:31 | 2020-01-03 11:44:33 | -1 | 1 | 24 | 1448 | 86882 |
| 2020-01-03 11:29:35 | 2020-01-03 11:44:33 | 0 | 0 | 0 | 14 | 898 |
+---------------------+---------------------+---------------------------+------+-------+---------+-----------+
19 rows in set (0.00 sec)
mysql> select date_sub(now(),interval 1 day) 减,date_sub(now(),interval 0 day),date_sub(now(),interval -1 day) 加;
+---------------------+--------------------------------+---------------------+
| 减 | date_sub(now(),interval 0 day) | 加 |
+---------------------+--------------------------------+---------------------+
| 2020-01-02 11:35:49 | 2020-01-03 11:35:49 | 2020-01-04 11:35:49 |
+---------------------+--------------------------------+---------------------+
1 row in set (0.00 sec)