MySQL里面的时间类型 datetime,date,timestamp,time

一、基本
如果要让这些字段存放诸如"2009-00-00"或"2009-11-31"之类的值，那么要为MySQL 指 定特别的SQL_MODE。
如果没有使用NO_ZERO_DATE的SQL_MODE，那么MySQL允许存放"0000-00-00"的值，有时候这样的值比null值方便。
（注意:TRADITIONAL模式也包含了NO_ZERO_DATE模式）
MySQL用标准格式输出时间，但解析某字符串成世界时，尝试多种格式。
年份若只有2位，70-99年解释为1970-1999年，00-69年解释为2000-2069年
要想被解析成时间，字符串总应该是“年-月-日”次序。
如果时间用在一个数字型的上下文中，时间将自动转为1个数字。
如果解析时，超过日期或时间表示范围，MySQL自动将其转为“零”值:(如果使用NO_ZERO_DATE模式，那么会产生warning)
datetime '0000-00-00 00:00:00'
date 

     '0000-00-00'
timestamp '0000-00-00 00:00:00'
time      '00:00:00'
year      0000
“零”值是特殊的，但可以直接用这些值，这些值可以简单地写成'0'或0
在MyODBC2.50.12及以上版本中，“零”值自动转为null值，因为MyODBC不能处理。

二、datetime,date和timestamp类型
datetime 显示为'YYYY-MM-DD HH:MM:SS'格式，范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
date      显示为'YYYY-MM-DD'格式，范围为'1001-01-01'到'9999-12-31'
timestamp 范围从'1970-01-01 00:00:01'UTC 到'2038-01-09 03:14:07'UTC
1. 指定值
指定datetime值时，可以用'2009-01-01 11:20:30','2009/01/01 11*20*30','2009*01*01 11^20^30','2009@01@01 11+20+30'等，效果是一样的。
指定date值时，与上类似。
指 定datetime值时，也可以用字符串'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式，如'20090101112030'或 '090101112030'，但'20090101119030'（分钟为90）会被解析为“零”值：'0000-00-00 00:00:00'。
指定date值时，与上类似。
指定datetime值时，也可以用数字YYYYMMDDHHMMSS或YYMMDDHHMMSS格式，如20090101112030或 090101112030。
指定date值时，与上类似。
指定datetime、date或timestamp值时，也可以用now()或current_date的值。
指定datetime、date或timestamp值时，月、日、时、分、秒，若小于10，可以只写1位，如'2009-01-01 11:30:30'与'2009-1-1 11:30:30'一样。
用数字指定时间时，可以为6、8、12或14位，6位表示YYMMDD，8位表示YYYYMMDD，12位表示YYMMDDHHMMSS，14位表示 YYYYMMDDHHMMSS。
用不带分割符的字符串指定时间时，如果字符串为8位或14位的，那么认为年份为YYYY，否则认为年份为YY，之后按顺序解析月日时分秒。
2. 不同类型转换
date值赋给datetime或timestamp时，时间部分为'00:00:00'。
datetime或timestamp值赋给date时，时间部分被去掉。
转换时注意其表示范围有所不同，超出范围的将被转为“零”值。
3. 注意'10:11:12'会被转为'2010-11-12 00:00:00'或'2010-11-12'。
4. timestamp类型
从当前时区转成UTC保存，要读取时，再从UTC转成当前时区。（不发生在datetime类型上）。
当前时区对应time_zone系统变量（缺省值为'System'表示用系统变量system_time_zone的值）
系统变量system_time_zone的值在启动MySQL时指定，一般去TZ环境变量，或执行mysqld_safe时用--timezone选 项。
（linux 安 装时挑选时区，安装后时区保存在/etc/sysconfig/clock文件里。）
若将timestamp类型字段定义为default current_timestamp，那么插入一条记录时，该timestamp字段自动被赋值为当前时间。
若将timestamp类型字段定义为on update current_timestamp，那么修改一条记录时，该timestamp字段自动被修改为当前时间。
在一个表里面的timestamp字段只能有下面4种定义：
a. 是default current_timestamp
插入时系统自动赋值，插入时若在SQL 语句中指定该字段的值，则用SQL语句中的指定值。
修改时时间值不自动改变，但可以在SQL语句中指定其值。
b. 是on update current_timestamp
插入时系统不自动赋值（字段为“零”值），但可以在SQL语句中指定该字段的值。
修改时，系统自动赋值，但若SQL语句中指定其值，则用SQL语句中的指定值。
c.两个都有default current_timestamp on update current_timestamp
如果timestamp后面啥都不写，也相当于2个都有。
插入修改时系统自动赋值，SQL语句中若指定，则用SQL语句中的指定值。
d.如果default 后面写个常数，如default 20090101010000，那么插入时即为这个值。其他与上面类似。
另外，一个表里面，如果想定义第2个timestamp字段，而且想让第2个字段有系统自动修改的机制，
那么之前的那个字段一定要赋予default 常数（即上面d），
然后，再给这个新的timestamp字段进行如上abc的定义，区别是这个新的timestamp字段不能后面啥不都写，
如果啥都不写，那么插入和修改时系统不会自动赋值。
依次类推，再想定义第3个timestamp字段，而且想让第3个字段有系统自动修改的机制，
那么之前第2个timestamp字段一定要赋予default 常数（即上面d），
然后，再给这个新的timestamp字段进行如上abc的定义，区别跟前面一样。
上述current_timestamp还可以用它的同义词 current_timestamp(),now(),localtime,localtime(),localtimestamp,localtimestamp() 代替。
timestamp字段缺省不允许空值，如果指定它的值为null，实际上就是用当前时间值赋给他了。
但是，可以这样定义:
col1 timestamp null default null, 注释：该字段可以为null，插入时缺省就是null，如果赋值null，那也真是null而不是当前时间值。
col2 timestamp null default 0,     注释：该字段可以null，插入时缺省为0，如果赋值null，那也真是null而不是当前时间值。
如果MySQL服务器以MAXDB SQL模式运行，那么timestamp等同于datetime类型，前面说的timestamp类型的所有描述都消失。

三、time类型
'HH:MM:SS'或'HHH:MM:SS'格式，范围为'-838:59:59'到'838:59:59'。
之所以小时值可以范围这么大，是因为time类型还可以用来表示两个时间点之差。
格式可以为'D HH:MM:SS.fraction'，其中D指示天（0～34），MySQL不存储D和fraction值。
可以为'D HH','HH:MM','HH:MM:SS','D HH','SS'，不能为'MM',或'HH'。注意：'11:12'被解释为'11:12:00'。
数字格式HHMMSS，可以为SS,MMSS,HHMMSS,HHMMSS.fraction，不能为MM或HH。注意：1112被解释为 '00:11:12'。
对于字符型来讲，时分秒如果小于10，可以写成1位。
合法的越界的值将被MySQL自动转换成靠近它的边界值，如'-850:00:00'被转换为'-838:59:59'。
不合法的值被MySQL自动转换为“零”值：'00:00:00'。 表中的0值不能区分是真实存储的0值还是因为不合法而被转换的0值。

四、year类型
year(2)或year(4)，缺省为year(4)
year(2) 范围为70(1970)-69(2069)，数字0表示2000年，'0'表示“零”值：0000
year(4) 范围为1901-2155
SQL语句中的Min(),Max()将把year转换成1个数字，在某些时候会产生问题。

五、存储空间
date 3个字节
time 3个字节
datetime 8个字节
timestamp 4个字节
year 1个字节