📝MySQL - Date Function - Tips

Version

select version();
'8.0.28'

TIMESTAMPDIFF()

Calculation of age. Calculating the number of days elapsed from a point.

SELECT
  '2000-01-01' as birth
  , TIMESTAMPDIFF(YEAR, '2000-01-01', now()) AS age
  , TIMESTAMPDIFF(MONTH, '2000-01-01', now()) AS age_month
  , TIMESTAMPDIFF(DAY, '2000-01-01', now()) AS age_day

+------------+------+-----------+---------+
| birth      | age  | age_month | age_day |
+------------+------+-----------+---------+
| 2000-01-01 |   22 |       274 |    8360 |
+------------+------+-----------+---------+

DATEDIFF()

SELECT
  DATEDIFF('2000-01-10', '2000-01-01') AS diff_day1
  , DATEDIFF('2000-01-01', '2000-01-10') AS diff_day2
  , DATEDIFF('2000-01-01', null) AS diff_day3

+-----------+-----------+-----------+
| diff_day1 | diff_day2 | diff_day3 |
+-----------+-----------+-----------+
|         9 |        -9 |      NULL |
+-----------+-----------+-----------+

DATE_FORMAT()

year

SELECT DATE_FORMAT('2019-12-07', '%Y')
> 2019

SELECT DATE_FORMAT('2019-12-07', '%y')
> 19

month

SELECT DATE_FORMAT('2019-12-07', '%Y%m')
> 201912

SELECT DATE_FORMAT('2019-12-07', '%M')
December

day

SELECT DATE_FORMAT('2019-12-07', '%Y%m%d')
> 20191207

SELECT DATE_FORMAT('2019-12-07', '%D')
> 7th