MySQL Date time function encyclopedia

Source: Internet
Author: User
Tags month name

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.