This article comes from: http: //linuxdb.yeah.net Author: Yan Zi (2001-07-05 15:00:00)
See 7.3.6 Date and Time Types for each type of value range and and and specify the valid format of how time to date. Here is an example of a date function. The following query selects all records, the value of Date_col is within the last 30 days: mysql> select something from table where to_days (now ()) - to_DAYS (Date_col) <= 30; dayofweek (date) returns the date Date Index (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard. MySQL> Select Dayofweek ('1998-02-03'); -> 3 Weekday (Date) Returns the week index (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) Returns the date of Date, In the range of 1 to 31. MySQL> Select Dayofmonth ('1998-02-03'); -> 3 DayOfyear (date) Returns the number of days in the year in the range of 1 to 366. MySQL> Select Dayofyear ('1998-02-03'); -> 34 MONTH (DATE) Returns the month of Date, range 1 to 12. MySQL> Select Month ('1998-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 in the year, ranging from 1 to 4. MySQL> Select Quarter ('98 -04-01 '); -> 2 Week (Date) Week (Date, First) For Sunday is the first day of the week, there is a single parameter, return Date's weeks, range In 0 to 52.2 parameter forms Week () allows you to specify whether we start on Sunday or Monday. If the second parameter is 0, the week starts from Sunday, if the second parameter is 1, from Monday. 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 year of Date, ranges from 1,000 to 9999. MySQL> SELECT YEAR ('98 -02-03 '); -> 1998 Hour (TIME) Returns the hour of Time, the range is 0 to 23.
MySQL> Select Hour ('10: 05: 03 '); -> 10 Minute (TIME) Returns the minute of Time, the range is 0 to 59. MySQL> SELECT Minute ('98 -02-03 10:05:03 "); -> 5 Second (Time) back to Time's second number, the range is 0 to 59. MySQL> Select Second ('10: 05: 03'); -> 3 Period_ADD (p, n) Increase N month-to-phase P (in format yymm or yyyymm). Return the value in format YYYYMM. Attention stage 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 format YYMM or YYYYMM. Note that the parameters P1 and P2 are not a date value. 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 date arithmetic . For MySQL 3.22, they are new. Adddate () and subdate () are synonyms for DATE_ADD () and DATE_SUB (). In Mysql 3.23, you can use and - instead of DATE_ADD () and Date_Sub (). (See example) Date is a DateTime or Date value specified start date. Expr is an expression that specifies an expression that is added to the start date or subtracts from the start date. EXPR is a string; it can start with a "-" start Indicates the negative interval. TYPE is a keyword that indicates how the expression should be interpreted. The Extract (Type from date) function returns "Type" interval from the date. The following table shows how the Type and EXPR parameters are associated: TYPE Value Meaning Exprical EXPR Format SECOND Seconds Minute Minute Minutes Hour Time Hours Year Day Days Month Months Year Year Years Minute_Second Minutes and Second "Minutes: Seconds" Hour_minute hours and minutes "Hours: minutes" Day_HOUR TAY and Hours "Days Hours" Year_Month and Month "Years-Months" Hour_second Hours, Minute, "Hours: Minutes: Seconds" Day_Minute Day, Hour, Minute "Days Hours: Minutes" Day_Second Day, hour , Minutes, second "Days Hours: minutes: seconds" MySQL allows any punctuation separator in the EXPR format. Indicates that it is a suggested separator. If the Date parameter is a Date value and your calculation only contains Year, Month, and a DAY section (ie, no time part), the result is a DATE value. Otherwise, the result is a DateTime value.
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 10" day_HOUR); -> 1997-12-30 14:00:00 mysql> select date_sub ("1998-01-02", Interval 31 day); -> 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 If you specify too short intervals (excluding Type Keys The spacing portion of the word expectation), MySQL assumes that you save the leftmost part of the interval value. For example, if you specify a Type is day_second, the value expr is desirable to have the day, hours, minutes, and seconds. If you specify a value like "1:10", MySQL assumes that the day and the hour portion is lost and the value represents minutes and seconds. In other words, "1:10" day_second explained to its equivalent "1:10" minute_second, which interprets the time value indicating the time of the TIME value indicates that the time is not as a day. If you use the index true date, the result is NULL. If you increase Month, Year_Month or Year and the result dates greater than the maximum number of days in the new month, the days are adjusted in the new month. MySQL> SELECT DATE_ADD ('1998-01-30', Interval 1 Month); -> 1998-02-28 Note that the word Interval and Type keywords from the previous example are not case sensitive.
TO_DAYS (Date) gives a date date and returns a number of days (from 0 years old). MySQL> SELECT TO_DAYS (950501); -> 728779 MySQL> SELECT TO_DAYS ('1997-10-07'); -> 729669 TO_DAYS () is not intended to use the value of the Gliel High Reline (1582). From_DAYS (n) gives a number of days n and returns a DATE value. MySQL> SELECT from_DAYS (729669); -> '1997-10-07' to_DAYS () is not intended to use the value of the Glue High Reline (1582). Date_Format (Date, Format) is formatted according to the Format string formatted. The following modifiers can be used in the Format string:% M month name (January ...... decEmber)% W weeks name (Sunday ... Saturday)% D Date with English prefix (1st, 2nd, 3rd, etc. .)% Y years, numbers, 4-bit% Y years, numbers, 2-bit% a abbreviated week name (Sun ... SAT)% D the number of days in the month, number (00 ... 31)% of the days in the month , Digital (0 ... 31)% M month, number (01 ... 12)%, Mik, Number (1 ... 12)% B abbreviated month name (Jan ... DEC)% J A year in the year ( 001 ... 366)% H hours (00 ... 23)% k hours (0 ... 23)% H hours (01 ... 12)% i hour (01 ... 12)% l hours (1 ... 12) % i minute, digital (00 ... 59)% R Time, 12 hours (HH: mm: SS [AP] M)% T Time, 24 hours (HH: mm: SS)% s second (00 ... 59) % s second (00 ... 59)% P AM or PM% W One week (0 = sunday ... 6 = SATURDAY)% u Week (0 ... 52), here Sunday is the first day of the week u Week (0 ... 52), Monday is the first day %% of the week "%". All other characters are not explained to the result. 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 97 SAT 04 10 OCT 277 'MySQL> SELECT DATE_FORMAT (' 1997-10-04 22:23:00 ','% H% K% I% R% T % S% w '); -> '22 22 10 10:00 00 6' mysql3.23, it is required to be% before the format modifier character. In the earliest version of MySQL,% is optional. Time_Format (Time, Format) is used as the DATE_FORMAT () function above, but the Format string can only contain those format modifiers that process hours, minutes, and seconds. Other modifiers produce a NULL value or 0.
CURDATE () CURRENT_DATE Returns today's date value with 'YYYY-MM-DD' or YYYMMDD format, depending on the function is used in a string or digital context. 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, depending on The function is used in a string or on a number of numbers. MySQL> Select Curtime (); -> '23: 50: 26 'mysql> select curtime () 0; -> 235026 now () sysdate () current_timestamp with' YYYY-MM-DD HH: mm: ss' or YYYYMMDDHMMMSS The format returns the current date and time, depending on the function is used in a string or on the digital context. MySQL> Select now (); -> '1997-12-15 23:50:26' mysql> select now () 0; -> 19971215235026 Unix_timestamp () Unix_TimeStamp (Date) Returns a Unix Time Stamp if there is no parameter call (From '1970-01-01 00: 00: 00' GMT). If unix_timestamp () is called with a DATE parameter, it returns the second value starting from '1970-01 00:00:00' GMT. Date can be a Date character string, a DateTime string, a TimeStamp or a number in the local time in YYMMDD or YYYYMMDD format. MySQL> SELECT UNIX_TIMESTAMP (); -> 882226357 MySQL> Select Unix_TimeStamp ('1997-10-04 22:23:00'); -> 875996580 When UNIX_TimeStamp is used for a TimeStAMP column, the function will directly accept the value, no implicit "String-to-Unix-TimeStamp" transformation. From_unixtime (unix_timestamp) Returns the value represented by the UNIX_TimeStamp parameter in 'YYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, depending on the function is used in a string or or digital context. mysql> select FROM_UNIXTIME (875996580); -> '1997-10-04 22:23:00' mysql> select FROM_UNIXTIME (875996580) 0; -> 19971004222300 FROM_UNIXTIME (unix_timestamp, format) Returns a string Unix time stamp , Format it according to the Format string. The Format can contain the same modifiers that are listed with the DATE_FORMAT () function.
