Some examples using more than one date function:
Select all record with a date_field from the last 30 days.
SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_field) <= 30;
A Date expression may be a date string, a datetime string, a
timestamp([6 | 8 | 14]) or a number of format YYMMDD or YYYYMMDD.
In a date expression a year may be 2 or 4 digits. 2 digits is assumed to be in the range 1970-2069. Dates 100-199 is converted to 2000-2999 to make year arithmetic easier! The special date '0000-00-00' can be stored and retrieved as 0000-00-00.
If you use a date function with a number, then if the length of the number is 4, 8 or >= 14 then the year is assumed to have 4 digits. In all other cases the year is assumed to be the 2 first digits in the given number. To be on the safe side when using dates as numbers (not strings) one should always use 4 digit dates! If not you will get in trouble with year 2000 when a number 002001 is sent to the date functions as '2001' instead of the date '20002001'. '002001' will of course work correctly!
A Time expression may be a date string, a datetime string, a
timestamp([6 | 8 | 14]) or a number of format HHMMSS
or YYYYMMDDHHMMSS.
DAYOFWEEK(date expr)
Date (1 = Sunday, 2 = Monday, 2 = Tuesday ..)
This is according to the ODBC standard.
mysql> select dayofweek('1998-02-03'); -> 3
WEEKDAY(date expr)
Date (0 = Monday, 1 = Tuesday ..)
mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5
mysql> select WEEKDAY('1997-11-05'); -> 2
DAYOFMONTH(date expr)
mysql> select DAYOFMONTH('1998-02-03'); -> 3
DAYOFYEAR(date expr)
mysql> select DAYOFYEAR('1998-02-03'); -> 34
MONTH(date expr)
mysql> select MONTH('1998-02-03'); -> 02
DAYNAME(date expr)
mysql> select dayname("1998-02-05"); -> Thursday
MONTHNAME(date expr)
mysql> select monthname("1998-02-05"); -> February
QUARTER(date expr)
mysql> select QUARTER('98-04-01'); -> 2
WEEK(date expr)
mysql> select WEEK('98-02-20'); -> 7
YEAR(date expr)
mysql> select YEAR('98-02-03'); -> 1998
HOUR(time expr)
mysql> select HOUR('10:05:03'); -> 10
MINUTE(time expr)
mysql> select MINUTE('98-02-03 10:05:03'); -> 5
SECOND(time expr)
mysql> select SECOND('10:05:03'); -> 3
PERIOD_ADD(P, N)
N months to period P (of type YYMM or
YYYYMM). Returns YYYYMM.
mysql> select PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(A, B)
A and B. A and
B should be of format YYMM or YYYYMM.
mysql> select PERIOD_DIFF(9802,199703); -> 11
TO_DAYS(Date)
Date to a daynumber (Number of days since year
0). Date may be a DATE string, a DATETIME string, a
TIMESTAMP([6 | 8 | 14]) or a number of format YYMMDD or
YYYYMMDD.
mysql> select TO_DAYS(9505); -> 733364
mysql> select TO_DAYS('1997-10-07); -> 729669
FROM_DAYS()
mysql> select from_days(729669); -> 1997-10-07
DATE_FORMAT(Date, Format)
Date (a date or a timestamp) according to the
Format string. The following format commands are known:
| M | Month name |
| W | Weekday name |
| D | Day of the month with english suffix |
| Y | Year with 4 digits |
| y | Year with 2 digits |
| a | Abbreviated weekday name (Sun..Sat) |
| d | Day of the month, numeric |
| m | Month, numeric |
| b | Abbreviated month name (Jan.Dec) |
| j | Day of 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 |
| r | Time, 12-hour (hh:mm:ss [AP]M) |
| T | Time, 24-hour (hh:mm:ss) |
| S | Seconds (00..59) |
| s | Seconds (00..59) |
| p | AM or PM |
| w | Day of the week (0=Sunday..) |
| % | single % are ignored. Use %% for a % (for future extensions). |
mysql> select date_format('1997-10-04 22:23:00', '%W %M %Y %h:%i:%s');
-> 'Saturday October 1997 22:23:00'
mysql> select date_format('1997-10-04 22:23:00', '%D %y %a %d %m %b %j %H %k %I %r %T %S %w');
-> '4th 97 Sat 04 10 Oct 277 22 22 10 10:23:00 PM 22:23:00 00 6'
For the moment % is optional. In future versions of MySQL
% will be required.
YYYYMMDD or 'YYYY-MM-DD'
depending on whether CURDATE() is used in a number or string
context.
mysql> select CURDATE(); -> '1997-12-15' mysql> select CURDATE()+0; -> 19971215
HHMMSS or 'HH:MM:SS',
depending on whether CURTIME() is used in a number or string
context.
mysql> select CURTIME(); -> '23:50:20' mysql> select CURTIME()+0; -> 235026
YYYYMMDDHHMMSS or
'YYYY-MM-DD HH:MM:SS' depending on whether NOW() is used
in a number or string context.
mysql> select NOW(); -> '1997-12-15 23:51:26' mysql> select NOW()+0; -> 19971215235131
TIMESTAMP column
as an argument in which case it returns the columns value in seconds.
Date may also be a date string, a datetime string, or a number of
format YYMMDD or YYYMMDD in local time.
mysql> select UNIX_TIMESTAMP(); -> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
YYYY-MM-DD HH:MM:SS or
YYYYMMDDHHMMSS format depending on context (numeric/string).
mysql> select FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00'
M | Month, textual |
W | Day (of the week), textual |
D | Day (of the month), numeric plus english suffix |
Y | Year, numeric, 4 digits |
y | Year, numeric, 2 digits |
m | Month, numeric |
d | Day (of the month), numeric |
h | Hour, numeric |
i | Minutes, numeric |
s | Seconds, numeric |
w | Day (of the week), numeric |
| All other | All other characters are just copied. |
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), 'Y D M h:m:s x');
-> '1997 23rd December 03:12:30 x'
H:MM:SS
or HMMSS format depending on context.
mysql> select SEC_TO_TIME(2378); -> '00:39:38' mysql> select SEC_TO_TIME(2378)+0; -> 3938
Time to seconds.
mysql> select TIME_TO_SEC('22:23:00'); -> 80580
mysql> select TIME_TO_SEC('00:39:38'); -> 2378
Go to the first, previous, next, last section, table of contents.