Mysql Date and Time Functions

The following query Selects all records whose date_col values are within the last 30 days.

Mysql> SELECT something FROM tbl_name
WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30;
DAYOFWEEK (date)
Returns the week index of date (1 = Sunday, 2 = Monday,... 7 = Saturday ). The index value complies with ODBC standards.
Mysql> select dayofweek ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday,... 6 = Sunday ):
Mysql> select weekday ('2017-02-03 22:23:00 ');
-> 1
Mysql> select weekday ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
The return date is the day of January 1, January, ranging from 1 to 31:
Mysql> select dayofmonth ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the day of the year from 1 to 366:
Mysql> select dayofyear ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month from date, ranging from 1 to 12:
Mysql> select month ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the week name of date:
Mysql> select dayname ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month name of date:
Mysql> select monthname ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of a year in which the date value ranges from 1 to 4:
Mysql> select quarter ('98-04-01 ');
-> 2
WEEK (date)
WEEK (date, first)
For Sunday is the first day of a week, if the function only calls one parameter, return the week of the year for date, and the returned value range is 0 to 53 (yes, may start from 53rd weeks ). The WEEK () parameter allows you to specify whether a WEEK starts on Sunday or Monday, and whether the returned value is 0-53 or 1-52. Here, a table shows how the second parameter works: Value Meaning
0 a week starts on Sunday, and the returned value range is 0-53
1 A week starts on Monday, and the returned value range is 0-53
2 A week starts on Sunday, and the returned value range is 1-53.
3 a week starts on Monday, and the returned value range is 1-53 (ISO 8601)

Mysql> select week ('2017-02-20 ');
-> 7
Mysql> select week ('2017-02-20 ', 0 );
-> 7
Mysql> select week ('2017-02-20 ', 1 );
-> 8
Mysql> select week ('2017-12-31 ', 1 );
-> 53
Note that in version 4.0, WEEK (#, 0) is changed to match the USA calendar. Note: If a week is the last week of the previous year, MySQL returns 0 if you do not use 2 or 3 as an optional parameter:
Mysql> select year ('2014-01-01 '), WEEK ('2014-01-01', 0 );
-> 2000, 0
Mysql> select week ('2017-01-01 ', 2 );
-> 52
You may argue that when the given date value is actually part of WEEK 1999 of 52nd, MySQL should return 52 for the WEEK () function. We decided to return 0 because we wanted the function to return "the week in the specified year ". This makes the use of WEEK () function reliable when used with other functions that extract the values of the month and day from the date value. If you want to get the appropriate year-week value, you should use parameter 2 or 3 as the optional parameter, or use the YEARWEEK () function ():
Mysql> select yearweek ('2017-01-01 ');
-> 199952
Mysql> select mid (YEARWEEK ('2017-01-01 '), 5, 2 );
-> 52
YEAR (date)
Returns the year of date, ranging from 1000 to 9999:
Mysql> select year ('98-02-03 ');
-> 1998
YEARWEEK (date)
YEARWEEK (date, first)
Returns the week of the year of the date value. The form and function of the second parameter are exactly the same as that of the second parameter of WEEK. Note: If the given date parameter is the first week or the last week of the year, the returned year value may be different from the year given by the date parameter:
Mysql> select yearweek ('2017-01-01 ');
-> 198653
Note that for optional parameters 0 or 1, the return value of the weekly value is different from that of the WEEK () function. WEEK () returns the weekly value based on the given yearly context.
HOUR (time)
Returns the hour value of time, ranging from 0 to 23:
Mysql> select hour ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the minute value of time, ranging from 0 to 59:
Mysql> select minute ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
Returns the second value of time, ranging from 0 to 59:
Mysql> select second ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to period P in the format of YYMM or YYYYMM. Return Value in YYYYMM format. Note that the period parameter P is not a date value:
Mysql> SELECT PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between P1 and P2. P1 and P2 should be specified in YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values:
Mysql> SELECT PERIOD_DIFF (9802,199703 );
-> 11
DATE_ADD (date, INTERVAL expr type)
DATE_SUB (date, INTERVAL expr type)
ADDDATE (date, INTERVAL expr type)
SUBDATE (date, INTERVAL expr type)
These functions perform arithmetic operations on dates. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB () respectively. In MySQL 3.23, if the right side of the expression is a date value or a datetime field, you can use + and-to replace DATE_ADD () and DATE_SUB () (example ). The date parameter is a DATETIME or DATE value that specifies the start of a date. Expr is an expression that specifies whether to increase or subtract the interval value from the start date. Expr is a string. It can lead by a "-" to indicate a negative interval value. Type is a keyword that indicates the format in which the expression is interpreted. The following table shows how the type and expr parameters are associated: The expected format of the type value expr
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES: SECONDS"
HOUR_MINUTE "HOURS: MINUTES"
DAY_HOUR "days hours"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS: MINUTES: SECONDS"
DAY_MINUTE "days hours: MINUTES"
DAY_SECOND "days hours: MINUTES: SECONDS"
In the format of expr, MySQL allows any character as the delimiter. The table displays the recommended delimiter. If the date parameter is a DATE value and the calculation interval only includes YEAR, MONTH, and DAY (no time), the return value is also a DATE value. Otherwise, the return value is a DATETIME value:
Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;
-> 00:00:00
Mysql> select interval 1 DAY + "1997-12-31 ";
-> 1998-01-01
Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND;
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1 SECOND );
-> 00:00:00
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1 DAY );
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL "" MINUTE_SECOND );
-> 00:01:00
Mysql> SELECT DATE_SUB ("00:00:00", INTERVAL "1" DAY_SECOND );
-> 1997-12-30 22:58:59
Mysql> SELECT DATE_ADD ("00:00:00", INTERVAL "-1 10" DAY_HOUR );
-> 1997-12-30 14:00:00
Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
-> 1997-12-02
If you specify a too short interval value (not including all the intervals expected by the type keyword), MySQL assumes that you omit the leftmost part of the interval value. For example, if you specify a type of DAY_SECOND, the expr value is expected to include the days, hours, minutes, and seconds. If you specify a value like "", MySQL assumes that the days and hours are missing, and the specified value indicates minutes and seconds. In other words, "" DAY_SECOND is interpreted as equivalent to "" MINUTE_SECOND. This is similar to MySQL's interpretation of the TIME value as the elapsed TIME rather than the TIME of the day. Note: If you increase or decrease a date value according to the interval that contains a time part, the date value will be automatically converted to a date and time value:
Mysql> SELECT DATE_ADD ("maid", INTERVAL 1 DAY );
->
Mysql> SELECT DATE_ADD ("maid", INTERVAL 1 HOUR );
-> 01:00:00
If you use an incorrect date, the returned result is NULL. If you add MONTH, YEAR_MONTH, or YEAR, and the result date is greater than the maximum number of days of the new MONTH, it is adjusted to the maximum number of days of the new MONTH:
Mysql> SELECT DATE_ADD ('2017-01-30 ', INTERVAL 1 MONTH );
-> 1998-02-28
Note: In the above example, the word INTERVAL and keyword type are case-insensitive.

EXTRACT (type FROM date)
The EXTRACT () function uses the same interval type as DATE_ADD () or DATE_SUB (), but it is used to specify the part extracted from the date instead of performing a date arithmetic operation.
Mysql> select extract (year from "maid ");
-> 1999
Mysql> select extract (YEAR_MONTH FROM "01:02:03 ");
-> 199907
Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
-> 20102
TO_DAYS (date)
Returns the number of days (the number of days from 0 years ):
Mysql> SELECT TO_DAYS (950501 );
-> 728779
Mysql> SELECT TO_DAYS ('2017-10-07 ');
-> 729669
TO_DAYS () does not intend to use values that appear before the Gregorian Calendar (that is, the current Gregorian calendar) (1582), because it does not consider the number of days lost when the calendar changes.

FROM_DAYS (N)
Given a number of days N, return a DATE value:
Mysql> SELECT FROM_DAYS (729669 );
-> '2017-10-07'
FROM_DAYS () does not intend to use values that appear before the Gregorian Calendar (1582), because it does not consider the number of days lost when the calendar changes.

DATE_FORMAT (date, format)
Format the date value according to the format string. The following modifier can be used in a format string: modifier meaning
% M month name (January... December)
% W name of the Week (Sunday... Saturday)
% D the day of a month with an English suffix (0th, 1st, 2nd, 3rd, etc .)
% Y year, number, 4 digits
% Y year, number, 2 digits
The year of the % X week value. Sunday is the first day of a week. It is a number and has four digits. It is used with '% V'.
The year of the % x week value. Monday is the first day of a week. It is a number and has four digits. It is used with '% V'.
% A abbreviation of the week name (Sun .. Sat)
% D number of days in the month (00 .. 31)
% E number of days in the month (0 .. 31)
% M month, number (00 .. 12)
% C month, number (0 .. 12)
% B abbreviated month name (Jan .. Dec)
% J days in a year (001 .. 366)
% H hour (00 .. 23)
% K hour (0 .. 23)
% H hour (01 .. 12)
% I hour (01 .. 12)
% L hour (1 .. 12)
% I minutes, numeric (00 .. 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 .. 59)
% S seconds (00 .. 59)
% P AM or PM
% W days in a week (0 = Sunday .. 6 = Saturday)
% U Week (00 .. 53), Sunday is the first day of a week
% U Week (00 .. 53), Monday is the first day of a week
% V Week (01 .. 53). Sunday is the first day of a week. Used with '% x'
% V Week (01 .. 53), Monday is the first day of a week. Used with '% x'
% A letter "%"
All other characters are directly copied to the result without explanation:
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October 1997'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');
-> '22: 23: 00'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'
Mysql> SELECT DATE_FORMAT ('2017-01-01 ',' % X % V ');
-> '2014 52'
In MySQL 3.23, the character '%' is required before the format modifier '. In earlier MySQL versions, '%' is optional. The reason that the range of the month and day modifier starts from scratch is that in MySQL 3.23, it allows the storage of imperfect date values (for example, '2017-00-00 ').

TIME_FORMAT (time, format)
Its usage is similar to the preceding DATE_FORMAT () function, but the format string only contains the format modifiers for processing hours, minutes, and seconds. Other modifiers generate a NULL value or 0.

CURDATE ()
CURRENT_DATE
Returns the current date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context:
Mysql> select curdate ();
-> '2017-12-15'
Mysql> select curdate () + 0;
-> 19971215
CURTIME ()
CURRENT_TIME
Returns the current time value in 'hh: MM: ss' or HHMMSS format. The returned format depends on whether the function is used in a string or a numeric context:
Mysql> select curtime ();
-> '23: 50: 26'
Mysql> select curtime () + 0;
-> 235026
NOW ()
SYSDATE ()
CURRENT_TIMESTAMP
Returns the current datetime value in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:
Mysql> select now ();
-> '2017-12-15 23:50:26'
Mysql> select now () + 0;
-> 19971215235026
Note that the function NOW () is calculated only once in each query, that is, when the query starts to be executed. That is to say, if you reference NOW () multiple times in a separate query, it only gives the same time value.

UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
If there is no parameter during the call, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned in the form of an unsigned integer ). If UNIX_TIMESTAMP () is called with a parameter date, it returns the second value that the value of this parameter has elapsed since '2017-01-01 00:00:00 'GMT. Date can be a DATE string, a datetime string, a TIMESTAMP, or the local time displayed as a YYMMDD or YYYYMMDD:
Mysql> SELECT UNIX_TIMESTAMP ();
-> 882226357
Mysql> SELECT UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function returns an internal timestamp value without performing an implicit conversion of "string-to-unix-TIMESTAMP. If you pass a date parameter out of the range to UNIX_TIMESTAMP (), it returns 0, but note that MySQL only performs a basic test on it (the annual range is 1970-2037, month 01-12, date 01-31 ). If you want to subtract the UNIX_TIMESTAMP () column, you must forcibly convert the result to a signed integer. See Section 6.3.5 Cast function.

FROM_UNIXTIME (unix_timestamp [, format])
Returns a unix_timestamp parameter value in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or a numeric context. If the format is given, the return value is formatted according to the format string. Format can contain the same modifier as the DATE_FORMAT () function.
Mysql> SELECT FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> SELECT FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
Mysql> SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x ');
-> '2014 23rd December 03:43:30 123'
SEC_TO_TIME (seconds)
Return the value of the seconds parameter in 'hh: MM: ss' or HHMMSS format after it is converted to the hour, minute, and second. The returned value depends on whether the function is used in a string or a digital context:
Mysql> SELECT SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> SELECT SEC_TO_TIME (2378) + 0;
-> 3938
TIME_TO_SEC (time)
After converting the time parameter to the number of seconds, return:
Mysql> SELECT TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> SELECT TIME_TO_SEC ('00: 39: 38 ');
-> 2378