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)
Advertisement