ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL里面的时间类型 datetime,date,timestamp,time

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

原创 Linux操作系统 作者:adrain_001 时间:2012-04-15 22:14:28 0 删除 编辑

一、基本
如果要让这些字段存放诸如"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个字节

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26690043/viewspace-721247/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-13

  • 博文量
    112
  • 访问量
    263049