MySQL Date and Time Functions

CURDATE and CURTIME Functions

CURDATE(): It returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on which function is used.

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2020-08-28 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
|      20200828 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 17:51:39  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
|        175152 |
+---------------+
1 row in set (0.00 sec)

NOW, SLEEP, and SYSDATE Functions

NOW(): It returns a constant time that indicates the time at which the statement start to execute, and SYSDATE() returns the exact time at which it executes.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-08-28 17:53:08 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2020-08-28 17:53:25 |        0 | 2020-08-28 17:53:25 |
+---------------------+----------+---------------------+
1 row in set (2.34 sec)

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2020-08-28 17:54:07 |        0 | 2020-08-28 17:54:09 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

DATEDIFF and DATE_ADD Functions

DATEDIFF(): It returns expr1 − expr2 (date or date-and-time expressions) expressed as a value in days from one date to the other

mysql> SELECT DATEDIFF(CURDATE(),'2018-02-19');
+----------------------------------+
| DATEDIFF(CURDATE(),'2018-02-19') |
+----------------------------------+
|                              921 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);
+-------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 1 DAY) |
+-------------------------------------+
| 2020-08-29                          |
+-------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 5 MONTH);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 5 MONTH) |
+---------------------------------------+
| 2021-01-28                            |
+---------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT Function

mysql> SELECT DATE_FORMAT(CURDATE(), '%W %M %Y');
+------------------------------------+
| DATE_FORMAT(CURDATE(), '%W %M %Y') |
+------------------------------------+
| Friday August 2020                 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(CURDATE(), '%D %y %a %d %m %b %j') AS DATE_FORMAT;
+---------------------------+
| DATE_FORMAT               |
+---------------------------+
| 28th 20 Fri 28 08 Aug 241 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2018-02-19', GET_FORMAT(DATE,'USA')) AS USA_DATE_FORMAT;
+-----------------+
| USA_DATE_FORMAT |
+-----------------+
| 02.19.2018      |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2018-02-19', GET_FORMAT(DATE,'EUR')) AS EUR_DATE_FORMAT;
+-----------------+
| EUR_DATE_FORMAT |
+-----------------+
| 19.02.2018      |
+-----------------+
1 row in set (0.00 sec)

DAYNAME Function

mysql> SELECT DAYNAME(CURDATE());
+--------------------+
| DAYNAME(CURDATE()) |
+--------------------+
| Friday             |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2018-02-19');
+-----------------------+
| DAYNAME('2018-02-19') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)

DAYOFMONTH, DAYOFWEEK, and DAYOFYEAR Functions

WEEKDAY(): It Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday)

mysql> SELECT DAYOFMONTH(CURDATE());
+-----------------------+
| DAYOFMONTH(CURDATE()) |
+-----------------------+
|                    28 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK('2018-02-19');
+-------------------------+
| DAYOFWEEK('2018-02-19') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK(CURDATE());
+----------------------+
| DAYOFWEEK(CURDATE()) |
+----------------------+
|                    6 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFYEAR(CURDATE());
+----------------------+
| DAYOFYEAR(CURDATE()) |
+----------------------+
|                  241 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFYEAR('2018-02-19');
+-------------------------+
| DAYOFYEAR('2018-02-19') |
+-------------------------+
|                      50 |
+-------------------------+
1 row in set (0.00 sec)

EXTRACT Function

mysql> SELECT EXTRACT(YEAR FROM CURDATE()) AS YEAR;
+------+
| YEAR |
+------+
| 2020 |
+------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(MONTH FROM CURDATE()) AS MONTH;
+-------+
| MONTH |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

LAST_DAY and MONTHNAME Functions

mysql> SELECT LAST_DAY('2018-02-19');
+------------------------+
| LAST_DAY('2018-02-19') |
+------------------------+
| 2018-02-28             |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME(CURDATE());
+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| August               |
+----------------------+
1 row in set (0.00 sec)

SEC_TO_TIME and SUBDATE Functions

mysql> SELECT SEC_TO_TIME(4500);
+-------------------+
| SEC_TO_TIME(4500) |
+-------------------+
| 01:15:00          |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE(CURDATE(), INTERVAL 107 DAY) AS DIFFERENCE;
+------------+
| DIFFERENCE |
+------------+
| 2020-05-13 |
+------------+
1 row in set (0.00 sec)

UTC_DATE and UTC_TIME Functions

mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2020-08-28 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 12:33:54   |
+------------+
1 row in set (0.00 sec)

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2020-08-28 12:34:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK(CURDATE());
+-----------------+
| WEEK(CURDATE()) |
+-----------------+
|              34 |
+-----------------+
1 row in set (0.00 sec)

Comments and Reactions

What Next?

MySQL String Functions MySQL Data Definition

GoDarda

MySQL Database