MySQL Date/Time

For storing a date or a date/time value in MySQL, the following data types are available:

• DATE – format YYYY-MM-DD
• DATETIME – format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
• YEAR – format YYYY or YY

DATE() function

The date portion of a date or datetime expression is extracted.

Example:

mysql> SELECT DATE('2022-12-30 01:02:03');
-> '2022-12-30'

ADDDATE() function

ADDDATE() is a synonym for DATE ADD when called using the INTERVAL form of the second argument (). DATE SUB is a synonym for the related function SUBDATE() (). Temporal Intervals has more information on the INTERVAL unit argument.

Example:

mysql> SELECT DATE_ADD('2022-01-02', INTERVAL 31 DAY);
-> '2022-02-02'
mysql> SELECT ADDDATE('2022-01-02', INTERVAL 31 DAY);
-> '2022-02-02'

CURDATE() function

Depending on whether the function is used in a string or numeric context, it returns the current date as a value in the ‘YYYY-MM-DD’ or ‘YYYYMMDD’ format.

Example:

mysql> SELECT CURDATE();
-> '2022-06-13'
mysql> SELECT CURDATE() + 0;
-> 20220613

DATE_ADD() function

Date arithmetic is performed by these functions. The starting date or datetime value is specified by the date parameter. The interval value to be added or subtracted from the starting date is specified by expr, which is an expression. expr is evaluated as a string; for negative intervals, it may begin with a -. unit is a keyword that specifies how the expression should be understood in units.

Example:

mysql> SELECT DATE_ADD('2022-05-01',INTERVAL 1 DAY);
-> '2022-05-02'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '2022-01-01 00:00:00'
mysql> SELECT DATE_ADD('2022-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '2022-01-01 23:59:59'

DATE_FORMAT() function

The date value is formatted according to the format string.

The format string can include the specifiers shown in the table below. Before format specifier characters, the percent character is necessary. Other functions that use the specifiers are STR TO DATE(), TIME FORMAT(), and UNIX TIMESTAMP() ().

DATEDIFF() function

DATEDIFF() returns the difference in days (in number of days) between two dates, expr1 and expr2. Date or date-and-time expressions are expr1 and expr2. In the calculation, just the date sections of the data are considered.

Example:

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

DAY() function

DAYOFMONTH is a synonym for DAY() .So there are multiple DAY() functions which are as follow.

DAYNAME() function

DAYNAME(date)

For date, returns the name of the weekday. The value of the time names system variable determines the language used for the name.

mysql> SELECT DAYNAME('2022-02-03');
-> 'Saturday'

DAYOFMONTH() function

DAYOFMONTH(date)

Returns the month day for date, in the range 1 to 31, or 0 for dates having a zero day part, such as ‘0000-00-00’ or ‘2022-00-00’.

Example:

mysql> SELECT DAYOFMONTH('2022-02-03');
-> 3

DAYOFWEEK() function

The weekday index for the given day (1 = Sunday, 2 = Monday,…, 7 = Saturday) is returned. The ODBC standard is followed by these index values.

Example:

mysql> SELECT DAYOFWEEK('2022-02-03');
-> 7

DAYOFYEAR() function

In the range 1 to 366, this function returns the day of the year for the given date.

Example:

mysql> SELECT DAYOFYEAR('2022-02-03');
-> 34

From_days() function

Returns a DATE value given a day number N.

mysql> SELECT FROM_DAYS(730669);
-> '2022-07-03'

Hour() function

For time, it returns the hour. For time-of-day values, the return result ranges from 0 to 23. TIME, on the other hand, has a considerably wider range of values, therefore HOUR can return numbers greater than 23.

mysql> SELECT HOUR('10:05:03');
-> 10
mysql> SELECT HOUR('272:59:59');
-> 272

For time, it returns the hour. For time-of-day values, the return result ranges from 0 to 23. TIME, on the other hand, has a considerably wider range of values, therefore HOUR can return numbers greater than 23.

ADDTIME() function

ADDTIME() delivers the result of adding expr2 to expr1. A time or datetime expression is expr1, and a time expression is expr2.

The return type of this method and the SUBTIME() function has been decided as follows since MySQL 8.0.28:

The return type is TIME if the first input is a dynamic parameter (as in a prepared statement).

Otherwise, the function’s resolved type is obtained from the first argument’s resolved type

Example:

mysql> SELECT ADDTIME('2022-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '2022-01-02 10:10:10.100001'
mysql> SELECT ADDTIME('10:01:01.999999', '02:00:00.999998');
-> '03:00:01.999997'

CURRENT_TIME() function

Depending on whether the function is called in a string or numeric environment, it returns the current time as a value in the ‘hh:mm:ss’ or hhmmss format. The time zone of the session is used to express the value.

The return value contains a fractional seconds component of that many digits if the fsp option is used to indicate a fractional seconds precision from 0 to 6.

Example:

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

CURRENT_TIMESTAMP() function

Depending on whether the function is called in a string or numeric environment, it returns the current time as a value in the ‘hh:mm:ss’ or hhmmss format. The time zone of the session is used to express the value.

CURTIME() function

Depending on whether the function is called in a string or numeric environment, it returns the current time as a value in the ‘hh:mm:ss’ or hhmmss format. The time zone of the session is used to express the value.

Example:

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

last_day() function

Returns the equivalent value for the last day of the month given a date or datetime value.

Example:

mysql> SELECT LAST_DAY('2022-02-05');
-> '2022-02-28'
mysql> SELECT LAST_DAY('2022-02-05');
-> '2022-02-29'
mysql> SELECT LAST_DAY('2022-01-01 01:01:01');
-> '2022-01-31'
mysql> SELECT LAST_DAY('2022-03-32');
-> NULL

localtime() function

NOW is a synonym for LOCALTIME and LOCALTIME() ().

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

localtimestamp() function

Returns a date, year, and day-of-year values for a given year. The value of dayofyear must be larger than 0; else, the result will be NULL.

The current date and time are returned by the LOCALTIMESTAMP() method.

Note that the date and time are returned as a string (YYYY-MM-DD HH-MM-SS) or as YYYYMMDDHHMMSS.

Syntax:

LOCALTIMESTAMP()

Example:

SELECT LOCALTIMESTAMP() + 1;

Output

makedate() function

Create a date based on a year and several days value and return it:

Example:

SELECT MAKEDATE(2022, 3);

maketime() function

Make a time value from an hour, minute, and second value and return it:

SELECT MAKETIME(11, 35, 4);

Output

microsecond() function

Return the part of a datetime that is in microseconds:

Example

minute() function

The minute portion of a datetime value is returned:

Example:

SELECT MINUTE(“2022-06-20 09:34:00”);

Output

month() function

The month part of a date should be returned:

Example:

SELECT MONTH(“2022-06-15”);

Output

monthname() function

For a date, return the month’s name:

SELECT MONTHNAME(“2022-06-15”);

Output

To make a period longer, add a specific number of months.

now() function

Return current date and time:

Output

period_add() function

To make a period longer, add a specific number of months:

SELECT PERIOD_ADD(202203, 15);

Output

period_diff() function

Calculate the difference between the two periods as follows:

Output

For the date, please return the year’s quarter:

quarter() function

For a given date value, the QUARTER() method returns the year’s quarter (a number from 1 to 4).

January-March returns 1

April-June returns 2

July-Sep returns 3

Oct-Dec returns 4

SELECT QUARTER(“2022-06-15”);

Output

sec_to_time() function

Return a time value based on the seconds value specified:

mysql> SELECT SEC_TO_TIME(2378) + 0;

-> 3938

second() function

Return the part of a time value that is in seconds:

SELECT SECOND(“23:59:59”);

Output

str_to_date() function

Get a date using a string and a format:

mysql> SELECT STR_TO_DATE('01,5,2022','%d,%m,%Y');
-> '2022-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2022','%M %d,%Y');
-> '2022-05-01'

Subdate() function

Subtract 10 days from a date and recalculate it as follows:

Subtime() function

Subtract 3 minutes from the time and recalculate:

SELECT SUBTIME(“10:24:21”, “300”);

Output

10:21:21

Sysdate() function

Return the current date and time

Select sysdate();

Output

2022-03-21 02:56:28

time() function

Take the time portion of a time expression and extract it.

Example

SELECT TIME(“19:30:10”);

Output

19:30:10

time_format() function

The TIME FORMAT() method applies a specified format to a time.

Output

time_to_sec() function

Helps in converting time into seconds

SELECT TIME_TO_SEC(“19:30:10”);

Output

timediff() function

Helps in returning difference between two time/datetime expressions

SELECT TIMEDIFF(“2022-06-25 13:10:11”, “2022-06-15 13:10:10”);

Output

timestamp() function

Return a datetime (timestamp) value based on the arguments:

SELECT TIMESTAMP(“2022-07-23”);

Output

to_day() function

Helps in returning days between the date and year

For a given date, return the week number

SELECT TO_DAYS(“2022-06-20”);

weekday() function

For a specific date, get the weekday number:

SELECT WEEKDAY(“2022-01-01”);

Output

week() function

For a given date, return the week number

SELECT WEEK(“2022-06-15”);

Output

weekofyear() function

For a given date, return the week number:

SELECT WEEKOFYEAR(“2022-06-15”);

Output