SQL Date & Time

1. 定义

慕课解释:DateTime数据类型分别用于存储日期时间格式类数据。

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 标准中关于时间和日期的数据类型主要有三类,即timedatetimestamp

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

7. 小结

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