如何计算两个MySQL日期相差多少年月周日？

本文共计323个文字，预计阅读时间需要2分钟。

1. 相差年数：SELECT NOW(), DATE_ADD(NOW(), INTERVAL -400 DAY) AS 历史日期, TIMESTAMPDIFF(YEAR, DATE_ADD(NOW(), INTERVAL -400 DAY), NOW()) AS 相差年数;

2.相差月数：SELECT NOW(), DATE_ADD(NOW(), INTERVAL -400 DAY) AS 历史日期, TIMESTAMPDIFF(MONTH, DATE_ADD(NOW(), INTERVAL -400 DAY), NOW()) AS 相差月数;

1、相差年数

1. SELECT
2. NOW() 当前日期,
3. DATE_ADD(NOW(), INTERVAL - 400 DAY) 历史日期,
4. TIMESTAMPDIFF(
5. YEAR,
6. DATE_ADD(NOW(), INTERVAL - 400 DAY),
7. NOW()
8. ) AS 相差年;

2、相差月数

1. SELECT
2. NOW() 当前日期,
3. DATE_ADD(NOW(), INTERVAL - 400 DAY) 历史日期,
4. TIMESTAMPDIFF(
5. MONTH,
6. DATE_ADD(NOW(), INTERVAL - 400 DAY),
7. NOW()
8. ) AS 相差月;

3、相差周数

1. SELECT
2. NOW() 当前日期,
3. DATE_ADD(NOW(), INTERVAL - 400 DAY) 历史日期,
4. TIMESTAMPDIFF(
5. WEEK,
6. DATE_ADD(NOW(), INTERVAL - 400 DAY),
7. NOW()
8. ) AS 相差周;

4、相差天数

整理了一下三种方式：

1. SELECT
2. NOW() 当前日期,
3. DATE_ADD(NOW(), INTERVAL - 400 DAY) 历史日期,
4. TIMESTAMPDIFF(
5. DAY,
6. DATE_ADD(NOW(), INTERVAL - 400 DAY),
7. NOW()
8. ) AS 相差日;
9. SELECT
10. TO_DAYS('2018-09-10') - TO_DAYS('2017-09-01') AS 相差天数;
11. SELECT
12. DATEDIFF(NOW(), '2018-09-01') AS 相差天数;