MySQL日期數(shù)據(jù)類(lèi)型MySQL時(shí)間類(lèi)型使用總結(jié)

本文格式為Word版，下載可任意編輯——MySQL日期數(shù)據(jù)類(lèi)型MySQL時(shí)間類(lèi)型使用總結(jié)selectnow();

++

|now()|++

|2023-08-0822:20:46|++

除了now()函數(shù)能獲得當(dāng)前的日期時(shí)間外，MySQL中還有下面的函數(shù)：

current_timestamp(),current_timestamp,localtime(),localtime

,localtimestamp--(v4.0.6),localtimestamp()--(v4.0.6)

這些日期時(shí)間函數(shù)，都等同于now()。鑒于now()函數(shù)簡(jiǎn)短易記，建議總是使用now()來(lái)替代上面列出的函數(shù)。

1.2獲得當(dāng)前日期+時(shí)間（date+time）函數(shù)：sysdate()

sysdate()日期時(shí)間函數(shù)跟now()類(lèi)似，不同之處在于：now()在執(zhí)行開(kāi)始時(shí)值就得到了，sysdate()在函數(shù)執(zhí)行時(shí)動(dòng)態(tài)得到值。看下面的例子就明白了：

mysql>selectnow(),sleep(3),now();

++++

|now()|sleep(3)|now()|++++

|2023-08-0822:28:21|0|2023-08-0822:28:21|++++

mysql>selectsysdate(),sleep(3),sysdate();

++++

|sysdate()|sleep(3)|sysdate()|++++

|2023-08-0822:28:41|0|2023-08-0822:28:44|

++++

可以看到，雖然中途sleep3秒，但now()函數(shù)兩次的時(shí)間值是一致的；sysdate()函數(shù)兩次得到的時(shí)間值相差3秒。MySQLManual中是這樣描述sysdate()的：Returnthetimeatwhichthefunctionexecutes。

sysdate()日期時(shí)間函數(shù)，一般狀況下很少用到。

2.獲得當(dāng)前日期（date）函數(shù)：curdate()

mysql>selectcurdate();

++|curdate()|++|2023-08-08|++

其中，下面的兩個(gè)日期函數(shù)等同于curdate()：

current_date(),current_date

3.獲得當(dāng)前時(shí)間（time）函數(shù)：curtime()

mysql>selectcurtime();

++|curtime()|++|22:41:30|++

其中，下面的兩個(gè)時(shí)間函數(shù)等同于curtime()：

current_time(),current_time

4.獲得當(dāng)前UTC日期時(shí)間函數(shù)：utc_date(),utc_time(),utc_timestamp()

mysql>selectutc_timestamp(),utc_date(),utc_time(),now()

+++++

|utc_timestamp()|utc_date()|utc_time()|now()|

+++++

|2023-08-0814:47:11|2023-08-08|14:47:11|2023-08-0822:47:11|+++++

由于我國(guó)位于東八時(shí)區(qū)，所以本地時(shí)間=UTC時(shí)間+8小時(shí)。UTC時(shí)間在業(yè)務(wù)涉及多個(gè)國(guó)家和地區(qū)的時(shí)候，十分有用。

二、MySQL日期時(shí)間Extract（選?。┖瘮?shù)。

1.選取日期時(shí)間的各個(gè)部分：日期、時(shí)間、年、季度、月、日、小時(shí)、分鐘、秒、微秒

set@dt='2023-09-1007:15:30.123456';

selectdate(@dt);--2023-09-10

selecttime(@dt);--07:15:30.123456selectyear(@dt);--2023selectquarter(@dt);--3selectmonth(@dt);--9selectweek(@dt);--36selectday(@dt);--10selecthour(@dt);--7selectminute(@dt);--15selectsecond(@dt);--30selectmicrosecond(@dt);--123456

2.MySQLExtract()函數(shù)，可以上面實(shí)現(xiàn)類(lèi)似的功能：

set@dt='2023-09-1007:15:30.123456';

selectextract(yearfrom@dt);--2023selectextract(quarterfrom@dt);--3selectextract(monthfrom@dt);--9selectextract(weekfrom@dt);--36selectextract(dayfrom@dt);--10selectextract(hourfrom@dt);--7selectextract(minutefrom@dt);--15selectextract(secondfrom@dt);--30selectextract(microsecondfrom@dt);--123456

selectextract(year_monthfrom@dt);--202309selectextract(day_hourfrom@dt);--1007selectextract(day_minutefrom@dt);--100715selectextract(day_secondfrom@dt);--10071530

selectextract(day_microsecondfrom@dt);--10071530123456selectextract(hour_minutefrom@dt);--715

selectextract(hour_secondfrom@dt);--71530

selectextract(hour_microsecondfrom@dt);--71530123456selectextract(minute_secondfrom@dt);--1530

selectextract(minute_microsecondfrom@dt);--1530123456selectextract(second_microsecondfrom@dt);--30123456

MySQLExtract()函數(shù)除了沒(méi)有date(),time()的功能外，其他功能一應(yīng)具全。并且還具有選取'day_microsecond'等功能。注意這里不是只選取day和microsecond，而是從日期的day部分一直選取到microsecond部分。夠強(qiáng)悍的吧！

MySQLExtract()函數(shù)唯一不好的地方在于：你需要多敲幾次鍵盤(pán)。

3.MySQLdayof...函數(shù)：dayofweek(),dayofmonth(),dayofyear()

分別返回日期參數(shù)，在一周、一月、一年中的位置。

set@dt='2023-08-08';

selectdayofweek(@dt);--6selectdayofmonth(@dt);--8selectdayofyear(@dt);--221

日期'2023-08-08'是一周中的第6天（1=Sunday,2=Monday,...,7=Saturday）；一月中的第8天；一年中的第221天。

4.MySQLweek...函數(shù)：week(),weekofyear(),dayofweek(),weekday(),yearweek()

set@dt='2023-08-08';

selectweek(@dt);--31selectweek(@dt,3);--32selectweekofyear(@dt);--32

selectdayofweek(@dt);--6selectweekday(@dt);--4

selectyearweek(@dt);--202331

MySQLweek()函數(shù)，可以有兩個(gè)參數(shù)，具體可看手冊(cè)。weekofyear()和week()一樣，都是計(jì)算\某天\是位于一年中的第幾周。weekofyear(@dt)等價(jià)于week(@dt,3)。

MySQLweekday()函數(shù)和dayofweek()類(lèi)似，都是返回\某天\在一周中的位置。不同點(diǎn)在于參考的標(biāo)準(zhǔn)，weekday：(0=Monday,1=Tuesday,...,6=Sunday)；dayofweek：（1=Sunday,2=Monday,...,7=Saturday）

MySQLyearweek()函數(shù)，返回year(2023)+week位置(31)。

5.MySQL返回星期和月份名稱函數(shù)：dayname(),monthname()

set@dt='2023-08-08';

selectdayname(@dt);--Fridayselectmonthname(@dt);--August

思考，如何返回中文的名稱呢？

6.MySQLlast_day()函數(shù)：返回月份中的最終一天。

selectlast_day('2023-02-01');--2023-02-29selectlast_day('2023-08-08');--2023-08-31

MySQLlast_day()函數(shù)十分有用，譬如我想得到當(dāng)前月份中有多少天，可以這樣來(lái)計(jì)算：

mysql>selectnow(),day(last_day(now()))asdays;

+++

|now()|days|+++

|2023-08-0911:45:45|31|+++

三、MySQL日期時(shí)間計(jì)算函數(shù)

1.MySQL為日期增加一個(gè)時(shí)間間隔：date_add()

set@dt=now();

selectdate_add(@dt,interval1day);--add1dayselectdate_add(@dt,interval1hour);--add1hourselectdate_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()函數(shù)，可以用date_add()來(lái)替代。下面是date_add()實(shí)現(xiàn)addtime()功能例如：

mysql>set@dt='2023-08-0912:12:33';

mysql>

mysql>selectdate_add(@dt,interval'01:15:30'hour_second);

++

|date_add(@dt,interval'01:15:30'hour_second)|++

|2023-08-0913:28:03|++

mysql>selectdate_add(@dt,interval'101:15:30'day_second);

++

|date_add(@dt,interval'101:15:30'day_second)|++

|2023-08-1013:28:03|++

date_add()函數(shù)，分別為@dt增加了\小時(shí)15分30秒\和\天1小時(shí)15分30秒\。建議：總是使用date_add()日期時(shí)間函數(shù)來(lái)替代adddate(),addtime()。

2.MySQL為日期減去一個(gè)時(shí)間間隔：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()日期時(shí)間函數(shù)和date_add()用法一致，不再贅述。另外，MySQL中還有兩個(gè)函數(shù)subdate(),subtime()，建議，用date_sub()來(lái)替代。

3.MySQL另類(lèi)日期函數(shù)：period_add(P,N),period_diff(P1,P2)

函數(shù)參數(shù)\的格式為\或者\(yùn)，其次個(gè)參數(shù)\表示增加或減去Nmonth（月）。

MySQLperiod_add(P,N)：日期加/減去N月。

mysql>selectperiod_add(202308,2),period_add(20230808,-2)

+++

|period_add(202308,2)|period_add(20230808,-2)|+++

|202310|20230806|+++

MySQLperiod_diff(P1,P2)：日期P1-P2，返回N個(gè)月。

mysql>selectperiod_diff(202308,202301);

++

|period_diff(202308,202301)|++

|7|++

在MySQL中，這兩個(gè)日期函數(shù)，一般狀況下很少用到。

4.MySQL日期、時(shí)間相減函數(shù)：datediff(date1,date2),timediff(time1,time2)

MySQLdatediff(date1,date2)：兩個(gè)日期相減date1-date2，返回天數(shù)。

selectdatediff('2023-08-08','2023-08-01');--7selectdatediff('2023-08-01','2023-08-08');---7

MySQLtimediff(time1,time2)：兩個(gè)日期相減time1-time2，返回time差值。

selecttimediff('2023-08-0808:08:08','2023-08-0800:00:00');--08:08:08selecttimediff('08:08:08','00:00:00');--08:08:08

注意：timediff(time1,time2)函數(shù)的兩個(gè)參數(shù)類(lèi)型必需一致。

四、MySQL日期轉(zhuǎn)換函數(shù)、時(shí)間轉(zhuǎn)換函數(shù)

1.MySQL（時(shí)間、秒）轉(zhuǎn)換函數(shù)：time_to_sec(time),sec_to_time(seconds)

selecttime_to_sec('01:00:05');--3605

selectsec_to_time(3605);--'01:00:05'

2.MySQL（日期、天數(shù)）轉(zhuǎn)換函數(shù)：to_days(date),from_days(days)

selectto_days('0000-00-00');--0

selectto_days('2023-08-08');--733627

selectfrom_days(0);--'0000-00-00'selectfrom_days(733627);--'2023-08-08'

3.MySQLStrtoDate（字符串轉(zhuǎn)換為日期）函數(shù)：str_to_date(str,format)

selectstr_to_date('08/09/2023','%m/%d/%Y');--2023-08-09selectstr_to_date('08/09/08','%m/%d/%y');--2023-08-09selectstr_to_date('08.09.2023','%m.%d.%Y');--2023-08-09selectstr_to_date('08:09:30','%h:%i:%s');--08:09:30

selectstr_to_date('08.09.202308:09:30','%m.%d.%Y%h:%i:%s');--2023-08-0908:09:30

可以看到，str_to_date(str,format)轉(zhuǎn)換函數(shù)，可以把一些雜亂無(wú)章的字符串轉(zhuǎn)換為日期格式。另外，它也可以轉(zhuǎn)換為時(shí)間。\可以參看MySQL手冊(cè)。

4.MySQLDate/TimetoStr（日期/時(shí)間轉(zhuǎn)換為字符串）函數(shù)：date_format(date,format),time_format(time,format)

mysql>selectdate_format('2023-08-0822:23:00','%W%M%Y');

++

|date_format('2023-08-0822:23:00','%W%M%Y')|++

|FridayAugust2023|++

mysql>selectdate_format('2023-08-0822:23:01','%Y%m%d%H%i%s');

++

|date_format('2023-08-0822:23:01','%Y%m%d%H%i%s')|++

|20230808222301|++

mysql>selecttime_format('22:23:01','%H.%i.%s');

++

|time_format('22:23:01','%H.%i.%s')|++

|22.23.01|++

MySQL日期、時(shí)間轉(zhuǎn)換函數(shù)：date_format(date,format),time_format(time,format)能夠把一

個(gè)日期/時(shí)間轉(zhuǎn)換成各種各樣的字符串格式。它是str_to_date(str,format)函數(shù)的一個(gè)逆轉(zhuǎn)換。

5.MySQL獲得國(guó)家地區(qū)時(shí)間格式函數(shù)：get_format()

MySQLget_format()語(yǔ)法：

get_format(date|time|datetime,'eur'|'usa'|'jis'|'iso'|'internal'

MySQLget_format()用法的全部例如：

selectget_format(date,'usa');--'%m.%d.%Y'selectget_format(date,'jis');--'%Y-%m-%d'selectget_format(date,'iso');--'%Y-%m-%d'selectget_format(date,'eur');--'%d.%m.%Y'selectget_format(date,'internal');--'%Y%m%d'

selectget_format(datetime,'usa');--'%Y-%m-%d%H.%i.%s'selectget_format(datetime,'jis');--'%Y-%m-%d%H:%i:%s'selectget_format(datetime,'iso');--'%Y-%m-%d%H:%i:%s'selectget_format(datetime,'eur');--'%Y-%m-%d%H.%i.%s'selectget_format(datetime,'internal');--'%Y%m%d%H%i%s'selectget_format(time,'usa');--'%h:%i:%s%p'selectget_format(time,'jis');--'%H:%i:%s'selectget_format(time,'iso');--'%H:%i:%s'selectget_format(time,'eur');--'%H.%i.%s'selectget_format(time,'internal');--'%H%i%s'

MySQLget_format()函數(shù)在實(shí)際中用到機(jī)遇的比較少。

6.MySQL拼湊日期、時(shí)間函數(shù)：makdedate(year,dayofyear),maketime(hour,minute,second)

selectmakedate(2023,31);--'2023-01-31'selectmakedate(2023,32);--'2023-02-01'

selectmaketime(12,15,30);--'12:15:30'

五、MySQL時(shí)間戳（Timestamp）函數(shù)

1.MySQL獲得當(dāng)前時(shí)間戳函數(shù)：current_timestamp,current_timestamp()

mysql>selectcurrent_timestamp,current_timestamp();

+++

|current_timestamp|current_timestamp()|+++

|2023-08-0923:22:24|2023-08-0923:22:24|+++

2.MySQL（Unix時(shí)間戳、日期）轉(zhuǎn)換函數(shù)：

unix_timestamp(),unix_timestamp(date),

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

下面是例如：

selectunix_timestamp();--1218290027selectunix_timestamp('2023-08-08');--1218124800selectunix_timestamp('2023-08-0812:30:00');--1218169800

selectfrom_unixtime(1218290027);--'2023-08-0921:53:47'selectfrom_unixtime(1218124800);--'2023-08-0800:00:00'selectfrom_unixtime(1218169800);--'2023-08-0812:30:00'

selectfrom_unixtime(1218169800,'%Y%D%M%h:%i:%s%x');--'20238thAugust12:30:002023'

3.MySQL時(shí)間戳（timestamp）轉(zhuǎn)換、增、減函數(shù)：

timestamp(date)--datetotimestamptimestamp(dt,time)--dt+timetimestampadd(unit,interval,datetime_expr)--timestampdiff(unit,datetime_ex