18 SQL Date & Time

优质

小牛编辑

131浏览

2023-12-01

1. 定义

慕课解释：Date和Time数据类型分别用于存储日期和时间格式类数据。

2. 前言

本小节中，我们将学习 SQL 种常见的日期和时间数据类型。

在很多真实业务中，我们需要存储一些日期时间数据，比如：用户生日，新闻联播播出时间等等；若是采用字符串存储，在操作的时候会颇为麻烦，为了方便用户，SQL 给我们提供了日期和时间数据类型和诸多好用的函数。

提示： 不同的 DBMS 对于日期和时间的支持差异很大，本小节会着重介绍几种常用数据类型。

本小节测试数据如下，请先在数据库中执行：

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 用于存储时间类数据类型，时间的范围在-838:59:59.000000 ~ 838:59:59.000000之间，当然有效值在00:00:00~23.59.59之间。

3.1 例1 插入用户

请书写 SQL 语句，向imooc_user中插入一个新用户，用户名为lucy，lunchtime为00:00:00。

分析：

由题干可知，使用 Insert 指令向表中插入数据即可；由于在新建 imooc_user 表时已经指定 lunchtime 的数据类型为time，在语句中写入格式化后的时间字符串，数据库会自动识别并存储。

语句：

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

插入成功后，结果为：

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

4. SQL Date

SQL Date 用于存储日期类数据类型，日期的有效值范围在1000-01-01 ~ 9999-12-31之间。

4.1 例2 更新用户

请书写 SQL 语句，更新imooc_user中的lucy用户，将其年龄设置为20，生日设置为2000-01-08。

分析：

题干中明确指出更新用户，使用 Update 指令，更新字段为年龄和生日。

语句：

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

更新成功后，结果为：

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

5. SQL Timestamp

SQL Timestamp 用来存储时间戳数据，不仅可以保存日期数据，还可以保存时间数据。

5.1 例3 修改用户表

请书写 SQL 语句，给imooc_user表新增create_time字段，字段类型为timestamp，并更新lucy用户的create_time字段的值为当前时间戳。

分析：

需先使用 Alter 指令给 imooc_user 新增 create_time 字段，字段类型为 timestamp，然后使用 Update 指令，更新 lucy 用户的 create_time 字段为当前时间戳。

提示： SQL 中提供了now函数可以方便的生成当前时间戳。

语句：

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

更新成功后，结果为：

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

6. 其它

在 SQL 标准中关于时间和日期的数据类型主要有三类，即time，date和timestamp。

不过在不同的数据库中，相应的时间会有所不同，如 MySQL 中还支持 datetime 来融合 date 和 time；而在 SQL Server 中不支持 timestamp 数据类型，仅支持 datetime 类型；PostgreSQL 对日期类型支持最为复杂，甚至还支持日期范围之类的日期格式。

7. 小结

timestamp 会受到时区因素影响，请提前设置数据库正确的配置信息保证数据的正确性。
datetime 和 timestamp 二者一定程度上可以通用，但是某些数据库只支持其中的一种。
time、date 和 timestamp 都可以像其它数据类型一样使用运算符，如>。