MySQL日期数据类型MySQL时间类型使用总结.docx
《MySQL日期数据类型MySQL时间类型使用总结.docx》由会员分享,可在线阅读,更多相关《MySQL日期数据类型MySQL时间类型使用总结.docx(15页珍藏版)》请在冰点文库上搜索。
![MySQL日期数据类型MySQL时间类型使用总结.docx](https://file1.bingdoc.com/fileroot1/2023-7/8/fc635cbb-51cb-4ace-a295-4c155028f242/fc635cbb-51cb-4ace-a295-4c155028f2421.gif)
MySQL日期数据类型MySQL时间类型使用总结
MySQL£ºMySQLÈÕÆÚÊý¾ÝÀàÐÍ¡¢MySQLʱ¼äÀàÐÍʹÓÃ×ܽá
MySQLÈÕÆÚÀàÐÍ£ºÈÕÆÚ¸ñʽ¡¢ËùÕ¼´æ´¢¿Õ¼ä¡¢ÈÕÆÚ·¶Î§±È½Ï¡£
ÈÕÆÚÀàÐÍ´æ´¢¿Õ¼äÈÕÆÚ¸ñʽÈÕÆÚ·¶Î§
-----------------------------------------------------------------------------------
datetime8bytesYYYY-MM-DDHH:
MM:
SS1000-01-0100:
00:
00~9999-12-3123:
59:
59
timestamp4bytesYYYY-MM-DDHH:
MM:
SS1970-01-0100:
00:
01~2038
date3bytesYYYY-MM-DD1000-01-01~9999-12-31
year1bytesYYYY1901~2155
ÔÚMySQLÖд´½¨±íʱ£¬¶ÔÕÕÉÏÃæµÄ±í¸ñ£¬ºÜÈÝÒ×¾ÍÄÜÑ¡Ôñµ½ºÏÊÊ×Ô¼ºµÄÊý¾ÝÀàÐÍ¡£²»¹ýµ½µ×ÊÇÑ¡Ôñdatetime»¹ÊÇtimestamp£¬¿ÉÄÜ»áÓе㷸ÄÑ¡£ÕâÁ½¸öÈÕÆÚʱ¼äÀàÐ͸÷ÓÐÓŵ㣺datetimeµÄÈÕÆÚ·¶Î§±È½Ï´ó£»timestampËùÕ¼´æ´¢¿Õ¼ä±È½ÏС£¬Ö»ÊÇdatetimeµÄÒ»°ë¡£
ÁíÍ⣬timestampÀàÐ͵ÄÁл¹ÓиöÌØÐÔ£ºÄ¬ÈÏÇé¿öÏ£¬ÔÚinsert,updateÊý¾Ýʱ£¬timestampÁлá×Ô¶¯ÒÔµ±Ç°Ê±¼ä£¨CURRENT_TIMESTAMP£©Ìî³ä/¸üС£¡°×Ô¶¯¡±µÄÒâ˼¾ÍÊÇ£¬Ä㲻ȥ¹ÜËü£¬MySQL»áÌæÄãÈ¥´¦Àí¡£
Ò»°ãÇé¿öÏ£¬ÎÒÇãÏòÓÚʹÓÃdatetimeÈÕÆÚÀàÐÍ¡£
MySQLʱ¼äÀàÐÍ£ºÊ±¼ä¸ñʽ¡¢ËùÕ¼´æ´¢¿Õ¼ä¡¢Ê±¼ä·¶Î§¡£
ʱ¼äÀàÐÍ´æ´¢¿Õ¼äʱ¼ä¸ñʽʱ¼ä·¶Î§
-----------------------------------------------------------------------------------
time3bytesHH:
MM:
SS-838:
59:
59~838:
59:
59
timeʱ¼ä·¶Î§¾ÓÈ»ÓÐÕâô´óµÄ·¶Î§£¬ÌرðÊÇtime¿ÉÒÔÈ¡¸ºÖµ£¬ÓеãÆæ¹Ö¡£ºóÀ´£¬¿´ÁËMySQLÊÖ²á²ÅÖªµÀÕâÊÇΪÁËÂú×ãÁ½¸öÈÕÆÚʱ¼äÏà¼õ²ÅÕâÑùÉè¼ÆµÄ¡£
selecttimediff('2000:
01:
3123:
59:
59','2000:
01:
0100:
00:
00');--743:
59:
59
selecttimediff('2000:
01:
0100:
00:
00','2000:
01:
3123:
59:
59');---743:
59:
59
selecttimediff('23:
59:
59','12:
00:
00');--11:
59:
59
×¢Ò⣬timediffµÄÁ½¸ö²ÎÊýÖ»ÄÜÊÇdatetime/timestamp,timeÀàÐ͵ģ¬²¢ÇÒÕâÁ½¸ö²ÎÊýÀàÐÍÒªÏàͬ¡£¼´£ºdatetime/timestampºÍdatetime/timestamp±È½Ï£»timeºÍtimeÏà±È½Ï¡£
ËäÈ»MySQLÖеÄÈÕÆÚʱ¼äÀàÐͱȽϷḻ£¬µ«Òź¶µÄÊÇ£¬Ä¿Ç°£¨2008-08-08£©ÕâЩÈÕÆÚʱ¼äÀàÐÍÖ»ÄÜÖ§³Öµ½Ã뼶±ð£¬²»Ö§³ÖºÁÃ롢΢Ã롣ҲûÓвúÉúºÁÃëµÄº¯Êý¡£
¡¶MySQL£ºMySQLÈÕÆÚÊý¾ÝÀàÐÍ¡¢MySQLʱ¼äÀàÐÍʹÓÃ×ܽᡷÊÊÓÃÓÚMySQL5.X¼°ÒÔÉÏ°æ±¾¡£
Ò»¡¢MySQL»ñµÃµ±Ç°ÈÕÆÚʱ¼äº¯Êý
1.1»ñµÃµ±Ç°ÈÕÆÚ+ʱ¼ä£¨date+time£©º¯Êý£ºnow()
mysql>selectnow();
+---------------------+
|now()|
+---------------------+
|2008-08-0822:
20:
46|
+---------------------+
³ýÁËnow()º¯ÊýÄÜ»ñµÃµ±Ç°µÄÈÕÆÚʱ¼äÍ⣬MySQLÖл¹ÓÐÏÂÃæµÄº¯Êý£º
current_timestamp()
current_timestamp
localtime()
localtime
localtimestamp--(v4.0.6)
localtimestamp()--(v4.0.6)
ÕâЩÈÕÆÚʱ¼äº¯Êý£¬¶¼µÈͬÓÚnow()¡£¼øÓÚnow()º¯Êý¼ò¶ÌÒ׼ǣ¬½¨Òé×ÜÊÇʹÓÃnow()À´Ìæ´úÉÏÃæÁгöµÄº¯Êý¡£
1.2»ñµÃµ±Ç°ÈÕÆÚ+ʱ¼ä£¨date+time£©º¯Êý£ºsysdate()
sysdate()ÈÕÆÚʱ¼äº¯Êý¸únow()ÀàËÆ£¬²»Í¬Ö®´¦ÔÚÓÚ£ºnow()ÔÚÖ´ÐпªÊ¼Ê±Öµ¾ÍµÃµ½ÁË£¬sysdate()ÔÚº¯ÊýÖ´ÐÐʱ¶¯Ì¬µÃµ½Öµ¡£¿´ÏÂÃæµÄÀý×Ó¾ÍÃ÷°×ÁË£º
mysql>selectnow(),sleep(3),now();
+---------------------+----------+---------------------+
|now()|sleep(3)|now()|
+---------------------+----------+---------------------+
|2008-08-0822:
28:
21|0|2008-08-0822:
28:
21|
+---------------------+----------+---------------------+
mysql>selectsysdate(),sleep(3),sysdate();
+---------------------+----------+---------------------+
|sysdate()|sleep(3)|sysdate()|
+---------------------+----------+---------------------+
|2008-08-0822:
28:
41|0|2008-08-0822:
28:
44|
+---------------------+----------+---------------------+
¿ÉÒÔ¿´µ½£¬ËäÈ»ÖÐ;sleep3Ã룬µ«now()º¯ÊýÁ½´ÎµÄʱ¼äÖµÊÇÏàͬµÄ£»sysdate()º¯ÊýÁ½´ÎµÃµ½µÄʱ¼äÖµÏà²î3Ãë¡£MySQLManualÖÐÊÇÕâÑùÃèÊösysdate()µÄ£ºReturnthetimeatwhichthefunctionexecutes¡£
sysdate()ÈÕÆÚʱ¼äº¯Êý£¬Ò»°ãÇé¿öϺÜÉÙÓõ½¡£
2.»ñµÃµ±Ç°ÈÕÆÚ£¨date£©º¯Êý£ºcurdate()
mysql>selectcurdate();
+------------+
|curdate()|
+------------+
|2008-08-08|
+------------+
ÆäÖУ¬ÏÂÃæµÄÁ½¸öÈÕÆÚº¯ÊýµÈͬÓÚcurdate()£º
current_date()
current_date
3.»ñµÃµ±Ç°Ê±¼ä£¨time£©º¯Êý£ºcurtime()
mysql>selectcurtime();
+-----------+
|curtime()|
+-----------+
|22:
41:
30|
+-----------+
ÆäÖУ¬ÏÂÃæµÄÁ½¸öʱ¼äº¯ÊýµÈͬÓÚcurtime()£º
current_time()
current_time
4.»ñµÃµ±Ç°UTCÈÕÆÚʱ¼äº¯Êý£ºutc_date(),utc_time(),utc_timestamp()
mysql>selectutc_timestamp(),utc_date(),utc_time(),now()
+---------------------+------------+------------+---------------------+
|utc_timestamp()|utc_date()|utc_time()|now()|
+---------------------+------------+------------+---------------------+
|2008-08-0814:
47:
11|2008-08-08|14:
47:
11|2008-08-0822:
47:
11|
+---------------------+------------+------------+---------------------+
ÒòΪÎÒ¹úλÓÚ¶«°ËʱÇø£¬ËùÒÔ±¾µØʱ¼ä=UTCʱ¼ä+8Сʱ¡£UTCʱ¼äÔÚÒµÎñÉæ¼°¶à¸ö¹ú¼ÒºÍµØÇøµÄʱºò£¬·Ç³£ÓÐÓá£
¶þ¡¢MySQLÈÕÆÚʱ¼äExtract£¨Ñ¡È¡£©º¯Êý¡£
1.Ñ¡È¡ÈÕÆÚʱ¼äµÄ¸÷¸ö²¿·Ö£ºÈÕÆÚ¡¢Ê±¼ä¡¢Äê¡¢¼¾¶È¡¢Ô¡¢ÈÕ¡¢Ð¡Ê±¡¢·ÖÖÓ¡¢Ã롢΢Ãë
set@dt='2008-09-1007:
15:
30.123456';
selectdate(@dt);--2008-09-10
selecttime(@dt);--07:
15:
30.123456
selectyear(@dt);--2008
selectquarter(@dt);--3
selectmonth(@dt);--9
selectweek(@dt);--36
selectday(@dt);--10
selecthour(@dt);--7
selectminute(@dt);--15
selectsecond(@dt);--30
selectmicrosecond(@dt);--123456
2.MySQLExtract()º¯Êý£¬¿ÉÒÔÉÏÃæʵÏÖÀàËƵŦÄÜ£º
set@dt='2008-09-1007:
15:
30.123456';
selectextract(yearfrom@dt);--2008
selectextract(quarterfrom@dt);--3
selectextract(monthfrom@dt);--9
selectextract(weekfrom@dt);--36
selectextract(dayfrom@dt);--10
selectextract(hourfrom@dt);--7
selectextract(minutefrom@dt);--15
selectextract(secondfrom@dt);--30
selectextract(microsecondfrom@dt);--123456
selectextract(year_monthfrom@dt);--200809
selectextract(day_hourfrom@dt);--1007
selectextract(day_minutefrom@dt);--100715
selectextract(day_secondfrom@dt);--10071530
selectextract(day_microsecondfrom@dt);--10071530123456
selectextract(hour_minutefrom@dt);--715
selectextract(hour_secondfrom@dt);--71530
selectextract(hour_microsecondfrom@dt);--71530123456
selectextract(minute_secondfrom@dt);--1530
selectextract(minute_microsecondfrom@dt);--1530123456
selectextract(second_microsecondfrom@dt);--30123456
MySQLExtract()º¯Êý³ýÁËûÓÐdate(),time()µÄ¹¦ÄÜÍ⣬ÆäËû¹¦ÄÜÒ»Ó¦¾ßÈ«¡£²¢ÇÒ»¹¾ßÓÐÑ¡È¡¡®day_microsecond¡¯µÈ¹¦ÄÜ¡£×¢ÒâÕâÀï²»ÊÇֻѡȡdayºÍmicrosecond£¬¶øÊÇ´ÓÈÕÆÚµÄday²¿·Öһֱѡȡµ½microsecond²¿·Ö¡£¹»Ç¿º·µÄ°É£¡
MySQLExtract()º¯ÊýΨһ²»ºÃµÄµØ·½ÔÚÓÚ£ºÄãÐèÒª¶àÇü¸´Î¼üÅÌ¡£
3.MySQLdayof...º¯Êý£ºdayofweek(),dayofmonth(),dayofyear()
·Ö±ð·µ»ØÈÕÆÚ²ÎÊý£¬ÔÚÒ»ÖÜ¡¢Ò»Ô¡¢Ò»ÄêÖеÄλÖá£
set@dt='2008-08-08';
selectdayofweek(@dt);--6
selectdayofmonth(@dt);--8
selectdayofyear(@dt);--221
ÈÕÆÚ'2008-08-08'ÊÇÒ»ÖÜÖеĵÚ6Ì죨1=Sunday,2=Monday,...,7=Saturday£©£»Ò»ÔÂÖеĵÚ8Ì죻һÄêÖеĵÚ221Ìì¡£
4.MySQLweek...º¯Êý£ºweek(),weekofyear(),dayofweek(),weekday(),yearweek()
set@dt='2008-08-08';
selectweek(@dt);--31
selectweek(@dt,3);--32
selectweekofyear(@dt);--32
selectdayofweek(@dt);--6
selectweekday(@dt);--4
selectyearweek(@dt);--200831
MySQLweek()º¯Êý£¬¿ÉÒÔÓÐÁ½¸ö²ÎÊý£¬¾ßÌå¿É¿´Êֲᡣweekofyear()ºÍweek()Ò»Ñù£¬¶¼ÊǼÆË㡰ijÌ족ÊÇλÓÚÒ»ÄêÖеĵڼ¸ÖÜ¡£weekofyear(@dt)µÈ¼ÛÓÚweek(@dt,3)¡£
MySQLweekday()º¯ÊýºÍdayofweek()ÀàËÆ£¬¶¼ÊÇ·µ»Ø¡°Ä³Ì족ÔÚÒ»ÖÜÖеÄλÖᣲ»Í¬µãÔÚÓڲο¼µÄ±ê×¼£¬weekday£º(0=Monday,1=Tuesday,...,6=Sunday)£»dayofweek£º£¨1=Sunday,2=Monday,...,7=Saturday£©
MySQLyearweek()º¯Êý£¬·µ»Øyear(2008)+weekλÖÃ(31)¡£
5.MySQL·µ»ØÐÇÆÚºÍÔ·ÝÃû³Æº¯Êý£ºdayname(),monthname()
set@dt='2008-08-08';
selectdayname(@dt);--Friday
selectmonthname(@dt);--August
˼¿¼£¬ÈçºÎ·µ»ØÖÐÎĵÄÃû³ÆÄØ£¿
6.MySQLlast_day()º¯Êý£º·µ»ØÔ·ÝÖеÄ×îºóÒ»Ìì¡£
selectlast_day('2008-02-01');--2008-02-29
selectlast_day('2008-08-08');--2008-08-31
MySQLlast_day()º¯Êý·Ç³£ÓÐÓ㬱ÈÈçÎÒÏëµÃµ½µ±Ç°Ô·ÝÖÐÓжàÉÙÌ죬¿ÉÒÔÕâÑùÀ´¼ÆË㣺
mysql>selectnow(),day(last_day(now()))asdays;
+---------------------+------+
|now()|days|
+---------------------+------+
|2008-08-0911:
45:
45|31|
+---------------------+------+
Èý¡¢MySQLÈÕÆÚʱ¼ä¼ÆË㺯Êý
1.MySQLΪÈÕÆÚÔö¼ÓÒ»¸öʱ¼ä¼ä¸ô£ºdate_add()
set@dt=now();
selectdate_add(@dt,interval1day);--add1day
selectdate_add(@dt,interval1hour);--add1hour
selectdate_add(@dt,interval1minute);--...
selectdate_add(@dt,interval1second);
selectdate_add(@dt,interval1microsecond);
selectdate_add(@dt,interval1week);
selectdate_add(@dt,interval1month);
selectdate_add(@dt,interval1quarter);
selectdate_add(@dt,interval1year);
selectdate_add(@dt,interval-1day);--sub1day
MySQLadddate(),addtime()º¯Êý£¬¿ÉÒÔÓÃdate_add()À´Ìæ´ú¡£ÏÂÃæÊÇdate_add()ʵÏÖaddtime()¹¦ÄÜʾÀý£º
mysql>set@dt='2008-08-0912:
12:
33';
mysql>
mysql>selectdate_add(@dt,interval'01:
15:
30'hour_second);
+------------------------------------------------+
|date_add(@dt,interval'01:
15:
30'hour_second)|
+------------------------------------------------+
|2008-08-0913:
28:
03|
+------------------------------------------------+
mysql>selectdate_add(@dt,interval'101:
15:
30'day_second);
+-------------------------------------------------+
|date_add(@dt,interval'101:
15:
30'day_second)|
+-------------------------------------------------+
|2008-08-1013:
28:
03|
+-------------------------------------------------+
date_add()º¯Êý£¬·Ö±ðΪ@dtÔö¼ÓÁË¡°1Сʱ15·Ö30Ã롱ºÍ¡°1Ìì1Сʱ15·Ö30Ã롱¡£½¨Ò飺×ÜÊÇʹÓÃdate_add()ÈÕÆÚʱ¼äº¯ÊýÀ´Ìæ´úadddate(),addtime()¡£
2.MySQLΪÈÕÆÚ¼õÈ¥Ò»¸öʱ¼ä¼ä¸ô£ºdate_sub()
mysql>selectdate_sub('1998-01-0100:
00:
00',interval'11:
1:
1'day_second);
+----------------------------------------------------------------+
|date_sub('1998-01-0100:
00:
00',interval'11:
1:
1'day_second)|
+----------------------------------------------------------------+
|1997-12-3022:
58:
59|
+----------------------------------------------------------------+
MySQLdate_sub()ÈÕÆÚʱ¼äº¯ÊýºÍd