MySQL 日期和时间类型
MySQL 有 5 种表示时间值的日期和时间类型, 分别为, DATE,TIME,YEAR,DATETIME,TIMESTAMP.
TIMESTAMP 类型有专有的自动更新特性,
TIMESTAMP 类型有专有的自动更新特性,
TIMESTAMP 类型有专有的自动更新特性,
日期类型
类型 | 大小 < br ztid="101" ow="0" oh="0">(字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
如果要用来表示年月日, 通常用 DATE 来表示.
如果要用来表示年月日时分秒, 通常用 DATETIME 表示.
如果只用来表示时分秒, 通常用 TIME 来表示.
TIMESTAMP 表示格式 :YYYY-MM-DD HH:MM:SS.
如果只是表示年份, 可以用 YEAR 来表示. DATE,DATETIME,TIME 是常用三种.
在 MySQL 中创建表时, 对照上面的表格, 很容易就能选择到合适自己的数据类型. 不过到底是选择 datetime 还是 timestamp, 可能会有点犯难. 这两个日期时间类型各有优点: datetime 的日期范围比较大; timestamp 所占存储空间比较小, 只是 datetime 的一半.
另外, timestamp 类型的列还有个特性: 默认情况下, 在 insert, update 数据时, timestamp 列会自动以当前时间 (CURRENT_TIMESTAMP) 填充 / 更新."自动" 的意思就是, 你不去管它, MySQL 会替你去处理.
建表的代码为:
- create table t8 (
- `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
- `id2` datetime default NULL
- );
一般情况下, 我倾向于使用 datetime 日期类型.
两者之间的比较:
1. timestamp 容易所支持的范围比 timedate 要小. 并且容易出现超出的情况
2.timestamp 比较受时区 timezone 的影响以及 MySQL 版本和服务器的 SQL MODE 的影响.
MySQL 时间类型: 时间格式, 所占存储空间, 时间范围.
时间类型 存储空间 时间格式 时间范围
- ------------ --------- --------------------- -----------------------------------------
- time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59
time 时间范围居然有这么大的范围, 特别是 time 可以取负值, 有点奇怪. 后来, 看了 MySQL 手册才知道这是为了满足两个日期时间相减才这样设计的.
- select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
- select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59
- select timediff('23:59:59', '12:00:00'); -- 11:59:59
注意, timediff 的两个参数只能是 datetime/timestamp, time 类型的, 并且这两个参数类型要相同. 即: datetime/timestamp 和 datetime/timestamp 比较; time 和 time 相比较.
虽然 MySQL 中的日期时间类型比较丰富, 但遗憾的是, 目前 (2008-08-08) 这些日期时间类型只能支持到秒级别, 不支持毫秒, 微秒. 也没有产生毫秒的函数.
CREATE TABLE t(d DATE,t TIME,dt DATETIME)
-- 插入当前日期
INSERT INTO t VALUES(NOW(),NOW(),NOW());
-- 查看
SELECT * FROM t;
TIMESTAMP 也用来表示日期, 和时区相关(DATETIME 则只能反应出插入时当地的时区, 其他时区的人查看数据必然会有误差的.)
- CREATE TABLE t3(d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
- INSERT INTO t3 VALUES(NOW());
最后采用不同的格式将日期 "2007-9-3 12:10:10" 插入到 DATETIME 列中
- CREATE TABLE t6(dt DATETIME);
- INSERT INTO t6 VALUES('2007-9-3 12:10:10');
- INSERT INTO t6 VALUES('2007/9/3 12+10+10');
- INSERT INTO t6 VALUES('20070903121010');
- INSERT INTO t6 VALUES(20070903121010);
- SELECT * FROM t6;
来源: http://www.linuxidc.com/Linux/2018-09/154334.htm