Mysql日期格式以及內置日期函數用法詳解

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

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

一、MySQL中的日期時間類型

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

datetime和timestamp的區(qū)別

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

詳細解釋

datetime : 時間日期型，格式是YYYY-mm-dd HH:ii:ss，表示的范圍是從1000到9999。但是有零值，0000-00-00 00:00:00；
date：日期，就是datetime中的date部分；
time：時間(段)，指定的某個區(qū)間之間，從-時間到+時間(有負時間表示)；
timestamp：時間戳，并不是常規(guī)意義時間戳(如：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)。當應用只需要記錄年份時，year比date更省空間

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

描述表：

測試表

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標識-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標識-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中獲取時間戳

select UNIX_TIMESTAMP();

三、mysql中datetime類型的長度位數

通常我們MySQL中設計datetime類型長度都默認為0：這時插入時間通常會是我們常見到的：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常用時間函數

獲取當前時間

now()
sysdate()
current_timestamp()

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

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

字符串轉換日期函數: str_to_date(str, format)

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

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

時間, 秒轉換函數: time_to_sec(time), sec_to_time(seconds)

兩個函數是相互的

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

時間戳, 日期轉換函數: unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp, format)

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

五、日期時間計算函數

日期增加一個時間間隔函數: date_add()

set @dt = now();
# 增加一天
select date_add(@dt, interval 1 day);
# 增加一小時
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);

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

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

時間戳 (timestamp) 轉換, 增加, 減少函數: 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), 返回結果以天為單位
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)

# 返回結果以小時為單位
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() 函數就比 datediff() 功能強多了，datediff() 只能計算兩個日期（date）之間相差的天數