对äºæ¯ä¸ªç±»åæ¥æçå¼èå´ä»¥å并ä¸æå®æ¥æä½æ¶é´å¼çæææ ¼å¼çæè¿°è§7.3.6 æ¥æåæ¶é´ç±»åã
è¿éæ¯ä¸ä¸ªä½¿ç¨æ¥æå½æ°çä¾åãä¸é¢çæ¥è¯¢éæ©äºææè®°å½ï¼å
¶date_colçå¼æ¯å¨æå30天以å
ï¼
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
è¿åæ¥ædateçææç´¢å¼(1=ææ天ï¼2=ææä¸, â¦â¦7=ææå
)ãè¿äºç´¢å¼å¼å¯¹åºäºODBCæ åã
mysql> select DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
è¿ådateçææç´¢å¼(0=ææä¸ï¼1=ææäº, â¦â¦6= ææ天)ã
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2
DAYOFMONTH(date)
è¿ådateçæ份ä¸æ¥æï¼å¨1å°31èå´å
ã
mysql> select DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
è¿ådateå¨ä¸å¹´ä¸çæ¥æ°, å¨1å°366èå´å
ã
mysql> select DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
è¿ådateçæ份ï¼èå´1å°12ã
mysql> select MONTH('1998-02-03');
-> 2
DAYNAME(date)
è¿ådateçææååã
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
è¿ådateçæ份ååã
mysql> select MONTHNAME("1998-02-05");
-> 'February'
QUARTER(date)
è¿ådateä¸å¹´ä¸çå£åº¦ï¼èå´1å°4ã
mysql> select QUARTER('98-04-01');
-> 2
WEEK(date)
ã
WEEK(date,first)
对äºææ天æ¯ä¸å¨ç第ä¸å¤©çå°æ¹ï¼æä¸ä¸ªå个åæ°ï¼è¿ådateçå¨æ°ï¼èå´å¨0å°52ã2个åæ°å½¢å¼WEEK()å
许
ä½ æå®æææ¯å¦å¼å§äºææ天æææä¸ãå¦æ第äºä¸ªåæ°æ¯0ï¼ææä»ææ天å¼å§ï¼å¦æ第äºä¸ªåæ°æ¯1ï¼
ä»ææä¸å¼å§ã
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)
è¿ådateç年份ï¼èå´å¨1000å°9999ã
mysql> select YEAR('98-02-03');
-> 1998
HOUR(time)
è¿åtimeçå°æ¶ï¼èå´æ¯0å°23ã
mysql> select HOUR('10:05:03');
-> 10
MINUTE(time)
è¿åtimeçåéï¼èå´æ¯0å°59ã
mysql> select MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
åæ¥timeçç§æ°ï¼èå´æ¯0å°59ã
mysql> select SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
å¢å N个æå°é¶æ®µPï¼ä»¥æ ¼å¼YYMMæYYYYMM)ãä»¥æ ¼å¼YYYYMMè¿åå¼ã注æé¶æ®µåæ°Pä¸æ¯æ¥æå¼ã
mysql> select PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
è¿åå¨æ¶æP1åP2ä¹é´ææ°ï¼P1åP2åºè¯¥ä»¥æ ¼å¼YYMMæYYYYMMã注æï¼æ¶æåæ°P1å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)
è¿äºåè½æ§è¡æ¥æè¿ç®ã对äºMySQL 3.22ï¼ä»ä»¬æ¯æ°çãADDDATE()åSUBDATE()æ¯DATE_ADD()åDATE_SUB()çåä¹è¯ã
å¨MySQL 3.23ä¸ï¼ä½ å¯ä»¥ä½¿ç¨+å-èä¸æ¯DATE_ADD()åDATE_SUB()ãï¼è§ä¾åï¼dateæ¯ä¸ä¸ªæå®å¼å§æ¥æç
DATETIMEæDATEå¼ï¼expræ¯æå®å å°å¼å§æ¥ææä»å¼å§æ¥æåå»çé´éå¼ä¸ä¸ªè¡¨è¾¾å¼ï¼expræ¯ä¸ä¸ªå符串ï¼å®å¯ä»¥ä»¥
ä¸ä¸ªâ-âå¼å§è¡¨ç¤ºè´é´éãtypeæ¯ä¸ä¸ªå
³é®è¯ï¼ææ表达å¼åºè¯¥å¦ä½è¢«è§£éãEXTRACT(type FROM date)å½æ°ä»æ¥æ
ä¸è¿åâtypeâé´éãä¸è¡¨æ¾ç¤ºäºtypeåexpråæ°ææ ·è¢«å
³èï¼ typeå¼ å«ä¹ ææçexpræ ¼å¼
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"
MySQLå¨expræ ¼å¼ä¸å
许任ä½æ ç¹åé符ã表示æ¾ç¤ºçæ¯å»ºè®®çåé符ãå¦ædateåæ°æ¯ä¸ä¸ªDATEå¼å¹¶ä¸ä½ ç计ç®ä»
ä»
å
å«YEARãMONTHåDAYé¨å(å³ï¼æ²¡ææ¶é´é¨å)ï¼ç»ææ¯ä¸ä¸ªDATEå¼ãå¦åç»ææ¯ä¸ä¸ªDATETIMEå¼ã
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
å¦æä½ æå®å¤ªççé´éå¼(ä¸å
æ¬typeå
³é®è¯ææçé´éé¨å)ï¼MySQLåè®¾ä½ çæäºé´éå¼çæå·¦é¢é¨åãä¾å¦ï¼
å¦æä½ æå®ä¸ä¸ªtypeæ¯DAY_SECONDï¼å¼expr被å¸ææ天ãå°æ¶ãåéåç§é¨åãå¦æä½ è±¡"1:10"è¿æ ·æå®å¼ï¼
MySQLå设æ¥ååå°æ¶é¨åæ¯ä¸¢å¤±ç并ä¸å¼ä»£è¡¨åéåç§ãæ¢å¥è¯è¯´ï¼"1:10" DAY_SECOND以å®çä»·äº"1:10" MINUTE_SECOND
çæ¹å¼è§£éï¼è¿å¯¹é£MySQL解éTIMEå¼è¡¨ç¤ºç»è¿çæ¶é´èéä½ä¸ºä¸å¤©çæ¶é´çæ¹å¼æäºä¹æ§ãå¦æä½ ä½¿ç¨ç¡®å®ä¸æ£ç¡®çæ¥æï¼
ç»ææ¯NULLãå¦æä½ å¢å MONTHãYEAR_MONTHæYEAR并ä¸ç»ææ¥æ大äºæ°æ份çæ大å¼å¤©æ°ï¼æ¥åå¨æ°æç¨æ大ç天è°æ´ã
mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
注æï¼ä»åé¢çä¾åä¸è¯INTERVALåtypeå
³é®è¯ä¸æ¯åºå大å°åçã
TO_DAYS(date)
ç»åºä¸ä¸ªæ¥ædateï¼è¿åä¸ä¸ªå¤©æ°(ä»0å¹´ç天æ°)ã
mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669
TO_DAYS()ä¸æç®ç¨äºä½¿ç¨æ ¼åé«éå(1582)åºç°åçå¼ã
FROM_DAYS(N)
ç»åºä¸ä¸ªå¤©æ°Nï¼è¿åä¸ä¸ªDATEå¼ã
mysql> select FROM_DAYS(729669);
-> '1997-10-07'
TO_DAYS()ä¸æç®ç¨äºä½¿ç¨æ ¼åé«éå(1582)åºç°åçå¼ã
DATE_FORMAT(date,format)
æ ¹æ®formatåç¬¦ä¸²æ ¼å¼ådateå¼ãä¸å修饰符å¯ä»¥è¢«ç¨å¨formatå符串ä¸ï¼ %M æåå(Januaryâ¦â¦December)
%W ææåå(Sundayâ¦â¦Saturday)
%D æè±è¯åç¼çæ份çæ¥æ(1st, 2nd, 3rd, ççãï¼
%Y å¹´, æ°å, 4 ä½
%y å¹´, æ°å, 2 ä½
%a 缩åçææåå(Sunâ¦â¦Sat)
%d æ份ä¸ç天æ°, æ°å(00â¦â¦31)
%e æ份ä¸ç天æ°, æ°å(0â¦â¦31)
%m æ, æ°å(01â¦â¦12)
%c æ, æ°å(1â¦â¦12)
%b 缩åçæ份åå(Janâ¦â¦Dec)
%j ä¸å¹´ä¸ç天æ°(001â¦â¦366)
%H å°æ¶(00â¦â¦23)
%k å°æ¶(0â¦â¦23)
%h å°æ¶(01â¦â¦12)
%I å°æ¶(01â¦â¦12)
%l å°æ¶(1â¦â¦12)
%i åé, æ°å(00â¦â¦59)
%r æ¶é´,12 å°æ¶(hh:mm:ss [AP]M)
%T æ¶é´,24 å°æ¶(hh:mm:ss)
%S ç§(00â¦â¦59)
%s ç§(00â¦â¦59)
%p AMæPM
%w ä¸ä¸ªææä¸ç天æ°(0=Sunday â¦â¦6=Saturday ï¼
%U ææ(0â¦â¦52), è¿éææ天æ¯ææç第ä¸å¤©
%u ææ(0â¦â¦52), è¿éææä¸æ¯ææç第ä¸å¤©
%% ä¸ä¸ªæåâ%âã
ææçå
¶ä»å符ä¸å解é被å¤å¶å°ç»æä¸ã
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:23:00 PM 22:23:00 00 6'
MySQL3.23ä¸ï¼å¨æ ¼å¼ä¿®é¥°ç¬¦å符åéè¦%ãå¨MySQLæ´æ©ççæ¬ä¸ï¼%æ¯å¯éçã
TIME_FORMAT(time,format)
è¿è±¡ä¸é¢çDATE_FORMAT()å½æ°ä¸æ ·ä½¿ç¨ï¼ä½æ¯formatå符串åªè½å
å«å¤çå°æ¶ãåéåç§çé£äºæ ¼å¼ä¿®é¥°ç¬¦ã
å
¶ä»ä¿®é¥°ç¬¦äº§çä¸ä¸ªNULLå¼æ0ã
CURDATE()
ã
CURRENT_DATE
以'YYYY-MM-DD'æYYYYMMDDæ ¼å¼è¿åä»å¤©æ¥æå¼ï¼åå³äºå½æ°æ¯å¨ä¸ä¸ªå符串è¿æ¯æ°åä¸ä¸æ被使ç¨ã
mysql> select CURDATE();
-> '1997-12-15'
mysql> select CURDATE() + 0;
-> 19971215
CURTIME()
ã
CURRENT_TIME
以'HH:MM:SS'æHHMMSSæ ¼å¼è¿åå½åæ¶é´å¼ï¼åå³äºå½æ°æ¯å¨ä¸ä¸ªå符串è¿æ¯å¨æ°åçä¸ä¸æ被使ç¨ã
mysql> select CURTIME();
-> '23:50:26'
mysql> select CURTIME() + 0;
-> 235026
NOW()
ã
SYSDATE()
ã
CURRENT_TIMESTAMP
以'YYYY-MM-DD HH:MM:SS'æYYYYMMDDHHMMSSæ ¼å¼è¿åå½åçæ¥æåæ¶é´ï¼åå³äºå½æ°æ¯å¨ä¸ä¸ªå符串è¿æ¯å¨æ°åç
ä¸ä¸æ被使ç¨ã
mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026
UNIX_TIMESTAMP()
ã
UNIX_TIMESTAMP(date)
å¦æ没æåæ°è°ç¨ï¼è¿åä¸ä¸ªUnixæ¶é´æ³è®°(ä»'1970-01-01 00:00:00'GMTå¼å§çç§æ°)ãå¦æUNIX_TIMESTAMP()ç¨ä¸
个dateåæ°è¢«è°ç¨ï¼å®è¿åä»'1970-01-01 00:00:00' GMTå¼å§çç§æ°å¼ãdateå¯ä»¥æ¯ä¸ä¸ªDATEå符串ãä¸ä¸ªDATETIME
å符串ãä¸ä¸ªTIMESTAMPæ以YYMMDDæYYYYMMDDæ ¼å¼çæ¬å°æ¶é´çä¸ä¸ªæ°åã
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
å½UNIX_TIMESTAMP被ç¨äºä¸ä¸ªTIMESTAMPåï¼å½æ°å°ç´æ¥æ¥åå¼ï¼æ²¡æéå«çâstring-to-unix-timestampâåæ¢ã
FROM_UNIXTIME(unix_timestamp)
以'YYYY-MM-DD HH:MM:SS'æYYYYMMDDHHMMSSæ ¼å¼è¿åunix_timestampåæ°æ表示çå¼ï¼åå³äºå½æ°æ¯å¨ä¸ä¸ªå符串
è¿æ¯ææ°åä¸ä¸æä¸è¢«ä½¿ç¨ã
mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
è¿å表示 Unix æ¶é´æ è®°çä¸ä¸ªå符串ï¼æ ¹æ®formatåç¬¦ä¸²æ ¼å¼åãformatå¯ä»¥å
å«ä¸DATE_FORMAT()å½æ°ååºçæ¡
ç®åæ ·ç修饰符ã
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 x'
SEC_TO_TIME(seconds)
è¿åsecondsåæ°ï¼åæ¢æå°æ¶ãåéåç§ï¼å¼ä»¥'HH:MM:SS'æHHMMSSæ ¼å¼åï¼åå³äºå½æ°æ¯å¨ä¸ä¸ªå符串è¿æ¯å¨æ°å
ä¸ä¸æä¸è¢«ä½¿ç¨ã
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
è¿åtimeåæ°ï¼è½¬æ¢æç§ã
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
SELECT DATE_SUB(now(),
INTERVAL "0 0:10:0" DAY_SECOND);
温馨提示:答案为网友推荐,仅供参考