MySQL日期、时间类型相关

 

MySQL 日期类型：日期格式、所占存储空间、日期范围 比较。

 

 日期类型      存储空间        日期格式                                           日期范围
------------  ---------       ---------------------             -----------------------------------------
 datetime     8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
 timestamp  4 bytes   YYYY-MM-DD HH:MM:SS    1970-01-01 00:00:01 ~ 2038
 date           3 bytes    YYYY-MM-DD                    1000-01-01          ~ 9999-12-31
 year           1 bytes    YYYY                                1901                ~ 2155

 

 

在 MySQL 中创建表时，对照上面的表格，很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp，可能会有点犯难。这两个日期时间类型各有优点：datetime 的日期范围比较大；timestamp 所占存储空间比较小，只是 datetime 的一半。

 

另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。

 

一般情况下，我倾向于使用 datetime 日期类型。

 

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 中的日期时间类型比较丰富，但遗憾的是，这些日期时间类型只能支持到秒级别，不支持毫秒、微秒。也没有产生毫秒的函数。

 

 

now() 只能精确到秒。 

MySQL 中也没有存储带有毫秒、微秒的日期时间类型。

但是 MySQL 已经有抽取（extract）微秒的函数。例如：

 

select microsecond('12:00:00.123456');                          -- 123456

select microsecond('1997-12-31 23:59:59.000010');               -- 10

select extract(microsecond from '12:00:00.123456');             -- 123456

select extract(microsecond from '1997-12-31 23:59:59.000010');  -- 10

select date_format('1997-12-31 23:59:59.000010', '%f');         -- 000010

尽管如此，想在 MySQL 获得毫秒、微秒还是要在应用层程序中想办法。

假如在应用程序中获得包含微秒的时间：1997-12-31 23:59:59.000010，

在 MySQL 存放时，可以设计两个字段：c1 datetime, c2 mediumint，分别存放日期和微秒。

为什么不采用 char 来存储呢？用 char 类型需要 26 bytes，而 datetime + mediumint 只有 11（8+3） 字节。

 

 

参考地址1：http://mlxia.iteye.com/blog/278982

参考地址2：http://blog.****.net/lckj2009/article/details/6091993