MySQL time date-related functions

2010-11-11  来源:本站原创  分类:Database  人气:108 

Here is an example of using the date function. The following query selects all date_col value in the last 30 days records.

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

DAYOFWEEK (date)
Return date of the week index (1 = Sunday, 2 = Monday, ... 7 = Saturday). Index ODBC-compliant standards.
mysql> SELECT DAYOFWEEK ('1998-02-03 ');
-> 3

WEEKDAY (date)
Return date of the week the index (0 = Monday, 1 = Tuesday, ... 6 = Sunday):
mysql> SELECT WEEKDAY ('1998-02-03 22:23:00 ');
-> 1
mysql> SELECT WEEKDAY ('1997-11-05 ');
-> 2

DAYOFMONTH (date)
Return date is in January, the first few days, range from 1 to 31:
mysql> SELECT DAYOFMONTH ('1998-02-03 ');
-> 3

DAYOFYEAR (date)
Return date is the year of the first few days, range 1 to 366:
mysql> SELECT DAYOFYEAR ('1998-02-03 ');
-> 34

MONTH (date)
Return date in the month, range from 1 to 12:
mysql> SELECT MONTH ('1998-02-03 ');
-> 2

DAYNAME (date)
Return date of the week Name:
mysql> SELECT DAYNAME ("1998-02-05");
-> 'Thursday'

MONTHNAME (date)
Return date of the month names:
mysql> SELECT MONTHNAME ("1998-02-05");
-> 'February'

QUARTER (date)
Return date in the year quarter, range from 1 to 4:
mysql> SELECT QUARTER ('98-04-01 ');
-> 2

WEEK (date)
WEEK (date, first)
Sunday is the week for the first day of the occasion, if only one function call parameters, return date for the first few weeks a year, the return value from 0 to 53 (yes, there may be the beginning of week 53). Two parameters in the form of WEEK () allows you to specify one week whether to start on Sunday or Monday, and the return value is 0-53 or 1-52. Here's a table shows the second parameter is how it works: Value Meaning
0 week to start on Sunday, the return value of the range of 0-53
1 week to Monday, the return value range is 0-53
2 week to start on Sunday, the return value of the range of 1-53
3 week to start on Monday, the return value range is 1-53 (ISO 8601)

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

Note that in version 4.0, WEEK (#, 0) is changed to match the USA calendar. Note that if a week is the final week of the previous year, when you do not have to use 2 or 3 as the optional parameters, MySQL will return 0:
mysql> SELECT YEAR ('2000-01-01 '), WEEK ('2000-01-01', 0);
-> 2000, 0
mysql> SELECT WEEK ('2000-01-01 ', 2);
-> 52

You might argue that, when given a date value is actually week 52 in 1999 when a part of, MySQL for the WEEK () function should return 52. We decided to return 0, because we want the function to return "in a given year in the first few weeks." When combined with other extracts the month value on the date the value function when used in combination, which makes the WEEK () function use and reliable. If you prefer to get the right year - weekly value, then you should use 2 or 3 parameters as optional parameters, or function YEARWEEK ():
mysql> SELECT YEARWEEK ('2000-01-01 ');
-> 199 952
mysql> SELECT MID (YEARWEEK ('2000-01-01 '), 5,2);
-> 52

YEAR (date)
Return date of the year, the range of 1000-9999:
mysql> SELECT YEAR ('98-02-03 ');
-> 1998

YEARWEEK (date)
YEARWEEK (date, first)
Returns a date value which is the year where a week. The second parameter of the form and role of the entirely WEEK () of the second argument. Note that the parameters for a given date is first week of the year or last week, returned year value may be the year of the date given inconsistent parameters:
mysql> SELECT YEARWEEK ('1987-01-01 ');
-> 198 653

Note that for 0 or 1 optional parameter, the value of the return value is different from the week of WEEK () function returns the value (0), WEEK () according to the given value in the context of return-week.
HOUR (time)
Return time of the hour value from 0 to 23:
mysql> SELECT HOUR ('10: 05:03 ');
-> 10

MINUTE (time)
Returns the minutes value of time, ranging from 0 to 59:
mysql> SELECT MINUTE ('98-02-03 10:05:03 ');
-> 5

SECOND (time)
Return time of the second values ​​range from 0 to 59:
mysql> SELECT SECOND ('10: 05:03 ');
-> 3

PERIOD_ADD (P, N)
Add N months to period P (format YYMM or YYYYMM) in. YYYYMM format to return value. Note that during the parameter P is not a date value:
mysql> SELECT PERIOD_ADD (9801,2);
-> 199 803

PERIOD_DIFF (P1, P2)
Return period between P1 and P2 the number of months. P1 and P2 should YYMM or YYYYMM specified. Note that the period arguments P1 and P2 are not date values:
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. ADDDATE () and SUBDATE () are DATE_ADD () and DATE_SUB () synonyms. In MySQL 3.23, if the expression on the right is a date value or a DateTime field, you can use the + and - instead of DATE_ADD () and DATE_SUB () (example below). Argument date is a DATETIME or DATE value, specify a start date. expr is an expression, specify a start date from the interval to add or subtract value. expr is a string; it can be a "-" represents a negative lead interval. type a keyword, it marks the expression in any format is explained. The following table shows how the type and expr arguments are related: type value of expr in the desired format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES: SECONDS"
HOUR_MINUTE "HOURS: MINUTES"
DAY_HOUR "DAYS HOURS"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS: MINUTES: SECONDS"
DAY_MINUTE "DAYS HOURS: MINUTES"
DAY_SECOND "DAYS HOURS: MINUTES: SECONDS"
In the expr format, MySQL allows any character as delimiter. The table shows the proposed delimitation character. If the date argument is a DATE value, and calculate the interval there is only YEAR, MONTH and DAY parts (no time part), then the return value is a DATE value. Otherwise the return value 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

If you specify a value for a short interval (not including the desired type words in all the interval parts), MySQL assumes that you left out the leftmost part of the interval. For example, if you specify a type of DAY_SECOND, then the value of expr is expected to include days, hours, minutes and seconds parts. If you like "1:10" like to specify a value, MySQL hypothesis days and hours parts are missing, the specified value represents minutes and seconds. In other words, "1:10" DAY_SECOND be interpreted as equivalent to "1:10" MINUTE_SECOND. This is similar to MySQL to explain TIME is the elapsed time rather than day time. Note that if by the interval contains a time part of the increase or decrease the value of a date, that date value is automatically converted to a datetime value:

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

If you are using an incorrect date for determining the return result will be NULL. If you add MONTH, YEAR_MONTH or YEAR, and the results of days of the date of the month than the new maximum number of days is also large, then it will be adjusted to the new maximum number of days in January:
mysql> SELECT DATE_ADD ('1998-01-30 ', INTERVAL 1 MONTH);
-> 1998-02-28

Note that the above example, the word INTERVAL and the type keyword are not case-sensitive letters.

EXTRACT (type FROM date)
EXTRACT () function uses the DATE_ADD () or DATE_SUB () the same interval type, but it is used to specify the date extracted from the part, rather than date arithmetic.
mysql> SELECT EXTRACT (YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT (YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199 907
mysql> SELECT EXTRACT (DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102

TO_DAYS (date)
Give a date date, returns a number of days (starting from 0 in the number of days):
mysql> SELECT TO_DAYS (950501);
-> 728 779
mysql> SELECT TO_DAYS ('1997-10-07 ');
-> 729 669

TO_DAYS () do not intend to use before the Gregorian calendar (the current Gregorian calendar) (1582) values ​​appear, because it does not take into account the time lost when the calendar changes in the number of days.

FROM_DAYS (N)
Given a number of days N, returns a DATE value:
mysql> SELECT FROM_DAYS (729669);
-> '1997 -10-07 '

FROM_DAYS () do not intend to use before the Gregorian calendar (1582) values ​​appear, because it does not take into account the time lost when the calendar changes in the number of days.

DATE_FORMAT (date, format)
Accordance with the format string format date values. The following modifiers can be used to format string: modifier meaning
% M month name (January.. December)
% W week of the name (Sunday.. Saturday)
% D English suffix of the first few days of a month (0th, 1st, 2nd, 3rd, etc.)
% Y Year, numeric, 4
% Y year, number, 2
% X weeks of the value of year, Sunday is the first day of a week, numbers, 4, and '% V' together with
% X value of the year week, Monday is first day of a week, numbers, 4, and '% v' used in conjunction
% A abbreviated weekday name (Sun.. Sat)
% D number of days in the month, numeric (00 .. 31)
% E number of days in the month, numeric (0 .. 31)
% M month number (00 .. 12)
% C month, numbers (0 .. 12)
% 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 minute numeric (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 day of week number (0 = Sunday .. 6 = Saturday)
% U Week (00 .. 53), Sunday is the first day of a week
% U Week (00 .. 53), on Monday the first day of a week
% V week (01 .. 53), Sunday is the first day of a week. And '% X' together
% V Week (01 .. 53), Monday is first day of a week. And '% x' together
%% Letter "%"
All other characters without explanation, directly copied 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');
-> '4 Th 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:23:00 PM 22:23:00 00 6 '

mysql> SELECT DATE_FORMAT ('1999-01-01 ','% X% V ');
-> '1998 52 '

In MySQL 3.23, in the format modifier is required before the character `% '. In earlier versions of MySQL, `% 'is optional. Month and day from scratch because the range of modifiers is that in MySQL 3.23, it allows you to store the date value of imperfect (eg '2004-00-00 ').

TIME_FORMAT (time, format)
Its use with the above DATE_FORMAT () function is similar, but the format string contains only deal with hours, minutes and seconds that format modifiers. Use other modifiers will produce a NULL value or 0.

CURDATE ()
CURRENT_DATE
To 'YYYY-MM-DD' or YYYYMMDD format, returns the current date value, depending on the format returned by the function is used for string or numeric context:
mysql> SELECT CURDATE ();
-> '1997 -12-15 '
mysql> SELECT CURDATE () + 0;
-> 19,971,215

CURTIME ()
CURRENT_TIME
To 'HH: MM: SS' or HHMMSS format, returns the current time value, the format returned depends on the function for string or numeric context:
mysql> SELECT CURTIME ();
-> '23: 50:26 '
mysql> SELECT CURTIME () + 0;
-> 235 026

NOW ()
SYSDATE ()
CURRENT_TIMESTAMP
To 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format returns the current date and time values ​​depends on the function returns the format for a string or numeric context:
mysql> SELECT NOW ();
-> '1997 -12-15 23:50:26 '
mysql> SELECT NOW () + 0;
-> 19971215235026

Note that the function NOW () calculated in each query only once, that is, the query begins execution. This means that if a single query in several references NOW (), it will only give the same value is a time.

UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
If called without parameters to the form of an unsigned integer returns a Unix timestamp (from '1970-01-01 00:00:00 'GMT the number of seconds). If a parameter called date UNIX_TIMESTAMP (), it will return the parameter value from '1970-01-01 00:00:00 'GMT after the second start value. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a YYMMDD or YYYYMMDD in local time display:
mysql> SELECT UNIX_TIMESTAMP ();
-> 882 226 357
mysql> SELECT UNIX_TIMESTAMP ('1997-10-04 22:23:00 ');
-> 875 996 580

When UNIX_TIMESTAMP is used for a TIMESTAMP column, the function returns directly to an internal time stamp value, and not to be an implicit "string-to-unix-timestamp" conversion. If you pass a date out of range parameters to UNIX_TIMESTAMP (), it returns 0, but please note that, MySQL is just a basic test of its (in the range 1970-2037, May 01-12, date 01-31). If you want to subtract UNIX_TIMESTAMP () columns, you should need to cast the result to a signed integer. See section 6.3.5 Cast Functions.

FROM_UNIXTIME (unix_timestamp [, format])
To 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format returns a unix_timestamp parameters, return value depends on the function of the form used in the string or numeric context. If the format is given, the return value is formatted according to the format string. format can contain the DATE_FORMAT () function is the same modifier.
mysql> SELECT FROM_UNIXTIME (875996580);
-> '1997 -10-04 22:23:00 '
mysql> SELECT FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (),
'% Y% D% M% h:% i:% s% x');
-> '1997 23rd December 03:43:30 1997 '

SEC_TO_TIME (seconds)
To 'HH: MM: SS' or HHMMSS format return parameter is converted to seconds after the minutes and seconds values, the return value depends on the function used in the form of a string or numeric context:
mysql> SELECT SEC_TO_TIME (2378);
-> '00: 39:38 '
mysql> SELECT SEC_TO_TIME (2378) + 0;
-> 3938

TIME_TO_SEC (time)
Time converted to the parameter number of seconds to return to:
mysql> SELECT TIME_TO_SEC ('22: 23:00 ');
-> 80580
mysql> SELECT TIME_TO_SEC ('00: 39:38 ');
-> 2378

相关文章
  • mysql date time functions and add and subtract functions 2011-05-24

    "MySQL: MySQL date data types, MySQL uses the type of summary of time" for MySQL 5.X and above. A, MySQL access to the current date and time functions 1.1 to obtain the current date + time (date + time) function: now () mysql> select now ();

  • Mysql Format Date / Time 2010-05-12

    Mysql Format Date / Time DATE_FORMAT (date, format) Format the date according to format string values. The following modifiers can be used in the format string:% M month name (January ... ... December) % W week name (Sunday ... ... Saturday) % D Engl

  • IBM DB2 database used to explain the date manipulation functions 2011-01-18

    db2 date manipulation functions Current Time: CURRENT DATE CURRENT TIME CURRENT TIMESTAMP Access time of year, month, day, hours, minutes, seconds and microseconds parts: YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR

  • Mysql database date conversion error 2011-01-21

    Mysql database date conversion error '0000-00-00 00:00:00 ' "Cannot convert value '0000-00-00 00:00:00 'from column 3 to TIMESTAMP." MySQL database if the database field is empty for the Date value of '0000-00-00 ", Hibernate will complain

  • MySQL stored procedures and functions of the difference 2011-06-23

    Transfer: http://www.gooseeker.com/cn/node/Fuller/2010061201 MySQL stored procedures and functions of the difference MySQL stored procedure (stored procedure) and functions (stored function) collectively referred to as stored routines, whether stored

  • Mysql statement date and time formatting method 2011-08-26

    Mysql statement date and time formatting method: select FROM_UNIXTIME(dateline,'%Y-%m-%d %H:%i') as dateline from tp_iteye Format modifier as follows: (Note: Some modifiers do not experiment too, side by side test please, thank you!) According to the

  • MySQL date and time related functions 2010-11-11

    Here is an example of using the date function. The following query selects all date_col values in the last 30 days of the record. mysql> SELECT something FROM tbl_name WHERE TO_DAYS (NOW ()) - TO_DAYS (date_col) <= 30; DAYOFWEEK (date) Back to the i

  • [Change] Mysql collection date and time functions 2010-06-19

    1. 2.DAYOFWEEK (date) 3.WEEKDAY (date) 4.DAYOFMONTH (date) 5.DAYOFYEAR (date) 6.MONTH (date) 7.DAYNAME (date) 8.MONTHNAME (date) 9.QUARTER (date) 10.WEEK (date) WEEK (date, first) 11.YEAR (date) 12.HOUR (time) 13.MINUTE (time) 14.SECOND (time) 15.PER

  • The most complete history of the time and date handling functions MySql 2010-07-24

    Reproduced in [IT168 server Institute ] Here is an example of using the date function. The following query select all the records, the date_col the value is in the last 30 days: mysql> SELECT something FROM table WHERE TO_DAYS (NOW ()) - TO_DAYS (dat

  • mysql data types and functions 2010-04-24

    Integer type int (m) followed by the width of the Float float double fixed-point type dicmal (m, n) m for the scale you for accuracy, is a small number of m-bit n value, he is in the mysql string stored within a few accurate and more used than the fl

  • MySQL query date range or single value query? 2010-11-21

    [Size = medium] [/ size] MySQL version 5.22 used I now want to check out 2010-01-2 2011-02-3 value between the value of stored date is DATE, or formatted String which is good? If there is now a text box that accepts user input the date, the use of St

  • SqlServer time and date handling functions and string conversion 2010-12-03

    Recent statements made by SqlServer2005, learning by doing, even though the speed will be slow ~ Brief Sql Server Date and time functions in 1. The current system date and time select getdate () 2. Dateadd added to the specified date, based on a peri

  • mysql how to learn functions and stored procedures 2011-05-05

    1. Create a stored procedure, use the delimiter $ $ to define a statement terminator, to create complete, restore the statement terminator delimiter; The default due to MySQL ";" as the delimiter, then the process of the body to every word MySQL

  • db2 date manipulation functions 2010-08-31

    Turn: http://hi.baidu.com/% C3% D3% F4% BB% A8% C6% C9% FA% BD% B4/blog/item/9076a6034aba47054bfb51c3.html current time: CURRENT DATE CURRENT TIME CURRENT TIMESTAMP Access time of year, month, day, hours, minutes, seconds and microseconds parts: YEAR

  • Oracle query tree and related functions 2010-11-01

    Oracle query tree, the most important thing is to select ... start with ... connect by ... prior syntax. Relying on the grammar, we can shape the structure of a table in the order listed in the tree. Oracle in the following sets out the common querie

  • Oracle stored procedure summary (two. String processing related functions) 2010-12-22

    Project has involved the handling of the string stored procedures, so will find information on the Internet summary, make a mosaic-style summary of the information. The following information comes from the Internet, posted a save to be after their us

  • mysql commonly used string functions 2011-04-25

    Keywords: mysql terminal; string functions Reference URL: http://blog.sina.com.cn/s/blog_45b088420100bst4.html Reference URL: http://dev.mysql.com/doc/refman/5.1/zh/functions.html # String-functions MySql string functions ASCII(str) Returns a string

  • Oracle stored procedure summary String handling related functions 2011-08-12

    Characters function - returns the character value These functions are all family to receive a parameter of type character (CHR excluded) and returns the character value. Except as noted in addition, most of these functions returns a VARCHAR2 value. T

  • ORACLE - Date Time Functions 2011-09-16

    Daquan TO_DATE format (time :2007 -11-02 13:45:25 for example) Year: yy two digits show the value of two years: 07 yyy three digits show the value of three years: 007 yyyy four digits show the value of four years: 2007 Month: mm number two on display

  • MySQL calculation date or time interval between the two solutions 2010-06-02

    1, using the time function Calculate the interval between two dates, you can use the DATEDIFF () function View the code to the clipboard SELECT DATEDIFF ('2010-06-01 ', '2010-01-01'), DATEDIFF ('2010-01-01 ', '2010-06-01') 151-151 SELECT DATEDIFF('20