{"id":158,"date":"2011-12-27T16:10:31","date_gmt":"2011-12-27T16:10:31","guid":{"rendered":"https:\/\/shan.info\/new\/mysql-%e6%97%a5%e6%9c%9f%e8%bd%ac%e6%8d%a2%e6%98%9f%e6%9c%9f\/"},"modified":"2011-12-27T16:10:31","modified_gmt":"2011-12-27T16:10:31","slug":"mysql-%e6%97%a5%e6%9c%9f%e8%bd%ac%e6%8d%a2%e6%98%9f%e6%9c%9f","status":"publish","type":"post","link":"https:\/\/shan.info\/new\/mysql-%e6%97%a5%e6%9c%9f%e8%bd%ac%e6%8d%a2%e6%98%9f%e6%9c%9f\/","title":{"rendered":"Mysql \u65e5\u671f\u8f6c\u6362\u661f\u671f"},"content":{"rendered":"<p>{codecitation class=&#8221;brush:sql&#8221; width=&#8221;500px&#8221;}<\/p>\n<p>mysql&gt; SELECT something FROM table <br \/>WHERE TO_DAYS(NOW()) &#8211; TO_DAYS(date_col) &lt;= 30; <\/p>\n<p>DAYOFWEEK(date) <br \/>\u8fd4\u56de\u65e5\u671fdate\u7684\u661f\u671f\u7d22\u5f15(1=\u661f\u671f\u5929\uff0c2=\u661f\u671f\u4e00, \u2026\u20267=\u661f\u671f\u516d)\u3002\u8fd9\u4e9b\u7d22\u5f15\u503c\u5bf9\u5e94\u4e8eODBC\u6807\u51c6\u3002 <br \/>mysql&gt; select DAYOFWEEK(&#8216;1998-02-03&#8217;); <br \/>-&gt; 3 <\/p>\n<p>WEEKDAY(date) <br \/>\u8fd4\u56dedate\u7684\u661f\u671f\u7d22\u5f15(0=\u661f\u671f\u4e00\uff0c1=\u661f\u671f\u4e8c, \u2026\u20266= \u661f\u671f\u5929)\u3002 <br \/>mysql&gt; select WEEKDAY(&#8216;1997-10-04 22:23:00&#8217;); <br \/>-&gt; 5 <br \/>mysql&gt; select WEEKDAY(&#8216;1997-11-05&#8217;); <br \/>-&gt; 2 <\/p>\n<p>DAYOFMONTH(date) <br \/>\u8fd4\u56dedate\u7684\u6708\u4efd\u4e2d\u65e5\u671f\uff0c\u57281\u523031\u8303\u56f4\u5185\u3002 <br \/>mysql&gt; select DAYOFMONTH(&#8216;1998-02-03&#8217;); <br \/>-&gt; 3 <\/p>\n<p>DAYOFYEAR(date) <br \/>\u8fd4\u56dedate\u5728\u4e00\u5e74\u4e2d\u7684\u65e5\u6570, \u57281\u5230366\u8303\u56f4\u5185\u3002 <br \/>mysql&gt; select DAYOFYEAR(&#8216;1998-02-03&#8217;); <br \/>-&gt; 34 <\/p>\n<p>MONTH(date) <br \/>\u8fd4\u56dedate\u7684\u6708\u4efd\uff0c\u8303\u56f41\u523012\u3002 <br \/>mysql&gt; select MONTH(&#8216;1998-02-03&#8217;); <br \/>-&gt; 2 <\/p>\n<p>DAYNAME(date) <br \/>\u8fd4\u56dedate\u7684\u661f\u671f\u540d\u5b57\u3002 <br \/>mysql&gt; select DAYNAME(&#8220;1998-02-05&#8221;); <br \/>-&gt; &#8216;Thursday&#8217; <\/p>\n<p>MONTHNAME(date) <br \/>\u8fd4\u56dedate\u7684\u6708\u4efd\u540d\u5b57\u3002 <br \/>mysql&gt; select MONTHNAME(&#8220;1998-02-05&#8221;); <br \/>-&gt; &#8216;February&#8217; <\/p>\n<p>QUARTER(date) <br \/>\u8fd4\u56dedate\u4e00\u5e74\u4e2d\u7684\u5b63\u5ea6\uff0c\u8303\u56f41\u52304\u3002 <br \/>mysql&gt; select QUARTER(&#8217;98-04-01&#8242;); <br \/>-&gt; 2 <\/p>\n<p>WEEK(date) <\/p>\n<p>WEEK(date,first) <br \/>\u5bf9\u4e8e\u661f\u671f\u5929\u662f\u4e00\u5468\u7684\u7b2c\u4e00\u5929\u7684\u5730\u65b9\uff0c\u6709\u4e00\u4e2a\u5355\u4e2a\u53c2\u6570\uff0c\u8fd4\u56dedate\u7684\u5468\u6570\uff0c\u8303\u56f4\u57280\u523052\u30022\u4e2a\u53c2\u6570\u5f62\u5f0fWEEK()\u5141\u8bb8<br \/>\u4f60\u6307\u5b9a\u661f\u671f\u662f\u5426\u5f00\u59cb\u4e8e\u661f\u671f\u5929\u6216\u661f\u671f\u4e00\u3002\u5982\u679c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u662f0\uff0c\u661f\u671f\u4ece\u661f\u671f\u5929\u5f00\u59cb\uff0c\u5982\u679c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u662f1\uff0c<br \/>\u4ece\u661f\u671f\u4e00\u5f00\u59cb\u3002 <br \/>mysql&gt; select WEEK(&#8216;1998-02-20&#8217;); <br \/>-&gt; 7 <br \/>mysql&gt; select WEEK(&#8216;1998-02-20&#8217;,0); <br \/>-&gt; 7 <br \/>mysql&gt; select WEEK(&#8216;1998-02-20&#8217;,1); <br \/>-&gt; 8 <\/p>\n<p>YEAR(date) <br \/>\u8fd4\u56dedate\u7684\u5e74\u4efd\uff0c\u8303\u56f4\u57281000\u52309999\u3002 <br \/>mysql&gt; select YEAR(&#8217;98-02-03&#8242;); <br \/>-&gt; 1998 <\/p>\n<p>HOUR(time) <br \/>\u8fd4\u56detime\u7684\u5c0f\u65f6\uff0c\u8303\u56f4\u662f0\u523023\u3002 <br \/>mysql&gt; select HOUR(&#8217;10:05:03&#8242;); <br \/>-&gt; 10 <\/p>\n<p>MINUTE(time) <br \/>\u8fd4\u56detime\u7684\u5206\u949f\uff0c\u8303\u56f4\u662f0\u523059\u3002 <br \/>mysql&gt; select MINUTE(&#8217;98-02-03 10:05:03&#8242;); <br \/>-&gt; 5 <\/p>\n<p>SECOND(time) <br \/>\u56de\u6765time\u7684\u79d2\u6570\uff0c\u8303\u56f4\u662f0\u523059\u3002 <br \/>mysql&gt; select SECOND(&#8217;10:05:03&#8242;); <br \/>-&gt; 3 <\/p>\n<p>PERIOD_ADD(P,N) <br \/>\u589e\u52a0N\u4e2a\u6708\u5230\u9636\u6bb5P\uff08\u4ee5\u683c\u5f0fYYMM\u6216YYYYMM)\u3002\u4ee5\u683c\u5f0fYYYYMM\u8fd4\u56de\u503c\u3002\u6ce8\u610f\u9636\u6bb5\u53c2\u6570P\u4e0d\u662f\u65e5\u671f\u503c\u3002 <br \/>mysql&gt; select PERIOD_ADD(9801,2); <br \/>-&gt; 199803 <\/p>\n<p>PERIOD_DIFF(P1,P2) <br \/>\u8fd4\u56de\u5728\u65f6\u671fP1\u548cP2\u4e4b\u95f4\u6708\u6570\uff0cP1\u548cP2\u5e94\u8be5\u4ee5\u683c\u5f0fYYMM\u6216YYYYMM\u3002\u6ce8\u610f\uff0c\u65f6\u671f\u53c2\u6570P1\u548cP2\u4e0d\u662f\u65e5\u671f\u503c\u3002 <br \/>mysql&gt; select PERIOD_DIFF(9802,199703); <br \/>-&gt; 11 <\/p>\n<p>DATE_ADD(date,INTERVAL expr type) <\/p>\n<p>DATE_SUB(date,INTERVAL expr type) <\/p>\n<p>ADDDATE(date,INTERVAL expr type) <\/p>\n<p>SUBDATE(date,INTERVAL expr type) <br \/>\u8fd9\u4e9b\u529f\u80fd\u6267\u884c\u65e5\u671f\u8fd0\u7b97\u3002\u5bf9\u4e8eMySQL 3.22\uff0c\u4ed6\u4eec\u662f\u65b0\u7684\u3002ADDDATE()\u548cSUBDATE()\u662fDATE_ADD()\u548cDATE_SUB()\u7684\u540c\u4e49\u8bcd\u3002<br \/>\u5728MySQL 3.23\u4e2d\uff0c\u4f60\u53ef\u4ee5\u4f7f\u7528+\u548c-\u800c\u4e0d\u662fDATE_ADD()\u548cDATE_SUB()\u3002\uff08\u89c1\u4f8b\u5b50\uff09date\u662f\u4e00\u4e2a\u6307\u5b9a\u5f00\u59cb\u65e5\u671f\u7684<br \/>DATETIME\u6216DATE\u503c\uff0cexpr\u662f\u6307\u5b9a\u52a0\u5230\u5f00\u59cb\u65e5\u671f\u6216\u4ece\u5f00\u59cb\u65e5\u671f\u51cf\u53bb\u7684\u95f4\u9694\u503c\u4e00\u4e2a\u8868\u8fbe\u5f0f\uff0cexpr\u662f\u4e00\u4e2a\u5b57\u7b26\u4e32\uff1b\u5b83\u53ef\u4ee5\u4ee5<br \/>\u4e00\u4e2a\u201c-\u201d\u5f00\u59cb\u8868\u793a\u8d1f\u95f4\u9694\u3002type\u662f\u4e00\u4e2a\u5173\u952e\u8bcd\uff0c\u6307\u660e\u8868\u8fbe\u5f0f\u5e94\u8be5\u5982\u4f55\u88ab\u89e3\u91ca\u3002EXTRACT(type FROM date)\u51fd\u6570\u4ece\u65e5\u671f<br \/>\u4e2d\u8fd4\u56de\u201ctype\u201d\u95f4\u9694\u3002\u4e0b\u8868\u663e\u793a\u4e86type\u548cexpr\u53c2\u6570\u600e\u6837\u88ab\u5173\u8054\uff1a type\u503c \u542b\u4e49 \u671f\u671b\u7684expr\u683c\u5f0f <br \/>SECOND \u79d2 SECONDS <br \/>MINUTE \u5206\u949f MINUTES <br \/>HOUR \u65f6\u95f4 HOURS <br \/>DAY \u5929 DAYS <br \/>MONTH \u6708 MONTHS <br \/>YEAR \u5e74 YEARS <br \/>MINUTE_SECOND \u5206\u949f\u548c\u79d2 &#8220;MINUTES:SECONDS&#8221; <br \/>HOUR_MINUTE \u5c0f\u65f6\u548c\u5206\u949f &#8220;HOURS:MINUTES&#8221; <br \/>DAY_HOUR \u5929\u548c\u5c0f\u65f6 &#8220;DAYS HOURS&#8221; <br \/>YEAR_MONTH \u5e74\u548c\u6708 &#8220;YEARS-MONTHS&#8221; <br \/>HOUR_SECOND \u5c0f\u65f6, \u5206\u949f\uff0c &#8220;HOURS:MINUTES:SECONDS&#8221; <br \/>DAY_MINUTE \u5929, \u5c0f\u65f6, \u5206\u949f &#8220;DAYS HOURS:MINUTES&#8221; <br \/>DAY_SECOND \u5929, \u5c0f\u65f6, \u5206\u949f, \u79d2 &#8220;DAYS HOURS:MINUTES:SECONDS&#8221; <\/p>\n<p>MySQL\u5728expr\u683c\u5f0f\u4e2d\u5141\u8bb8\u4efb\u4f55\u6807\u70b9\u5206\u9694\u7b26\u3002\u8868\u793a\u663e\u793a\u7684\u662f\u5efa\u8bae\u7684\u5206\u9694\u7b26\u3002\u5982\u679cdate\u53c2\u6570\u662f\u4e00\u4e2aDATE\u503c\u5e76\u4e14\u4f60\u7684\u8ba1\u7b97\u4ec5\u4ec5<br \/>\u5305\u542bYEAR\u3001MONTH\u548cDAY\u90e8\u5206(\u5373\uff0c\u6ca1\u6709\u65f6\u95f4\u90e8\u5206)\uff0c\u7ed3\u679c\u662f\u4e00\u4e2aDATE\u503c\u3002\u5426\u5219\u7ed3\u679c\u662f\u4e00\u4e2aDATETIME\u503c\u3002 <\/p>\n<p>mysql&gt; SELECT &#8220;1997-12-31 23:59:59&#8221; + INTERVAL 1 SECOND; <br \/>-&gt; 1998-01-01 00:00:00 <br \/>mysql&gt; SELECT INTERVAL 1 DAY + &#8220;1997-12-31&#8221;; <br \/>-&gt; 1998-01-01 <br \/>mysql&gt; SELECT &#8220;1998-01-01&#8221; &#8211; INTERVAL 1 SECOND; <br \/>-&gt; 1997-12-31 23:59:59 <br \/>mysql&gt; SELECT DATE_ADD(&#8220;1997-12-31 23:59:59&#8221;, <br \/>INTERVAL 1 SECOND); <br \/>-&gt; 1998-01-01 00:00:00 <br \/>mysql&gt; SELECT DATE_ADD(&#8220;1997-12-31 23:59:59&#8221;, <br \/>INTERVAL 1 DAY); <br \/>-&gt; 1998-01-01 23:59:59 <br \/>mysql&gt; SELECT DATE_ADD(&#8220;1997-12-31 23:59:59&#8221;, <br \/>INTERVAL &#8220;1:1&#8221; MINUTE_SECOND); <br \/>-&gt; 1998-01-01 00:01:00 <br \/>mysql&gt; SELECT DATE_SUB(&#8220;1998-01-01 00:00:00&#8221;, <br \/>INTERVAL &#8220;1 1:1:1&#8221; DAY_SECOND); <br \/>-&gt; 1997-12-30 22:58:59 <br \/>mysql&gt; SELECT DATE_ADD(&#8220;1998-01-01 00:00:00&#8221;, <br \/>INTERVAL &#8220;-1 10&#8221; DAY_HOUR); <br \/>-&gt; 1997-12-30 14:00:00 <br \/>mysql&gt; SELECT DATE_SUB(&#8220;1998-01-02&#8221;, INTERVAL 31 DAY); <br \/>-&gt; 1997-12-02 <br \/>mysql&gt; SELECT EXTRACT(YEAR FROM &#8220;1999-07-02&#8221;); <br \/>-&gt; 1999 <br \/>mysql&gt; SELECT EXTRACT(YEAR_MONTH FROM &#8220;1999-07-02 01:02:03&#8221;); <br \/>-&gt; 199907 <br \/>mysql&gt; SELECT EXTRACT(DAY_MINUTE FROM &#8220;1999-07-02 01:02:03&#8221;); <br \/>-&gt; 20102 <\/p>\n<p>\u5982\u679c\u4f60\u6307\u5b9a\u592a\u77ed\u7684\u95f4\u9694\u503c(\u4e0d\u5305\u62ectype\u5173\u952e\u8bcd\u671f\u671b\u7684\u95f4\u9694\u90e8\u5206)\uff0cMySQL\u5047\u8bbe\u4f60\u7701\u6389\u4e86\u95f4\u9694\u503c\u7684\u6700\u5de6\u9762\u90e8\u5206\u3002\u4f8b\u5982\uff0c<br \/>\u5982\u679c\u4f60\u6307\u5b9a\u4e00\u4e2atype\u662fDAY_SECOND\uff0c\u503cexpr\u88ab\u5e0c\u671b\u6709\u5929\u3001\u5c0f\u65f6\u3001\u5206\u949f\u548c\u79d2\u90e8\u5206\u3002\u5982\u679c\u4f60\u8c61&#8221;1:10&#8243;\u8fd9\u6837\u6307\u5b9a\u503c\uff0c<br \/>MySQL\u5047\u8bbe\u65e5\u5b50\u548c\u5c0f\u65f6\u90e8\u5206\u662f\u4e22\u5931\u7684\u5e76\u4e14\u503c\u4ee3\u8868\u5206\u949f\u548c\u79d2\u3002\u6362\u53e5\u8bdd\u8bf4\uff0c&#8221;1:10&#8243; DAY_SECOND\u4ee5\u5b83\u7b49\u4ef7\u4e8e&#8221;1:10&#8243; MINUTE_SECOND<br \/>\u7684\u65b9\u5f0f\u89e3\u91ca\uff0c\u8fd9\u5bf9\u90a3MySQL\u89e3\u91caTIME\u503c\u8868\u793a\u7ecf\u8fc7\u7684\u65f6\u95f4\u800c\u975e\u4f5c\u4e3a\u4e00\u5929\u7684\u65f6\u95f4\u7684\u65b9\u5f0f\u6709\u4e8c\u4e49\u6027\u3002\u5982\u679c\u4f60\u4f7f\u7528\u786e\u5b9e\u4e0d\u6b63\u786e\u7684\u65e5\u671f\uff0c<br \/>\u7ed3\u679c\u662fNULL\u3002\u5982\u679c\u4f60\u589e\u52a0MONTH\u3001YEAR_MONTH\u6216YEAR\u5e76\u4e14\u7ed3\u679c\u65e5\u671f\u5927\u4e8e\u65b0\u6708\u4efd\u7684\u6700\u5927\u503c\u5929\u6570\uff0c\u65e5\u5b50\u5728\u65b0\u6708\u7528\u6700\u5927\u7684\u5929\u8c03\u6574\u3002 <\/p>\n<p>mysql&gt; select DATE_ADD(&#8216;1998-01-30&#8217;, Interval 1 month); <br \/>-&gt; 1998-02-28 <\/p>\n<p>\u6ce8\u610f\uff0c\u4ece\u524d\u9762\u7684\u4f8b\u5b50\u4e2d\u8bcdINTERVAL\u548ctype\u5173\u952e\u8bcd\u4e0d\u662f\u533a\u5206\u5927\u5c0f\u5199\u7684\u3002 <br \/>TO_DAYS(date) <br \/>\u7ed9\u51fa\u4e00\u4e2a\u65e5\u671fdate\uff0c\u8fd4\u56de\u4e00\u4e2a\u5929\u6570(\u4ece0\u5e74\u7684\u5929\u6570)\u3002 <br \/>mysql&gt; select TO_DAYS(950501); <br \/>-&gt; 728779 <br \/>mysql&gt; select TO_DAYS(&#8216;1997-10-07&#8217;); <br \/>-&gt; 729669 <\/p>\n<p>TO_DAYS()\u4e0d\u6253\u7b97\u7528\u4e8e\u4f7f\u7528\u683c\u5217\u9ad8\u91cc\u5386(1582)\u51fa\u73b0\u524d\u7684\u503c\u3002 <\/p>\n<p>FROM_DAYS(N) <br \/>\u7ed9\u51fa\u4e00\u4e2a\u5929\u6570N\uff0c\u8fd4\u56de\u4e00\u4e2aDATE\u503c\u3002 <br \/>mysql&gt; select FROM_DAYS(729669); <br \/>-&gt; &#8216;1997-10-07&#8217; <\/p>\n<p>TO_DAYS()\u4e0d\u6253\u7b97\u7528\u4e8e\u4f7f\u7528\u683c\u5217\u9ad8\u91cc\u5386(1582)\u51fa\u73b0\u524d\u7684\u503c\u3002 <\/p>\n<p>DATE_FORMAT(date,format) <br \/>\u6839\u636eformat\u5b57\u7b26\u4e32\u683c\u5f0f\u5316date\u503c\u3002\u4e0b\u5217\u4fee\u9970\u7b26\u53ef\u4ee5\u88ab\u7528\u5728format\u5b57\u7b26\u4e32\u4e2d\uff1a %M \u6708\u540d\u5b57(January\u2026\u2026December) <br \/>%W \u661f\u671f\u540d\u5b57(Sunday\u2026\u2026Saturday) <br \/>%D \u6709\u82f1\u8bed\u524d\u7f00\u7684\u6708\u4efd\u7684\u65e5\u671f(1st, 2nd, 3rd, \u7b49\u7b49\u3002\uff09 <br \/>%Y \u5e74, \u6570\u5b57, 4 \u4f4d <br \/>%y \u5e74, \u6570\u5b57, 2 \u4f4d <br \/>%a \u7f29\u5199\u7684\u661f\u671f\u540d\u5b57(Sun\u2026\u2026Sat) <br \/>%d \u6708\u4efd\u4e2d\u7684\u5929\u6570, \u6570\u5b57(00\u2026\u202631) <br \/>%e \u6708\u4efd\u4e2d\u7684\u5929\u6570, \u6570\u5b57(0\u2026\u202631) <br \/>%m \u6708, \u6570\u5b57(01\u2026\u202612) <br \/>%c \u6708, \u6570\u5b57(1\u2026\u202612) <br \/>%b \u7f29\u5199\u7684\u6708\u4efd\u540d\u5b57(Jan\u2026\u2026Dec) <br \/>%j \u4e00\u5e74\u4e2d\u7684\u5929\u6570(001\u2026\u2026366) <br \/>%H \u5c0f\u65f6(00\u2026\u202623) <br \/>%k \u5c0f\u65f6(0\u2026\u202623) <br \/>%h \u5c0f\u65f6(01\u2026\u202612) <br \/>%I \u5c0f\u65f6(01\u2026\u202612) <br \/>%l \u5c0f\u65f6(1\u2026\u202612) <br \/>%i \u5206\u949f, \u6570\u5b57(00\u2026\u202659) <br \/>%r \u65f6\u95f4,12 \u5c0f\u65f6(hh:mm:ss [AP]M) <br \/>%T \u65f6\u95f4,24 \u5c0f\u65f6(hh:mm:ss) <br \/>%S \u79d2(00\u2026\u202659) <br \/>%s \u79d2(00\u2026\u202659) <br \/>%p AM\u6216PM <br \/>%w \u4e00\u4e2a\u661f\u671f\u4e2d\u7684\u5929\u6570(0=Sunday \u2026\u20266=Saturday \uff09 <br \/>%U \u661f\u671f(0\u2026\u202652), \u8fd9\u91cc\u661f\u671f\u5929\u662f\u661f\u671f\u7684\u7b2c\u4e00\u5929 <br \/>%u \u661f\u671f(0\u2026\u202652), \u8fd9\u91cc\u661f\u671f\u4e00\u662f\u661f\u671f\u7684\u7b2c\u4e00\u5929 <br \/>%% \u4e00\u4e2a\u6587\u5b57\u201c%\u201d\u3002 <\/p>\n<p>\u6240\u6709\u7684\u5176\u4ed6\u5b57\u7b26\u4e0d\u505a\u89e3\u91ca\u88ab\u590d\u5236\u5230\u7ed3\u679c\u4e2d\u3002 <\/p>\n<p>mysql&gt; select DATE_FORMAT(&#8216;1997-10-04 22:23:00&#8217;, &#8216;%W %M %Y&#8217;); <br \/>-&gt; &#8216;Saturday October 1997&#8217; <br \/>mysql&gt; select DATE_FORMAT(&#8216;1997-10-04 22:23:00&#8217;, &#8216;%H:%i:%s&#8217;); <br \/>-&gt; &#8217;22:23:00&#8242; <br \/>mysql&gt; select DATE_FORMAT(&#8216;1997-10-04 22:23:00&#8217;, <br \/>&#8216;%D %y %a %d %m %b %j&#8217;); <br \/>-&gt; &#8216;4th 97 Sat 04 10 Oct 277&#8217; <br \/>mysql&gt; select DATE_FORMAT(&#8216;1997-10-04 22:23:00&#8217;, <br \/>&#8216;%H %k %I %r %T %S %w&#8217;); <br \/>-&gt; &#8217;22 22 10 10:23:00 PM 22:23:00 00 6&#8242; <br \/>MySQL3.23\u4e2d\uff0c\u5728\u683c\u5f0f\u4fee\u9970\u7b26\u5b57\u7b26\u524d\u9700\u8981%\u3002\u5728MySQL\u66f4\u65e9\u7684\u7248\u672c\u4e2d\uff0c%\u662f\u53ef\u9009\u7684\u3002 <\/p>\n<p>TIME_FORMAT(time,format) <br \/>\u8fd9\u8c61\u4e0a\u9762\u7684DATE_FORMAT()\u51fd\u6570\u4e00\u6837\u4f7f\u7528\uff0c\u4f46\u662fformat\u5b57\u7b26\u4e32\u53ea\u80fd\u5305\u542b\u5904\u7406\u5c0f\u65f6\u3001\u5206\u949f\u548c\u79d2\u7684\u90a3\u4e9b\u683c\u5f0f\u4fee\u9970\u7b26\u3002<br \/>\u5176\u4ed6\u4fee\u9970\u7b26\u4ea7\u751f\u4e00\u4e2aNULL\u503c\u62160\u3002 <br \/>CURDATE() <\/p>\n<p>CURRENT_DATE <br \/>\u4ee5&#8217;YYYY-MM-DD&#8217;\u6216YYYYMMDD\u683c\u5f0f\u8fd4\u56de\u4eca\u5929\u65e5\u671f\u503c\uff0c\u53d6\u51b3\u4e8e\u51fd\u6570\u662f\u5728\u4e00\u4e2a\u5b57\u7b26\u4e32\u8fd8\u662f\u6570\u5b57\u4e0a\u4e0b\u6587\u88ab\u4f7f\u7528\u3002 <br \/>mysql&gt; select CURDATE(); <br \/>-&gt; &#8216;1997-12-15&#8217; <br \/>mysql&gt; select CURDATE() + 0; <br \/>-&gt; 19971215 <\/p>\n<p>CURTIME() <\/p>\n<p>CURRENT_TIME <br \/>\u4ee5&#8217;HH:MM:SS&#8217;\u6216HHMMSS\u683c\u5f0f\u8fd4\u56de\u5f53\u524d\u65f6\u95f4\u503c\uff0c\u53d6\u51b3\u4e8e\u51fd\u6570\u662f\u5728\u4e00\u4e2a\u5b57\u7b26\u4e32\u8fd8\u662f\u5728\u6570\u5b57\u7684\u4e0a\u4e0b\u6587\u88ab\u4f7f\u7528\u3002 <br \/>mysql&gt; select CURTIME(); <br \/>-&gt; &#8217;23:50:26&#8242; <br \/>mysql&gt; select CURTIME() + 0; <br \/>-&gt; 235026 <\/p>\n<p>NOW() <\/p>\n<p>SYSDATE() <\/p>\n<p>CURRENT_TIMESTAMP <br \/>\u4ee5&#8217;YYYY-MM-DD HH:MM:SS&#8217;\u6216YYYYMMDDHHMMSS\u683c\u5f0f\u8fd4\u56de\u5f53\u524d\u7684\u65e5\u671f\u548c\u65f6\u95f4\uff0c\u53d6\u51b3\u4e8e\u51fd\u6570\u662f\u5728\u4e00\u4e2a\u5b57\u7b26\u4e32\u8fd8\u662f\u5728\u6570\u5b57\u7684<br \/>\u4e0a\u4e0b\u6587\u88ab\u4f7f\u7528\u3002 <br \/>mysql&gt; select NOW(); <br \/>-&gt; &#8216;1997-12-15 23:50:26&#8217; <br \/>mysql&gt; select NOW() + 0; <br \/>-&gt; 19971215235026 <\/p>\n<p>UNIX_TIMESTAMP() <\/p>\n<p>UNIX_TIMESTAMP(date) <br \/>\u5982\u679c\u6ca1\u6709\u53c2\u6570\u8c03\u7528\uff0c\u8fd4\u56de\u4e00\u4e2aUnix\u65f6\u95f4\u6233\u8bb0(\u4ece&#8217;1970-01-01 00:00:00&#8217;GMT\u5f00\u59cb\u7684\u79d2\u6570)\u3002\u5982\u679cUNIX_TIMESTAMP()\u7528\u4e00<br \/>\u4e2adate\u53c2\u6570\u88ab\u8c03\u7528\uff0c\u5b83\u8fd4\u56de\u4ece&#8217;1970-01-01 00:00:00&#8242; GMT\u5f00\u59cb\u7684\u79d2\u6570\u503c\u3002date\u53ef\u4ee5\u662f\u4e00\u4e2aDATE\u5b57\u7b26\u4e32\u3001\u4e00\u4e2aDATETIME<br \/>\u5b57\u7b26\u4e32\u3001\u4e00\u4e2aTIMESTAMP\u6216\u4ee5YYMMDD\u6216YYYYMMDD\u683c\u5f0f\u7684\u672c\u5730\u65f6\u95f4\u7684\u4e00\u4e2a\u6570\u5b57\u3002 <br \/>mysql&gt; select UNIX_TIMESTAMP(); <br \/>-&gt; 882226357 <br \/>mysql&gt; select UNIX_TIMESTAMP(&#8216;1997-10-04 22:23:00&#8217;); <br \/>-&gt; 875996580 <\/p>\n<p>\u5f53UNIX_TIMESTAMP\u88ab\u7528\u4e8e\u4e00\u4e2aTIMESTAMP\u5217\uff0c\u51fd\u6570\u5c06\u76f4\u63a5\u63a5\u53d7\u503c\uff0c\u6ca1\u6709\u9690\u542b\u7684\u201cstring-to-unix-timestamp\u201d\u53d8\u6362\u3002 <\/p>\n<p>FROM_UNIXTIME(unix_timestamp) <br \/>\u4ee5&#8217;YYYY-MM-DD HH:MM:SS&#8217;\u6216YYYYMMDDHHMMSS\u683c\u5f0f\u8fd4\u56deunix_timestamp\u53c2\u6570\u6240\u8868\u793a\u7684\u503c\uff0c\u53d6\u51b3\u4e8e\u51fd\u6570\u662f\u5728\u4e00\u4e2a\u5b57\u7b26\u4e32<br \/>\u8fd8\u662f\u6216\u6570\u5b57\u4e0a\u4e0b\u6587\u4e2d\u88ab\u4f7f\u7528\u3002 <br \/>mysql&gt; select FROM_UNIXTIME(875996580); <br \/>-&gt; &#8216;1997-10-04 22:23:00&#8217; <br \/>mysql&gt; select FROM_UNIXTIME(875996580) + 0; <br \/>-&gt; 19971004222300 <\/p>\n<p>FROM_UNIXTIME(unix_timestamp,format) <br \/>\u8fd4\u56de\u8868\u793a Unix \u65f6\u95f4\u6807\u8bb0\u7684\u4e00\u4e2a\u5b57\u7b26\u4e32\uff0c\u6839\u636eformat\u5b57\u7b26\u4e32\u683c\u5f0f\u5316\u3002format\u53ef\u4ee5\u5305\u542b\u4e0eDATE_FORMAT()\u51fd\u6570\u5217\u51fa\u7684\u6761<br \/>\u76ee\u540c\u6837\u7684\u4fee\u9970\u7b26\u3002 <br \/>mysql&gt; select FROM_UNIXTIME(UNIX_TIMESTAMP(), <br \/>&#8216;%Y %D %M %h:%i:%s %x&#8217;); <br \/>-&gt; &#8216;1997 23rd December 03:43:30 x&#8217; <\/p>\n<p>SEC_TO_TIME(seconds) <br \/>\u8fd4\u56deseconds\u53c2\u6570\uff0c\u53d8\u6362\u6210\u5c0f\u65f6\u3001\u5206\u949f\u548c\u79d2\uff0c\u503c\u4ee5&#8217;HH:MM:SS&#8217;\u6216HHMMSS\u683c\u5f0f\u5316\uff0c\u53d6\u51b3\u4e8e\u51fd\u6570\u662f\u5728\u4e00\u4e2a\u5b57\u7b26\u4e32\u8fd8\u662f\u5728\u6570\u5b57<br \/>\u4e0a\u4e0b\u6587\u4e2d\u88ab\u4f7f\u7528\u3002 <br \/>mysql&gt; select SEC_TO_TIME(2378); <br \/>-&gt; &#8217;00:39:38&#8242; <br \/>mysql&gt; select SEC_TO_TIME(2378) + 0; <br \/>-&gt; 3938 <\/p>\n<p>TIME_TO_SEC(time) <br \/>\u8fd4\u56detime\u53c2\u6570\uff0c\u8f6c\u6362\u6210\u79d2\u3002 <br \/>mysql&gt; select TIME_TO_SEC(&#8217;22:23:00&#8242;); <br \/>-&gt; 80580 <br \/>mysql&gt; select TIME_TO_SEC(&#8217;00:39:38&#8242;); <br \/>-&gt; 2378<\/p>\n<p>{\/codecitation}<\/p>\n","protected":false},"excerpt":{"rendered":"<p>{codecitation class=&#8221;brush:sql&#8221; width=&#8221;500px&#8221;} mysql&gt; SELECT something FROM table WHERE TO_DAYS(NOW()) &#8211; TO_DAYS(date_col) &lt;= 30; DAYOFWEEK(date) \u8fd4\u56de\u65e5\u671fdate\u7684\u661f\u671f\u7d22\u5f15(1=\u661f\u671f\u5929\uff0c2=\u661f\u671f\u4e00, \u2026\u20267=\u661f\u671f\u516d)\u3002\u8fd9\u4e9b\u7d22\u5f15\u503c\u5bf9\u5e94\u4e8eODBC\u6807\u51c6\u3002 mysql&gt; select DAYOFWEEK(&#8216;1998-02-03&#8217;); -&gt; 3 WEEKDAY(date) \u8fd4\u56dedate\u7684\u661f\u671f\u7d22\u5f15(0=\u661f\u671f\u4e00\uff0c1=\u661f\u671f\u4e8c, \u2026\u20266= \u661f\u671f\u5929)\u3002 mysql&gt; select WEEKDAY(&#8216;1997-10-04 22:23:00&#8217;); -&gt; 5 mysql&gt; select WEEKDAY(&#8216;1997-11-05&#8217;); -&gt; 2 DAYOFMONTH(date) \u8fd4\u56dedate\u7684\u6708\u4efd\u4e2d\u65e5\u671f\uff0c\u57281\u523031\u8303\u56f4\u5185\u3002 mysql&gt; select DAYOFMONTH(&#8216;1998-02-03&#8217;); -&gt; 3 DAYOFYEAR(date) \u8fd4\u56dedate\u5728\u4e00\u5e74\u4e2d\u7684\u65e5\u6570, \u57281\u5230366\u8303\u56f4\u5185\u3002 mysql&gt; select DAYOFYEAR(&#8216;1998-02-03&#8217;); -&gt; 34 MONTH(date) \u8fd4\u56dedate\u7684\u6708\u4efd\uff0c\u8303\u56f41\u523012\u3002 mysql&gt; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":0,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"wp:attachment":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}