ããå½ä½ éè¦åæ¶å
å«æ¥æåæ¶é´ä¿¡æ¯çå¼æ¶å使ç¨DATETIMEç±»åãMySQL以'YYYY-MM-DD HH:MM:SS'æ ¼å¼æ£ç´¢åæ¾ç¤ºDATETIMEå¼ãæ¯æçèå´ä¸º'1000-01-01 00:00:00'å°'9999-12-31 23:59:59'ã(âæ¯æâ表示尽管å
åçå¼å¯è½å·¥ä½ï¼ä½æ²¡æä¿è¯)ã
ããå½ä½ åªéè¦æ¥æå¼èä¸éè¦æ¶é´é¨åæ¶åºä½¿ç¨DATEç±»åãMySQLç¨'YYYY-MM-DD'æ ¼å¼æ£ç´¢åæ¾ç¤ºDATEå¼ãæ¯æçèå´æ¯'1000-01-01'å° '9999-12-31'ã
ããTIMESTAMPåç±»åçå±æ§ä¸åºå®ï¼åå³äºMySQLçæ¬åæå¡å¨è¿è¡çSQL模å¼ãè¿äºå±æ§å°å¨æ¬èåé¢æè¿°ã
ããå¯ä»¥ä½¿ç¨ä»»ä½å¸¸è§æ ¼å¼æå®DATETIMEãDATEåTIMESTAMPå¼ï¼
ãã'YYYY-MM-DD HH:MM:SS'æ'YY-MM-DD HH:MM:SS'æ ¼å¼çå符串ãå
许âä¸ä¸¥æ ¼âè¯æ³ï¼ä»»ä½æ ç¹ç¬¦é½å¯ä»¥ç¨åæ¥æé¨åææ¶é´é¨åä¹é´çé´å²ç¬¦ãä¾å¦ï¼'98-12-31 11:30:45'ã'98.12.31 11+30+45'ã'98/12/31 11*30*45'å'98@12@31 11^30^45'æ¯çä»·çã
ãã'YYYY-MM-DD'æ'YY-MM-DD'æ ¼å¼çå符串ãè¿éä¹å
许使ç¨âä¸ä¸¥æ ¼çâè¯æ³ãä¾å¦ï¼'98-12-31'ã'98.12.31'ã'98/12/31'å'98@12@31'æ¯çä»·çã
ãã'YYYYMMDDHHMMSS'æ'YYMMDDHHMMSS'æ ¼å¼ç没æé´å²ç¬¦çå符串ï¼åå®å符串对äºæ¥æç±»åæ¯ææä¹çãä¾å¦ï¼'19970523091528'å'970523091528'被解é为'1997-05-23 09:15:28'ï¼ä½'971122129015'æ¯ä¸åæ³ç(å®æä¸ä¸ªæ²¡ææä¹çåéé¨å)ï¼å°å为'0000-00-00 00:00:00'ã
ãã'YYYYMMDD'æ'YYMMDD'æ ¼å¼ç没æé´å²ç¬¦çå符串ï¼åå®å符串对äºæ¥æç±»åæ¯ææä¹çãä¾å¦ï¼'19970523'å'970523'被解é为 '1997-05-23'ï¼ä½'971332'æ¯ä¸åæ³ç(å®æä¸ä¸ªæ²¡ææä¹çæåæ¥é¨å)ï¼å°å为'0000-00-00'ã
ããYYYYMMDDHHMMSSæYYMMDDHHMMSSæ ¼å¼çæ°åï¼åå®æ°å对äºæ¥æç±»åæ¯ææä¹çãä¾å¦ï¼19830905132800å830905132800被解é为 '1983-09-05 13:28:00'ã
ããYYYYMMDDæYYMMDDæ ¼å¼çæ°åï¼åå®æ°å对äºæ¥æç±»åæ¯ææä¹çãä¾å¦ï¼19830905å830905被解é为'1983-09-05'ã
ããå½æ°è¿åçç»æï¼å
¶å¼éåDATETIMEãDATEæè
TIMESTAMPä¸ä¸æï¼ä¾å¦NOW()æCURRENT_DATEã
ããæ æDATETIMEãDATEæè
TIMESTAMPå¼è¢«è½¬æ¢ä¸ºç¸åºç±»åçâé¶âå¼('0000-00-00 00:00:00'ã'0000-00-00'æè
00000000000000)ã
ãã对äºå
æ¬æ¥æé¨åé´å²ç¬¦çå符串å¼ï¼å¦ææ¥åæçå¼å°äº10ï¼ä¸éè¦æå®ä¸¤ä½æ°ã'1979-6-9'ä¸'1979-06-09'æ¯ç¸åçãåæ ·ï¼å¯¹äºå
æ¬æ¶é´é¨åé´å²ç¬¦çå符串å¼ï¼å¦ææ¶ãååç§çå¼å°äº10ï¼ä¸éè¦æå®ä¸¤ä½æ°ã'1979-10-30 1:2:3'ä¸'1979-10-30 01:02:03'ç¸åã
ããæ°åå¼åºä¸º6ã8ã12æè
14ä½é¿ãå¦æä¸ä¸ªæ°å¼æ¯8æ14ä½é¿ï¼ååå®ä¸ºYYYYMMDDæYYYYMMDDHHMMSSæ ¼å¼ï¼å4ä½æ°è¡¨ç¤ºå¹´ãå¦ææ°å æ¯6æ12ä½é¿ï¼ååå®ä¸ºYYMMDDæYYMMDDHHMMSSæ ¼å¼ï¼å2ä½æ°è¡¨ç¤ºå¹´ãå
¶å®æ°å被解é为仿ä½ç¨é¶å¡«å
å°äºæè¿çé¿åº¦ã
ããæå®ä¸ºééå®ç¬¦å符串çå¼ä½¿ç¨ç»å®çé¿åº¦è¿è¡è§£éãå¦æå符串为8æ14å符é¿ï¼å4ä½æ°è¡¨ç¤ºå¹´ãå¦åï¼å2ä½æ°è¡¨ç¤ºå¹´ãä»å·¦åå³è§£éå符串å
åºç°çåé¨åï¼ä»¥åç°å¹´ãæãæ¥ãå°æ¶ãååç§å¼ãè¿è¯´æä¸åºä½¿ç¨å°äº6å符çå符串ãä¾å¦ï¼å¦æä½ æå®'9903'ï¼è®¤ä¸ºå®è¡¨ç¤º1999å¹´3æï¼MySQLå°å¨ä½ ç表å
æå
¥ä¸ä¸ªâé¶âæ¥æå¼ãè¿æ¯å 为年åæå¼æ¯99å03ï¼ä½æ¥é¨åå®å
¨ä¸¢å¤±ï¼å æ¤è¯¥å¼ä¸æ¯ä¸ä¸ªåæ³çæ¥æãä½æ¯ï¼å¯ä»¥ææ¾æå®ä¸ä¸ªé¶å¼æ¥ä»£è¡¨ç¼ºå°çæææ¥é¨åãä¾å¦ï¼å¯ä»¥ä½¿ç¨'990300'æ¥æå
¥å¼'1999-03-00'ã
ããå¨ä¸å®ç¨åº¦ä¸ï¼å¯ä»¥å°ä¸ä¸ªæ¥æç±»åçå¼åé
ç»ä¸ä¸ªä¸åçæ¥æç±»åãä½æ¯ï¼å¼å¯è½ä¼æ´æ¹æ丢失ä¸äºä¿¡æ¯ï¼
ããå¦æä½ ä¸ºä¸ä¸ªDATETIMEæTIMESTAMP对象åé
ä¸ä¸ªDATEå¼ï¼ç»æå¼çæ¶é´é¨å被设置为'00:00:00'ï¼å 为DATEå¼æªå
å«æ¶é´ä¿¡æ¯ã
ããå¦æä½ ä¸ºä¸ä¸ªDATE对象åé
ä¸ä¸ªDATETIMEæTIMESTAMPå¼ï¼ç»æå¼çæ¶é´é¨å被å é¤ï¼å 为DATEå¼æªå
å«æ¶é´ä¿¡æ¯ã
ããè®°ä½å°½ç®¡å¯ä»¥ä½¿ç¨ç¸åçæ ¼å¼æå®DATETIMEãDATEåTIMESTAMPå¼ï¼ä¸åç±»åçå¼çèå´å´ä¸åãä¾å¦ï¼TIMESTAMPå¼ä¸è½æ©äº1970ææäº2037ãè¿è¯´æä¸ä¸ªæ¥æï¼ä¾å¦'1968-01-01'ï¼è½ç¶å¯¹äºDATETIMEæDATEå¼æ¯ææçï¼ä½å¯¹äºTIMESTAMPå¼å´æ æï¼å¦æåé
ç»è¿æ ·ä¸ä¸ªå¯¹è±¡å°è¢«è½¬æ¢ä¸º0ã
ããå½æå®æ¥æå¼æ¶è¯·æ³¨ææäºç¼ºé·ï¼
ããæå®ä¸ºå符串çå¼å
许çéä¸¥æ ¼æ ¼å¼å¯è½ä¼æ¬ºéªãä¾å¦ï¼å¼'10:11:12'ç±äºâ:âé´å²ç¬¦çä¸å»å¯è½è±¡æ¶é´å¼ï¼ä½å¦æç¨äºæ¥æä¸ä¸æå¼å被解é为年'2010-11-12'ãå¼'10:45:15'被转æ¢ä¸º'0000-00-00'å 为'45'ä¸æ¯åæ³æã
ããå¨éä¸¥æ ¼æ¨¡å¼ï¼MySQLæå¡å¨åªå¯¹æ¥æçåæ³æ§è¿è¡åºæ¬æ£æ¥ï¼å¹´ãæåæ¥çèå´åå«æ¯1000å°9999ã00å°12å00å°31ãä»»ä½å
å«è¶
åºè¿äºèå´çé¨åçæ¥æ被转æ¢æ'0000-00-00'ã请注æä»ç¶å
è®¸ä½ ä¿åéæ³æ¥æï¼ä¾å¦'2002-04-31'ãè¦æ³ç¡®ä¿ä¸ä½¿ç¨ä¸¥æ ¼æ¨¡å¼æ¶æ¥æææï¼åºæ£æ¥åºç¨ç¨åºã
ããå¨ä¸¥æ ¼æ¨¡å¼ï¼éæ³æ¥æä¸è¢«æ¥åï¼å¹¶ä¸ä¸è½¬æ¢ã
ããå
å«ä¸¤ä½å¹´å¼çæ¥æä¼ä»¤äººæ¨¡ç³ï¼å 为ä¸çºªä¸ç¥éãMySQL使ç¨ä»¥ä¸è§å解é两ä½å¹´å¼ï¼
ãã00-69èå´çå¹´å¼è½¬æ¢ä¸º2000-2069ã
ãã70-99èå´çå¹´å¼è½¬æ¢ä¸º1970-1999ã
转载ä»
ä¾åèï¼çæå±äºåä½è
温馨提示:答案为网友推荐,仅供参考