Working with date and time in MySQL + PHP

Below is an example that uses date functions. The above query selects all records with the date_col value within the last 30 days:

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK (date) - Returns the index of the day of the week for the argument date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values ​​correspond to the ODBC standard.

SELECT DAYOFWEEK('1998-02-03'); // -> 3

WEEKDAY (date) - Returns the index of the day of the week for the argument date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):

SELECT WEEKDAY('1998-02-03 22:23:00'); // -> 1
SELECT WEEKDAY('1997-11-05'); // -> 2

DAYOFMONTH (date) - Returns the ordinal number of the day of the month for the date argument in the range from 1 to 31:

SELECT DAYOFMONTH('1998-02-03'); // -> 3

DAYOFYEAR (date) - Returns the ordinal number of the year of the year for the date argument in the range from 1 to 366:

SELECT DAYOFYEAR('1998-02-03'); // -> 34

MONTH (date) - Returns the month ordinal of the year for the date argument in the range from 1 to 12:

SELECT MONTH('1998-02-03'); // -> 2

DAYNAME (date) - Returns the name of the day of the week for the date argument:

SELECT DAYNAME("1998-02-05"); // -> 'Thursday'

MONTHNAME (date) - Returns the name of the month for the date argument:

SELECT MONTHNAME("1998-02-05"); // -> 'February'

QUARTER (date) - Returns the quarter number of the year for the date argument in the range from 1 to 4:

SELECT QUARTER('98-04-01'); // -> 2

WEEK (date), WEEK (date, first) - If there is one argument, returns the week ordinal of the year for date in the range from 0 to 53 (yes, perhaps the beginning of the 53rd week) for regions where Sunday is considered the first day of the week. The WEEK () form with two arguments allows you to specify from which day the week starts - from Sunday or from Monday. The result will be in the range 0-53 or 1-52.

Here's how the second argument works: 
0 - Week starts on Sunday; the return value is in the range 0-53 
1 - Week starts on Monday; the return value is in the range 0-53 
2 - Week starts on Sunday; the return value is in the range 1-53 
3 - Week starts on Monday; the return value is in the range 1-53 (ISO 8601)

SELECT WEEK('1998-02-20'); // -> 7
SELECT WEEK('1998-02-20',0); // -> 7
SELECT WEEK('1998-02-20',1); // -> 8
SELECT WEEK('1998-12-31',1); // -> 53

Note: in version 4.0 WEEK (#, 0) function has been changed to meet the US calendar. 
Note, if the week is the last week of last year, MySQL will return 0 if you did not specify 2 or 3 as an optional argument:

SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); // -> 2000, 0
SELECT WEEK('2000-01-01',2); // -> 52

We can assume that MySQL should return 52, since this date is the 52nd week of the year 1999. We decided to return 0, since we want the function to give "the week number in the specified year". This makes the WEEK () function more reliable when used in conjunction with other functions that compute parts of dates.

If you still need to clarify the correct week in a year, then you can use 2 or 3 as an optional argument or use YEARWEEK ()

SELECT YEARWEEK('2000-01-01'); // -> 199952
SELECT MID(YEARWEEK('2000-01-01'),5,2); // -> 52

YEAR (date) - Returns the year for the date argument in the range from 1000 to 9999:

SELECT YEAR('98-02-03'); // -> 1998

YEARWEEK (date), YEARWEEK (date, first) - Returns the year and week for the date argument. The second argument in this function works like the second argument in the WEEK () function. Note that the year may differ from the date specified in the date argument for the first and last weeks of the year:

SELECT YEARWEEK('1987-01-01'); // -> 198653

Note that the week number is different from the one returned by the WEEK () (0) function, being called with the optional argument 0 or 1. This is because WEEK () returns the week number in the specified year. 

HOUR (time) - Returns the hour for the time argument in the range from 0 to 23:

SELECT HOUR('10:05:03'); //-> 10

MINUTE (time) - Returns the number of minutes for the time argument in the range 0 to 59:

SELECT MINUTE('98-02-03 10:05:03'); // -> 5

SECOND (time) - Returns the number of seconds for the time argument in the range from 0 to 59:

SELECT SECOND('10:05:03'); // -> 3

PERIOD_ADD (P, N) - Adds N months to period P (in YYMM or YYYYMM format). Returns the value in YYYYMM format. Note that the argument of period P is not a date value:

SELECT PERIOD_ADD(9801,2); // -> 199803

PERIOD_DIFF (P1, P2) - Returns the number of months between periods P1 and P2. P1 and P2 must be in YYMM or YYYYMM format. Note that the arguments of period P1 and P2 are not date values:

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. Both are a new version of MySQL 3.22. The ADDDATE () and SUBDATE () functions are synonyms for DATE_ADD () and DATE_SUB (). In MySQL version 3.23, instead of the DATE_ADD () and DATE_SUB () functions, you can use the + and - operators if the expression on the right side is a column of type DATE or DATETIME (see the example below). The date argument is a value of type DATETIME or DATE, which specifies the start date.

The expression expr specifies the amount of the interval to be added to the start date or subtracted from the start date. The expression expr is a string that can begin with - for negative values ​​of intervals. The type keyword shows how to interpret this expression. The auxiliary function EXTRACT (type FROM date) returns the interval of the specified type (type) from the date value. The following table shows the relationship between type and expr:

SECOND, - SECONDS 
MINUTE - MINUTES 
HOUR - HOURS 
DAY - DAYS 
on MONTH - in MONTHS 
the YEAR - YEARS 
MINUTE_SECOND - "MINUTES: SECONDS" 
HOUR_MINUTE - "HOURS: MINUTES" 
DAY_HOUR - "DAYS HOURS" 
YEAR_MONTH - "YEARS-in MONTHS" 
HOUR_SECOND - "HOURS: MINUTES: SECONDS " 
DAY_MINUTE -" DAYS HOURS: MINUTES " 
DAY_SECOND -" DAYS HOURS: MINUTES: SECONDS "

In MySQL, the format of the expr expression allows any separator characters. The separators shown in this table are given as examples. If the date argument is a DATE type value and the supposed calculations include only the parts YEAR, MONTH, and DAY (ie do not contain the time part of TIME), the result is represented as a value of type DATE. In other cases, the result is the DATETIME value:

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; // -> 1998-01-01 00:00:00
SELECT INTERVAL 1 DAY + "1997-12-31"; // -> 1998-01-01
SELECT "1998-01-01" - INTERVAL 1 SECOND; // -> 1997-12-31 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); // -> 1998-01-01 00:00:00
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY); // -> 1998-01-01 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); // -> 1998-01-01 00:01:00
SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND); // -> 1997-12-30 22:58:59
SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); // -> 1997-12-30 14:00:00
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); // -> 1997-12-02

If the specified interval is too short (i.e., does not include all parts of the interval expected with the specified keyword type), then MySQL assumes that the leftmost parts of the interval are omitted. For example, if the argument type is specified as DAY_SECOND, then the expected expression expr should have the following parts: days, hours, minutes and seconds. If in this case you specify the value of the interval as "1:10", MySQL assumes that the days and hours are omitted, and this value includes only minutes and seconds. In other words, the combination "1:10" DAY_SECOND is interpreted as the equivalent of "1:10" MINUTE_SECOND. Similarly, MySQL interprets TIME values ​​- more as representing the elapsed time than as the time of day. Should be considered,

SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY); // -> 1999-01-02
SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR); // -> 1999-01-01 01:00:00

If you use incorrect date values, the result will be NULL. If, when summing MONTH, YEAR_MONTH, or YEAR, the day number in the resulting date exceeds the maximum number of days in the new month, then the day of the resulting date is taken as the last day of the new month:

SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH); // -> 1998-02-28

From the previous example, we see that the word INTERVAL and the type keyword are not case-sensitive.

EXTRACT (type FROM date) - The interval types for the EXTRACT () function are the same as for the DATE_ADD () or DATE_SUB () functions, but EXTRACT () extracts the part from the date value rather than performing arithmetic operations.

SELECT EXTRACT(YEAR FROM "1999-07-02"); // -> 1999
SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); // -> 199907
SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); // -> 20102

TO_DAYS (date) - function returns the day number for the date specified in the date argument (number of days since year 0):

SELECT TO_DAYS(950501); // -> 728779
SELECT TO_DAYS('1997-10-07'); // -> 729669

The TO_DAYS () function is not intended to be used with values ​​preceding the introduction of the Gregorian calendar (1582), since it does not take into account the days lost when the calendar is changed.

FROM_DAYS (N) - Returns the DATE value for the given day number N:

SELECT FROM_DAYS(729669); // -> '1997-10-07'

The FROM_DAYS () function is not intended for use with values ​​preceding the introduction of the Gregorian calendar (1582), since it does not take into account the days lost when the calendar is changed.

DATE_FORMAT (date, format) - Format the date value according to the format string. In the format string, the following qualifiers can be used:% M The name of the month (January ... December) 
% W The name of the day of the week (Sunday ... Saturday) 
% D The day of the month with the English suffix (0st, 1st, 2nd, 3rd, etc .) 
% Y Year, date, 4 digits 
% y Year, date, 2 digits 
% X Year for the week where Sunday is the first day of the week, number, 4 digits, used with '% V' 
% x Year for the week where Sunday is the first day of the week, the number is 4 digits, is used with '% v' 
% a Abbreviated name of the day of the week (Sun ... Sat) 
% d Day of the month, day (00..31) 
% e Day of the month, number (0. .31) 
% m Month, number (00.1 2)
% c Month, number (0..12) 
% b Abbreviated month name (Jan ... Dec) 
% j Day of the 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, number (00..59) 
% r Time, 12 hours format (hh: mm: ss [AP] M) 
% T Time, 24h format (hh: mm: ss) 
% S Seconds (00..59) 
% s Seconds (00..59) 
% p AM or PM 
% w Day of the week (0 = 
Sunday..6 = Saturday) % U Week (00..53), where Sunday is considered the first day of the week 
% u Week (00..53), where Monday is the first day of the week 
% V Week (01..53 ), where Sunday is considered the first day of the week. Used with '% X'
% v Week (01..53), where Monday is the first day of the week. Used with '% x' 
%% Literals '%'.

All other symbols are simply copied into the resulting expression without interpretation:

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); // -> 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); // -> '22:23:00'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); // -> '4th 97 Sat 04 10 Oct 277'
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'
SELECT DATE_FORMAT('1999-01-01', '%X %V'); // -> '1998 52'
SELECT DATE_FORMAT(sale.time, '%Y-%m-%d') as dat // 2014-03-11

In MySQL 3.23, the character '%' must precede the symbols of the format identifier. In earlier versions of MySQL, the character '%' is optional.

The reason that the intervals for the month and day start from zero is that MySQL allows you to use incomplete dates, such as '2004-00-00', starting with MySQL 3.23.

TIME_FORMAT (time, format) - This function is used similarly to the function DATE_FORMAT () described above, but the format string can only contain format specifiers that refer to hours, minutes and seconds. When specifying other determinants, the value NULL or 0 will be returned.

CURDATE () , CURRENT_DATE - Returns today's date as a value in the format YYYY-MM-DD or YYYYMMDD, depending on the context in which the function is used - in a string or numeric:

SELECT CURDATE(); // -> '1997-12-15'
SELECT CURDATE() + 0; //-> 19971215

CURTIME (), CURRENT_TIME - Returns the current time as a value in the format HH: MM: SS or HHMMS, depending on the context in which the function is used - in a string or numeric:

SELECT CURTIME(); // -> '23:50:26'
SELECT CURTIME() + 0; // -> 235026

NOW (), SYSDATE (), CURRENT_TIMESTAMP - Returns the current date and time as a value in the format YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric:

SELECT NOW(); // -> '1997-12-15 23:50:26'
SELECT NOW() + 0; // -> 19971215235026

Note that NOW () is evaluated only once for each request, namely, at the beginning of its execution. This allows you to be sure that multiple links to NOW () within the same query will give the same value.

UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date) - When this function is called without an argument, it returns the UNIX_TIMESTAMP timestamp (seconds from 1970-01-01 00:00:00 GMT) as an unsigned integer. If the UNIX_TIMESTAMP () function is called with the date argument, it returns the argument value as the number of seconds from 1970-01-01 00:00:00 GMT. The date argument can be a DATE, a DATETIME, a TIMESTAMP type, or a YYMMDD or YYYYMMDD local time:

SELECT UNIX_TIMESTAMP(); // -> 882226357
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); // -> 875996580

When using the UNIX_TIMESTAMP function in the TIMESTAMP column, this function will return the value of the internal timestamp directly, without implicit conversion of the string to a timestamp (`` string-to-unix-timestamp ''). If the specified date is outside the allowed range, the UNIX_TIMESTAMP () function will return 0, but note that only the basic check is performed (year 1970-2037, month 01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP () columns, you can convert the result to signed integers. See Section 6.3.5, "Type casting functions".

FROM_UNIXTIME (unix_timestamp) - Returns the representation of the unix_timestamp argument as a value in the format YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric:

SELECT FROM_UNIXTIME(875996580); // -> '1997-10-04 22:23:00'
SELECT FROM_UNIXTIME(875996580) + 0; // -> 19971004222300

FROM_UNIXTIME (unix_timestamp, format) - Returns the string representation of the unix_timestamp argument, formatted according to the format string. The format string can contain the same qualifiers that are listed in the description for the function DATE_FORMAT ():

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); // -> '1997 23rd December 03:43:30 1997'

SEC_TO_TIME (seconds) - Returns the seconds argument, converted to hours, minutes and seconds, as a value in the format HH: MM: SS or HHMMSS, depending on whether the function is used in a string or numeric:

SELECT SEC_TO_TIME(2378); // -> '00:39:38'
SELECT SEC_TO_TIME(2378) + 0; // -> 3938

TIME_TO_SEC (time) - Returns the time argument converted to seconds:

SELECT TIME_TO_SEC('22:23:00'); // -> 80580
SELECT TIME_TO_SEC('00:39:38'); // -> 2378