MENU
Programming / Cloud / Frameworks / Libraries / Design
  1. Home
  2. MySQL
  3. How to use Date Functions (Compare, Add, Difference, Extract)

How to use Date Functions (Compare, Add, Difference, Extract)

MySQL

This section reviews the date functions available in MySQL. This section explains “Compare,” “Add,” “Difference,” “Extract,” and other date functions with examples of their use.

TOC

Selection (from a group)

SECOND｜Extract second

mysql> SELECT SECOND('2018-01-15 11:30:20');
+-------------------------------+
| SECOND('2018-01-15 11:30:20') |
+-------------------------------+
|                            20 |
+-------------------------------+

MINUTE｜Extract minutes

mysql> SELECT MINUTE('2018-01-15 11:30:20');
+-------------------------------+
| MINUTE('2018-01-15 11:30:20') |
+-------------------------------+
|                            30 |
+-------------------------------+

HOUR｜Extract time

mysql> SELECT HOUR('2018-01-15 11:30:20');
+-----------------------------+
| HOUR('2018-01-15 11:30:20') |
+-----------------------------+
|                          11 |
+-----------------------------+

TIME｜Extract hours, minutes and seconds

mysql> SELECT TIME('2018-01-15 11:30:20');
+-----------------------------+
| TIME('2018-01-15 11:30:20') |
+-----------------------------+
| 11:30:20                    |
+-----------------------------+

DAY｜Extract day

mysql> SELECT DAY('2018-01-15 11:30:20');
+----------------------------+
| DAY('2018-01-15 11:30:20') |
+----------------------------+
|                         15 |
+----------------------------+

MONTH｜Extract month

mysql> SELECT MONTH('2018-01-15 11:30:20');
+------------------------------+
| MONTH('2018-01-15 11:30:20') |
+------------------------------+
|                            1 |
+------------------------------+

YEAR｜Extract year

mysql> SELECT YEAR('2018-01-15 11:30:20');
+-----------------------------+
| YEAR('2018-01-15 11:30:20') |
+-----------------------------+
|                        2018 |
+-----------------------------+

DATE｜Extract date

mysql> SELECT DATE('2018-01-15 11:30:20');
+-----------------------------+
| DATE('2018-01-15 11:30:20') |
+-----------------------------+
| 2018-01-15                  |
+-----------------------------+

WEEKDAY｜Extract day number

( 0 = Monday 1 = Tuesday… 6 = Sunday )

mysql> SELECT WEEKDAY('2018-01-15 11:30:20');
+--------------------------------+
| WEEKDAY('2018-01-15 11:30:20') |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

WEEK｜Extract week number

mysql> SELECT WEEK('2018-01-15 11:30:20');
+-----------------------------+
| WEEK('2018-01-15 11:30:20') |
+-----------------------------+
|                           2 |
+-----------------------------+

LAST_DAY｜Extract the end of the month

mysql> SELECT LAST_DAY('2018-01-22');
+------------------------+
| LAST_DAY('2018-01-22') |
+------------------------+
| 2018-01-31             |
+------------------------+

Current date and time

CURRENT_DATE

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2018-08-31     |
+----------------+

CURRENT_TIME｜Current time

mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 07:06:46       |
+----------------+

NOW｜Current date and time

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-08-31 07:06:46 |
+---------------------+

CURRENT_TIMESTAMP｜Synonyms for NOW

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2018-08-31 07:06:46 |
+---------------------+

UTC_DATE｜Current UTC date

mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2018-08-30 |
+------------+

UTC_TIME｜Current UTC time

mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 22:06:46   |
+------------+

UTC_TIMESTAMP｜Current UTC date and time

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2018-08-30 22:06:46 |
+---------------------+

Additions and subtractions

ADDTIME｜Add time

mysql> SELECT ADDTIME('2018-01-15 11:30:20', '01:10:10');
+--------------------------------------------+
| ADDTIME('2018-01-15 11:30:20', '01:10:10') |
+--------------------------------------------+
| 2018-01-15 12:40:30                        |
+--------------------------------------------+

SUBTIME｜Subtract time

mysql> SELECT SUBTIME('2018-01-15 11:30:20', '01:10:10');
+--------------------------------------------+
| SUBTIME('2018-01-15 11:30:20', '01:10:10') |
+--------------------------------------------+
| 2018-01-15 10:20:10                        |
+--------------------------------------------+

DATE_ADD｜Add time value (interval)

mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2018-01-15 11:30:21                                |
+----------------------------------------------------+

mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-01-16 11:30:20                             |
+-------------------------------------------------+

mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND);
+-----------------------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2018-01-15 11:31:21                                             |
+-----------------------------------------------------------------+

mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND);
+-------------------------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND) |
+-------------------------------------------------------------------+
| 2018-01-16 12:31:21                                               |
+-------------------------------------------------------------------+

DATE_SUB｜Subtract time value (interval)

mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2018-01-15 11:30:19                                |
+----------------------------------------------------+

mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-01-14 11:30:20                             |
+-------------------------------------------------+

mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND);
+-----------------------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2018-01-15 11:29:19                                             |
+-----------------------------------------------------------------+

mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND);
+-------------------------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND) |
+-------------------------------------------------------------------+
| 2018-01-14 10:29:19                                               |
+-------------------------------------------------------------------+

Interval

DATEDIFF｜Date Difference

mysql> SELECT DATEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20');
+--------------------------------------------------------+
| DATEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20') |
+--------------------------------------------------------+
|                                                      5 |
+--------------------------------------------------------+

TIMEDIFF｜Time Difference

mysql> SELECT TIMEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20');
+--------------------------------------------------------+
| TIMEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20') |
+--------------------------------------------------------+
| 122:10:10                                              |
+--------------------------------------------------------+

TIMESTAMPDIFF｜Difference in specified unit

mysql> SELECT TIMESTAMPDIFF(MINUTE, '2018-01-15 11:30:20', '2018-01-15 13:40:30');
+---------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE, '2018-01-15 11:30:20', '2018-01-15 13:40:30') |
+---------------------------------------------------------------------+
|                                                                 130 |
+---------------------------------------------------------------------+

mysql> SELECT TIMESTAMPDIFF(DAY, '2018-01-15 11:30:20', '2018-01-20 13:40:30');
+------------------------------------------------------------------+
| TIMESTAMPDIFF(DAY, '2018-01-15 11:30:20', '2018-01-20 13:40:30') |
+------------------------------------------------------------------+
|                                                                5 |
+------------------------------------------------------------------+

mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-01-15 11:30:20', '2018-03-20 13:40:30');
+--------------------------------------------------------------------+
| TIMESTAMPDIFF(MONTH, '2018-01-15 11:30:20', '2018-03-20 13:40:30') |
+--------------------------------------------------------------------+
|                                                                  2 |
+--------------------------------------------------------------------+

mysql> SELECT TIMESTAMPDIFF(YEAR, '2018-01-15 11:30:20', '2020-01-20 13:40:30');
+-------------------------------------------------------------------+
| TIMESTAMPDIFF(YEAR, '2018-01-15 11:30:20', '2020-01-20 13:40:30') |
+-------------------------------------------------------------------+
|                                                                 2 |
+-------------------------------------------------------------------+

Conversion

DATE_FORMAT｜Conversion to specified format

mysql> SELECT DATE_FORMAT('2018-01-15 11:30:20', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2018-01-15 11:30:20', '%H:%i:%s') |
+------------------------------------------------+
| 11:30:20                                       |
+------------------------------------------------+

mysql> SELECT DATE_FORMAT('2018-01-15 11:30:20', '%Y/%m/%d');
+------------------------------------------------+
| DATE_FORMAT('2018-01-15 11:30:20', '%Y/%m/%d') |
+------------------------------------------------+
| 2018/01/15                                     |
+------------------------------------------------+

TIME_TO_SEC｜Converted to seconds

mysql> SELECT TIME_TO_SEC('00:02:02');
+-------------------------+
| TIME_TO_SEC('00:02:02') |
+-------------------------+
|                     122 |
+-------------------------+

CONVERT_TZ｜Convert time zone

mysql> SELECT CONVERT_TZ('2018-01-15 12:00:00', "+00:00", "+09:00");
+-------------------------------------------------------+
| CONVERT_TZ('2018-01-15 12:00:00', "+00:00", "+09:00") |
+-------------------------------------------------------+
| 2018-01-15 21:00:00                                   |
+-------------------------------------------------------+

UNIX_TIMESTAMP｜Convert to UNIX timestamp

mysql> SELECT UNIX_TIMESTAMP('2018-01-15 11:30:20');
+---------------------------------------+
| UNIX_TIMESTAMP('2018-01-15 11:30:20') |
+---------------------------------------+
|                            1515983420 |
+---------------------------------------+

FROM_UNIXTIME｜Convert UNIX timestamp to date/time

mysql> SELECT FROM_UNIXTIME(1515983420);
+---------------------------+
| FROM_UNIXTIME(1515983420) |
+---------------------------+
| 2018-01-15 11:30:20       |
+---------------------------+
MySQL
Pickup
Let's share this post !

Related Articles

TOC