Mysql日期格式以及內(nèi)置日期函數(shù)用法詳解

更新時(shí)間：2023年05月10日 14:34:24 作者：ximeneschen

MySQL中有多種數(shù)據(jù)類型可以用于日期和時(shí)間的表示,這篇文章主要給大家介紹了關(guān)于Mysql日期格式以及內(nèi)置日期函數(shù)用法的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、MySQL中的日期時(shí)間類型

MySQL中常用的幾種時(shí)間類型有：date、datetime、time、year、timestamp

datetime和timestamp的區(qū)別

相同點(diǎn): 存儲(chǔ)格式相同 datetime和timestamp兩者的時(shí)間格式都是YYYY-MM-DD HH:MM:SS
不同點(diǎn):
- a. 存儲(chǔ)范圍不同. datetime的范圍是1000-01-01到9999-12-31. 而timestamp是從1970-01-01到2038-01-19, 即后者的時(shí)間范圍很小.
- b. 與時(shí)區(qū)關(guān)系. datetime是存儲(chǔ)服務(wù)器當(dāng)前的時(shí)區(qū). 而timestamp類型,是將服務(wù)器當(dāng)前時(shí)間轉(zhuǎn)換為UTC(世界時(shí)間)來(lái)存儲(chǔ).即datetime與時(shí)區(qū)無(wú)關(guān),存什么,返回什么. 而timestamp存儲(chǔ)的時(shí)間,返回的時(shí)間會(huì)隨著數(shù)據(jù)庫(kù)的時(shí)區(qū)不同而發(fā)生改變.

詳細(xì)解釋

datetime : 時(shí)間日期型，格式是YYYY-mm-dd HH:ii:ss，表示的范圍是從1000到9999。但是有零值，0000-00-00 00:00:00；
date：日期，就是datetime中的date部分；
time：時(shí)間(段)，指定的某個(gè)區(qū)間之間，從-時(shí)間到+時(shí)間(有負(fù)時(shí)間表示)；
timestamp：時(shí)間戳，并不是常規(guī)意義時(shí)間戳(如：14253685)，范圍是’1970-01-01 00:00:00’到2037年。格式為YYYY-mm-dd HH:ii:ss,格式與datetime完全一致；
year：yy和yyyy，yyyy的范圍是1901-2155，yy的范圍是1970-2069。

兩位year(00-69表示2000-2069,70-99表示1970~1999)。當(dāng)應(yīng)用只需要記錄年份時(shí)，year比date更省空間

create table my_date(
	d1 datetime,
	d2 date,
	d3 time,
	d4 timestamp,
	d5 year
)charset utf8;

描述表：

測(cè)試表

insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','2015');
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-11:50:54','2015-09-28 11:51:08','2015');-- -11
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-211:50:54','2015-09-28 11:51:08','2015');-- -2 11
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-2 11:50:54','2015-09-28 11:51:08','2015');-- -2過去兩天
#year用69標(biāo)識(shí)-2069
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','69');-- 69
#year用70標(biāo)識(shí)-1970
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','70');-- 70
insert into my_date VALUES(now(),now(),now(),now(),now());-- 70

二、mysql中獲取時(shí)間戳

select UNIX_TIMESTAMP();

三、mysql中datetime類型的長(zhǎng)度位數(shù)

通常我們MySQL中設(shè)計(jì)datetime類型長(zhǎng)度都默認(rèn)為0：這時(shí)插入時(shí)間通常會(huì)是我們常見到的：2020-08-29 12:52:16格式。但是如果datetime(n)中的n不為0呢？

create table my_date(
	datetime datetime,
	datetime2 datetime(2),
	date date,
	time time,
	timestamp timestamp,
	year year
)charset utf8;

四、mysql常用時(shí)間函數(shù)

獲取當(dāng)前時(shí)間

now()
sysdate()
current_timestamp()

sysdate() 日期函數(shù)和 now() 類似, 不同之處在于: now() 在執(zhí)行開始的時(shí)候就得到值了, sysdate() 在函數(shù)執(zhí)行時(shí)動(dòng)態(tài)得到的值,例子如下：

時(shí)間轉(zhuǎn)換函數(shù)（字符串轉(zhuǎn)時(shí)間，時(shí)間轉(zhuǎn)字符串）日期/時(shí)間轉(zhuǎn)字符串函數(shù): date_format(date, format)（可以格式日期和時(shí)間）, time_format(time, format)（可以格式時(shí)間）

字符串轉(zhuǎn)換日期函數(shù): str_to_date(str, format)

可以看出, str_to_date(str, format) 轉(zhuǎn)換函數(shù), 可以把一些雜亂無(wú)章的字符串轉(zhuǎn)換為日期格式.

日期, 天數(shù)轉(zhuǎn)換函數(shù): to_days(date), from_days(days)，to_days():返回的是一個(gè)天數(shù)!從年份 0 開始的天數(shù)，from_days(days)作用與to_days相反。

時(shí)間, 秒轉(zhuǎn)換函數(shù): time_to_sec(time), sec_to_time(seconds)

兩個(gè)函數(shù)是相互的

設(shè)定日期, 時(shí)間函數(shù): makedate(year, dayfoyear), maketime(hour, minute, second)

時(shí)間戳, 日期轉(zhuǎn)換函數(shù): unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp, format)

# 當(dāng)前時(shí)間戳
select unix_timestamp();
# 指定時(shí)間的時(shí)間戳
select unix_timestamp('2020-09-09 12:00:00');
# 時(shí)間戳轉(zhuǎn)換成日期
select from_unixtime(1599624000);
# 時(shí)間戳轉(zhuǎn)換成指定格式日期
select from_unixtime(1599624000, '%Y-%D-%M %h:%i:%s');

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

日期增加一個(gè)時(shí)間間隔函數(shù): date_add()

set @dt = now();
# 增加一天
select date_add(@dt, interval 1 day);
# 增加一小時(shí)
select date_add(@dt, interval 1 hour);
# 增加一周
select date_add(@dt, interval 1 week);
# 增加一季度
select date_add(@dt, interval 1 quarter);
# 增加一天
select date_add(@dt, interval -1 day);

為指定日期減去一個(gè)時(shí)間間隔函數(shù): date_sub()，用法如上。

兩個(gè)日期, 時(shí)間相減函數(shù): datediff(date1, date2), timediff(time1, time2)

時(shí)間戳 (timestamp) 轉(zhuǎn)換, 增加, 減少函數(shù): timestamp(date), timestamp(dt, time), timestampadd(unit, interval, datetime_expr), timestampdiff(unit, datetime_expr1, datetime_expr2)

# timestamp(date)
mysql> select timestamp('2020-09-09');
+-------------------------+
| timestamp('2020-09-09') |
+-------------------------+
| 2020-09-09 00:00:00     |
+-------------------------+
1 row in set (0.00 sec)

# timestamp(dt, time)
mysql> select timestamp('2020-09-09 09:00:00', '01:00:00');
+----------------------------------------------+
| timestamp('2020-09-09 09:00:00', '01:00:00') |
+----------------------------------------------+
| 2020-09-09 10:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)

# timestampadd(unit, interval, datetime_expr)
mysql> select timestampadd(day, 1, '2020-09-09 09:00:00');
+---------------------------------------------+
| timestampadd(day, 1, '2020-09-09 09:00:00') |
+---------------------------------------------+
| 2020-09-10 09:00:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)


# timestampdiff(unit, datetime_expr1, datetime_expr2), 返回結(jié)果以天為單位
mysql> select timestampdiff(day, '2020-09-09', '2020-09-01');
+------------------------------------------------+
| timestampdiff(day, '2020-09-09', '2020-09-01') |
+------------------------------------------------+
|                                             -8 |
+------------------------------------------------+
1 row in set (0.00 sec)

# 返回結(jié)果以小時(shí)為單位
mysql> select timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00');
+-------------------------------------------------------------------+
| timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00') |
+-------------------------------------------------------------------+
|                                                              -193 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2020-09-09', '2020-09-01');
+--------------------------------------+
| datediff('2020-09-09', '2020-09-01') |
+--------------------------------------+
|                                    8 |
+--------------------------------------+
1 row in set (0.00 sec)

timestampdiff() 函數(shù)就比 datediff() 功能強(qiáng)多了，datediff() 只能計(jì)算兩個(gè)日期（date）之間相差的天數(shù)