SQL Date & Time

1. 定義

慕課解釋：Date和Time數(shù)據(jù)類型分別用于存儲日期和時間格式類數(shù)據(jù)。

2. 前言

本小節(jié)中，我們將學(xué)習(xí) SQL 種常見的日期和時間數(shù)據(jù)類型。

在很多真實業(yè)務(wù)中，我們需要存儲一些日期時間數(shù)據(jù)，比如：用戶生日，新聞聯(lián)播播出時間等等；若是采用字符串存儲，在操作的時候會頗為麻煩，為了方便用戶，SQL 給我們提供了日期和時間數(shù)據(jù)類型和諸多好用的函數(shù)。

提示： 不同的 DBMS 對于日期和時間的支持差異很大，本小節(jié)會著重介紹幾種常用數(shù)據(jù)類型。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  username varchar(20),
  age int,
  lunchtime time,
  birthday date
);
INSERT INTO imooc_user(username,age,lunchtime,birthday)
VALUES ('peter', 18, '12:00:00', '2002-10-21'), ('pedro', 24, '11:30:00', '1996-03-15');

3. SQL Time

SQL Time 用于存儲時間類數(shù)據(jù)類型，時間的范圍在-838:59:59.000000 ~ 838:59:59.000000之間，當(dāng)然有效值在00:00:00~23.59.59之間。

3.1 例1 插入用戶

請書寫 SQL 語句，向imooc_user中插入一個新用戶，用戶名為lucy，lunchtime為00:00:00。

分析：

由題干可知，使用 Insert 指令向表中插入數(shù)據(jù)即可；由于在新建 imooc_user 表時已經(jīng)指定 lunchtime 的數(shù)據(jù)類型為time，在語句中寫入格式化后的時間字符串，數(shù)據(jù)庫會自動識別并存儲。

語句：

INSERT INTO imooc_user(username,lunchtime) VALUES('lucy','00:00:00');

插入成功后，結(jié)果為：

+----------+--------+-----------+------------+
| username | age    | lunchtime | birthday   |
+----------+--------+-----------+------------+
| lucy     | <null> | 00:00:00  | <null>     |
+----------+--------+-----------+------------+

4. SQL Date

SQL Date 用于存儲日期類數(shù)據(jù)類型，日期的有效值范圍在1000-01-01 ~ 9999-12-31之間。

4.1 例2 更新用戶

請書寫 SQL 語句，更新imooc_user中的lucy用戶，將其年齡設(shè)置為20，生日設(shè)置為2000-01-08。

分析：

題干中明確指出更新用戶，使用 Update 指令，更新字段為年齡和生日。

語句：

UPDATE imooc_user SET age=20,birthday='2000-01-08' WHERE username='lucy';

更新成功后，結(jié)果為：

+----------+-----+-----------+------------+
| username | age | lunchtime | birthday   |
+----------+-----+-----------+------------+
| lucy     | 20  | 00:00:00  | 2000-01-08 |
+----------+-----+-----------+------------+

5. SQL Timestamp

SQL Timestamp 用來存儲時間戳數(shù)據(jù)，不僅可以保存日期數(shù)據(jù)，還可以保存時間數(shù)據(jù)。

5.1 例3 修改用戶表

請書寫 SQL 語句，給imooc_user表新增create_time字段，字段類型為timestamp，并更新lucy用戶的create_time字段的值為當(dāng)前時間戳。

分析：

需先使用 Alter 指令給 imooc_user 新增 create_time 字段，字段類型為 timestamp，然后使用 Update 指令，更新 lucy 用戶的 create_time 字段為當(dāng)前時間戳。

提示： SQL 中提供了now函數(shù)可以方便的生成當(dāng)前時間戳。

語句：

ALTER TABLE imooc_user ADD create_time timestamp;
UPDATE imooc_user SET create_time=now() WHERE username='lucy';

更新成功后，結(jié)果為：

+----------+-----+-----------+------------+---------------------+
| username | age | lunchtime | birthday   | create_time         |
+----------+-----+-----------+------------+---------------------+
| lucy     | 20  | 00:00:00  | 2000-01-08 | 2020-01-12 22:43:38 |
+----------+-----+-----------+------------+---------------------+

6. 其它

在 SQL 標準中關(guān)于時間和日期的數(shù)據(jù)類型主要有三類，即time，date和timestamp。

不過在不同的數(shù)據(jù)庫中，相應(yīng)的時間會有所不同，如 MySQL 中還支持 datetime 來融合 date 和 time；而在 SQL Server 中不支持 timestamp 數(shù)據(jù)類型，僅支持 datetime 類型；PostgreSQL 對日期類型支持最為復(fù)雜，甚至還支持日期范圍之類的日期格式。

7. 小結(jié)

• timestamp 會受到時區(qū)因素影響，請?zhí)崆霸O(shè)置數(shù)據(jù)庫正確的配置信息保證數(shù)據(jù)的正確性。
• datetime 和 timestamp 二者一定程度上可以通用，但是某些數(shù)據(jù)庫只支持其中的一種。
• time、date 和 timestamp 都可以像其它數(shù)據(jù)類型一樣使用運算符，如>。