Summary of the use of MySQL date and time functions

This article is based on MySQL 8.0

This article introduces MySQL functions for date and time operations.

Date and Time Functions

Let's take a look at examples of commonly used MySQL date and time functions.

Get the current date and time

CURDATE()

Returns the current date in "YYYY-MM-DD" or YYYYMMDD format, depending on whether the function is used in a string context or a numeric context.

mysql> SELECT CURDATE();
 -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
 -> 20080613

CURTIME([fsp])

Returns the current time in 'hh:mm:ss' or hhmmss format, depending on whether the function is used in a string context or a numeric context. The value is expressed in the session time zone.

If the specified fsp precision is from 0 to 6 decimal places, then the decimal precision is from 0 to 6.

mysql> SELECT CURTIME();
 -> '23:50:26'
mysql> SELECT CURTIME() + 0;
 -> 235026.000000

NOW([fsp])

Returns the current date and time in 'YYYY-MM-DD hh:MM:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in a string context or a numeric context. The value is expressed in the session time zone.

mysql> SELECT NOW();
 -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
 -> 20071215235026.000000

Time and date format conversion

The same date and time can be expressed in many different ways, and sometimes it is necessary to convert between different formats. In MySQL, the date_format() function is used:

• DATE_FORMAT(date,format): Formats a date value according to a format string.
  

The relevant formats are as follows. In addition to being used in this function, the format can also be used in: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
 -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
 -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
 -> '%D %y %a %d %m %b %j');
 -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
 -> '%H %k %I %r %T %S %w');
 -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
 -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
 -> '00'

Date and time operations

• DATE_ADD(date,INTERVAL expr unit),
• DATE_SUB(date,INTERVAL expr unit)
  

These functions perform date arithmetic to shift dates forward or backward. The date parameter specifies a starting date or datetime value. expr is an expression that specifies the interval value to be added or subtracted from the start date. expr is evaluated as a string; it may begin with - to indicate a negative interval. unit is a keyword indicating the units that the expression should use.

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
 -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
 -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
 -> INTERVAL 1 SECOND);
 -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
 -> INTERVAL 1 DAY);
 -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
 -> INTERVAL '1:1' MINUTE_SECOND);
 -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
 -> INTERVAL '1 1:1:1' DAY_SECOND);
 -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
 -> INTERVAL '-1 10' DAY_HOUR);
 -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
 -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
 -> INTERVAL '1.999999' SECOND_MICROSECOND);
 -> '1993-01-01 00:00:01.000001'

• DATEDIFF(expr1,expr2)
  

DATEDIFF() returns expr1−expr2, expressed as the number of days from one date to another. expr1 and expr2 are date or date and time expressions. Only the date portion of the value is used in the calculation.

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
 -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
 -> -31

refer to:

【1】：12.7 Date and Time Functions

[2]: MySQL function to get the current date and time

【3】：A guide to MySQL date and time functions

This is the end of this article about MySQL date and time functions. For more information about MySQL date and time functions, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!