SQL 日期和时间类型

概述

SQL 的 日期和时间 类型比较简单

日期和时间类型 字节 最小值 最大值
date 4 1000-01-01 9999-12-31
time 3 -838:59:59 838:59:59
year 1 1901 2155
datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp 4 19700101080001 2038年的某个时刻

特点

\ 字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038

使用

创建

create table num.dt (
    d1 datetime,
    d2 timestamp,
    d3 time
)
`

查看结构

mysql> desc dt;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d1    | datetime  | YES  |     | NULL              |                             |
| d2    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| d3    | time      | YES  |     | NULL              |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)

插入数据

insert into num.dt values(now(),now(),curtime());

查看数据

mysql> select * from dt;
+---------------------+---------------------+----------+
| d1                  | d2                  | d3       |
+---------------------+---------------------+----------+
| 2020-03-07 19:27:06 | 2020-03-07 19:27:06 | 19:27:06 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)
发表评论 / Comment

用心评论~