MySQL日期数据类型、MySQL时间类型使用总结.doc

《MySQL日期数据类型、MySQL时间类型使用总结.doc》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、MySQL£ºMySQLÈÕÆÚÊý¾ÝÀàÐÍ¡¢MySQLʱ¼äÀàÐÍʹÓÃ×ܽáMySQLÈÕÆÚÀàÐÍ£ºÈÕÆÚ¸ñʽ¡¢ËùÕ¼´æ´¢¿Õ¼ä¡¢ÈÕÆÚ·¶Î§±È½Ï¡£ÈÕÆÚÀàÐÍ´æ´¢¿Õ¼äÈÕÆÚ¸ñʽÈÕÆÚ·¶Î§-----------------------------------------------------------------------------------datetime8bytesYYYY-MM-DDHH:MM:SS1000-01-0100:00:00~9999-12-312

2、3:59:59timestamp4bytesYYYY-MM-DDHH:MM:SS1970-01-0100:00:01~2038date3bytesYYYY-MM-DD1000-01-01~9999-12-31year1bytesYYYY1901~2155ÔÚMySQLÖд´½¨±íʱ£¬¶ÔÕÕÉÏÃæµÄ±í¸ñ£¬ºÜÈÝÒ×¾ÍÄÜÑ¡Ôñµ½ºÏÊÊ×Ô¼ºµÄÊý¾ÝÀàÐÍ¡£²»¹ýµ½µ×ÊÇÑ¡Ôñdatetime»¹ÊÇtimestamp£¬¿ÉÄÜ»áÓе㷸ÄÑ¡£ÕâÁ½¸öÈÕÆÚʱ¼äÀàÐ͸÷ÓÐÓŵã

3、£ºdatetimeµÄÈÕÆÚ·¶Î§±È½Ï´ó£»timestampËùÕ¼´æ´¢¿Õ¼ä±È½ÏС£¬Ö»ÊÇdatetimeµÄÒ»°ë¡£ÁíÍ⣬timestampÀàÐ͵ÄÁл¹ÓиöÌØÐÔ£ºÄ¬ÈÏÇé¿öÏ£¬ÔÚinsert,updateÊý¾Ýʱ£¬timestampÁлá×Ô¶¯ÒÔµ±Ç°Ê±¼ä£¨CURRENT_TIMESTAMP£©Ìî³ä/¸üС£¡°×Ô¶¯¡±µÄÒâ˼¾ÍÊÇ£¬Ä㲻ȥ¹ÜËü£¬MySQL»áÌæÄãÈ¥´¦Àí¡£Ò»°ãÇé¿öÏ£¬ÎÒÇãÏòÓÚ

4、ʹÓÃdatetimeÈÕÆÚÀàÐÍ¡£MySQLʱ¼äÀàÐÍ£ºÊ±¼ä¸ñʽ¡¢ËùÕ¼´æ´¢¿Õ¼ä¡¢Ê±¼ä·¶Î§¡£Ê±¼äÀàÐÍ´æ´¢¿Õ¼äʱ¼ä¸ñʽʱ¼ä·¶Î§-----------------------------------------------------------------------------------time3bytesHH:MM:SS-838:59:59~838:59:59timeʱ¼ä·¶Î§¾ÓÈ»ÓÐÕâô´óµÄ·¶Î§£¬ÌرðÊÇtime¿ÉÒÔÈ¡¸ºÖµ£

5、¬ÓеãÆæ¹Ö¡£ºóÀ´£¬¿´ÁËMySQLÊÖ²á²ÅÖªµÀÕâÊÇΪÁËÂú×ãÁ½¸öÈÕÆÚʱ¼äÏà¼õ²ÅÕâÑùÉè¼ÆµÄ¡£selecttimediff('2000:01:3123:59:59','2000:01:0100:00:00');--743:59:59selecttimediff('2000:01:0100:00:00','2000:01:3123:59:59');---743:59:59selecttimediff('23:59:59','12:00:00');--11:59:59×¢Ò⣬timedi

6、ffµÄÁ½¸ö²ÎÊýÖ»ÄÜÊÇdatetime/timestamp,timeÀàÐ͵ģ¬²¢ÇÒÕâÁ½¸ö²ÎÊýÀàÐÍÒªÏàͬ¡£¼´£ºdatetime/timestampºÍdatetime/timestamp±È½Ï£»timeºÍtimeÏà±È½Ï¡£ËäÈ»MySQLÖеÄÈÕÆÚʱ¼äÀàÐͱȽϷḻ£¬µ«Òź¶µÄÊÇ£¬Ä¿Ç°£¨2008-08-08£©ÕâЩÈÕÆÚʱ¼äÀàÐÍÖ»ÄÜÖ§³Öµ½Ã뼶±ð£¬²»Ö§³ÖºÁÃ롢΢Ã롣ҲûÓвúÉúºÁÃëµÄº¯Ê

7、ý¡£¡¶MySQL£ºMySQLÈÕÆÚÊý¾ÝÀàÐÍ¡¢MySQLʱ¼äÀàÐÍʹÓÃ×ܽᡷÊÊÓÃÓÚMySQL5.X¼°ÒÔÉÏ°æ±¾¡£Ò»¡¢MySQL»ñµÃµ±Ç°ÈÕÆÚʱ¼äº¯Êý1.1»ñµÃµ±Ç°ÈÕÆÚ+ʱ¼ä£¨date+time£©º¯Êý£ºnow()mysql>selectnow();+---------------------+

8、now()

9、+---------------------+

10、2008-08-0822:20:46

11、+---------------------+³ýÁËnow()º

12、¯ÊýÄÜ»ñµÃµ±Ç°µÄÈÕÆÚʱ¼äÍ⣬MySQLÖл¹ÓÐÏÂÃæµÄº¯Êý£ºcurrent_tim