SQL Date & Time
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 都可以像其它数据类型一样使用运算符,如
>
。