MySQL Date time function encyclopedia

DAYOFWEEK (date)
The return date is the day of the week (1= Sunday, 2 = Monday,...... 7= Saturday, ODBC Standard)
Mysql> Select DAYOFWEEK (' 1998-02-03 ');
3
WEEKDAY (date)
The return date is the day of the week (0= Monday, 1 = Tuesday,...... 6= Sunday).
Mysql> Select WEEKDAY (' 1997-10-04 22:23:00 ');
5
Mysql> Select WEEKDAY (' 1997-11-05 ');
2
DayOfMonth (date)
Return date is the day of the January (within the range of 1 to 31)
Mysql> Select DayOfMonth (' 1998-02-03 ');
3
DayOfYear (date)
Return date is the day of the year (within the range of 1 to 366)
Mysql> Select DayOfYear (' 1998-02-03 ');
34
MONTH (date)
Returns the month value in date
Mysql> Select MONTH (' 1998-02-03 ');
2
Dayname (date)
Returns the day of the week (returned by English name)
Mysql> Select Dayname ("1998-02-05");
' Thursday '
MONTHNAME (date)
Returns a date that is a few months (returned by English name)
Mysql> Select MONTHNAME ("1998-02-05");
' February '
QUARTER (date)
Returns the first quarter of a year for date
Mysql> Select QUARTER (' 98-04-01 ');
2
WEEK (Date,first)
Return date is the week ordinal of the year (first default 0,first value of 1 indicates that Monday is the beginning of the week, 0 starting from Sunday)
Mysql> Select WEEK (' 1998-02-20 ');
7
Mysql> Select WEEK (' 1998-02-20 ', 0);
7
Mysql> Select WEEK (' 1998-02-20 ', 1);
8
Year (date)
Returns the date of the year (in the range 1000 to 9999)
Mysql> Select year (' 98-02-03 ');
1998
HOUR (Time)
Number of hours to return time (range 0 to 23)
Mysql> Select HOUR (' 10:05:03 ');
10
MINUTE (Time)
Number of minutes to return time (range 0 to 59)
Mysql> Select MINUTE (' 98-02-03 10:05:03 ');
5
SECOND (Time)
Returns the number of seconds in time (range 0 to 59)
Mysql> Select SECOND (' 10:05:03 ');
3
Period_add (P,n)
Add n months to a period p and return (P's format yymm or YYYYMM)
Mysql> Select Period_add (9801,2);
199803
Period_diff (P1,P2)
Returns the number of months between the period P1 and P2 (format yymm or yyyymm of P1 and P2)
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)
Adding and subtracting date time
(Adddate () and subdate () are synonyms of date_add () and date_sub (), or they can be used with operators and-not functions
Date is a datetime or date value in which expr adds and subtracts a date by an expression string type that indicates how the expression expr should be interpreted
[Type value meaning expected expr format]:
SECOND sec SECONDS
MINUTE min MINUTES
HOUR Time HOURS
Day days
Month MONTHS
Year years
Minute_second minutes and seconds "Minutes:seconds"
Hour_minute hours and minutes "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month "Years-months"
Hour_second hours, minutes, "HOURS:MINUTES:SECONDS"
Day_minute day, hour, minute "Days Hours:minutes"
Day_second day, hour, minute, second "days HOURS:MINUTES:SECONDS"
Any punctuation in expr is allowed to be delimited, and if all is a date value the result is a date value, otherwise the result is a datetime value)
If the type keyword is incomplete, then MySQL takes the value from the right end, day_second because the missing hour minute equals Minute_second)
If you increase month, year_month, or year, the maximum number of days is the maximum number of days greater than the result month)
mysql> Select "1997-12-31 23:59:59" INTERVAL 1 SECOND;
-1998-01-01 00:00:00
Mysql> Select INTERVAL 1 Day "1997-12-31";
1998-01-01
mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
-1997-12-31 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL 1 SECOND);
-1998-01-01 00:00:00
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL 1 day);
-1998-01-01 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL "1:1" minute_second);
-1998-01-01 00:01:00
Mysql> Select Date_sub ("1998-01-01 00:00:00", INTERVAL "1 1:1:1" day_second);
-1997-12-30 22:58:59
Mysql> Select Date_add ("1998-01-01 00:00:00", INTERVAL "-1" day_hour);
-1997-12-30 14:00:00
Mysql> Select Date_sub ("1998-01-02", INTERVAL);
1997-12-02
Mysql> Select EXTRACT (Year from "1999-07-02");
1999
Mysql> Select EXTRACT (year_month from "1999-07-02 01:02:03");
199907
Mysql> Select EXTRACT (day_minute from "1999-07-02 01:02:03");
20102
To_days (date)
Return date dates is 00 to present how many days (not calculated before 1582)
Mysql> Select To_days (950501);
728779
Mysql> Select To_days (' 1997-10-07 ');
729669
From_days (N)
Given the number of days 00 to date returns the date value (not calculated until 1582)
Mysql> Select From_days (729669);
' 1997-10-07 '
Date_format (Date,format)
Formatting a date value based on the format string
(a marker is available in the format string:
%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (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 one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
% of percent characters)
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');
' Saturday October 1997 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');
' 22:23:00 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%d%y%a%d%m%b%j ');
4th Sat Oct 277 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%H%k%I%r%T%s%w ');
-10:23:00 PM 22:23:00 00 6 '
Time_format (Time,format)
Similar to Date_format (), but Time_format only handles hours, minutes, and seconds (the remaining symbols produce a null value or 0)
Curdate ()
Current_date ()
Returns the current date value in ' Yyyy-mm-dd ' or YYYYMMDD format (a string or number based on the context in which the return value is located)
Mysql> select Curdate ();
' 1997-12-15 '
Mysql> Select Curdate () 0;
19971215
Curtime ()
Current_time ()
Returns the current time value in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)
Mysql> select Curtime ();
' 23:50:26 '
Mysql> Select Curtime () 0;
235026
Now ()
Sysdate ()
Current_timestamp ()
Returns the current datetime in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select Now ();
' 1997-12-15 23:50:26 '
Mysql> Select Now () 0;
19971215235026
Unix_timestamp ()
Unix_timestamp (date)
Returns a UNIX timestamp (the number of seconds starting from ' 1970-01-01 00:00:00 ' GMT, the date default is the current time)
Mysql> select Unix_timestamp ();
882226357
Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 ');
875996580
From_unixtime (Unix_timestamp)
Returns the timestamp value in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select From_unixtime (875996580);
' 1997-10-04 22:23:00 '
Mysql> Select From_unixtime (875996580) 0;
19971004222300
From_unixtime (Unix_timestamp,format)
Returns the timestamp value as format string
Mysql> Select From_unixtime (Unix_timestamp (), '%Y%d%M%h:%i:%s%x ');
' 1997 23rd December 03:43:30 X '
Sec_to_time (seconds)
A time value that returns the number of seconds in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select Sec_to_time (2378);
' 00:39:38 '
Mysql> Select Sec_to_time (2378) 0;
3938
Time_to_sec (Time)
Returns how many seconds the time value is
Mysql> Select Time_to_sec (' 22:23:00 ');
80580
Mysql> Select Time_to_sec (' 00:39:38 ');
2378