MySQL 日期和时间类型

MySQL表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

 

如果试图插入一个不合法的日期，MySQL将给出警告或错误。可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如'1999-11-31'。当你想要保存一个“可能错误的”用户已经在数据库中指定(例如，以web形式)用于将来处理的值时很有用。在这种模式下，MySQL只验证月范围为从0到12，日范围为从0到31。这些范围可以包括零，因为MySQL允许在DATE或DATETIME列保存日/月和日是零的日期。这在应用程序需要保存一个你不知道确切日期的生日时非常有用。在这种情况下，只需要将日期保存为'1999-00-00'或'1999-01-00'。如果保存此类日期，DATE_SUB()或DATE_ADD等需要完整日期的函数不会得到正确的结果。(如果你不想在日期中出现零，可以使用NO_ZERO_IN_DATE SQL模式)。

MySQL还允许将'0000-00-00'保存为“伪日期”(如果不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。

 

当使用日期和时间类型时应记住以下几点：

MySQL以标准输出格式检索给定日期或时间类型的值，但它尽力解释你指定的各种输入值格式(例如，当你指定一个分配给或与日期或时间类型进行比较的值时)。只支持下面章节中描述的格式。期望你能提供有效值。如果你使用其它格式的值会发生意想不到的结果。

包含两位年值的日期会令人模糊，因为世纪不知道。MySQL使用以下规则解释两位年值：

70-99范围的年值转换为1970-1999。

00-69范围的年值转换为2000-2069。

  尽管MySQL尝试解释几种格式的值，日期总是以年-月-日顺序(例如，'98-09-04')，而不是其它地方常用的月-日-年或日-月-年顺序(例如，'09-04-98'，'04-09-98')。

  如果值用于数值上下文中，MySQL自动将日期或时间类型的值转换为数字，反之亦然。

  当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时(如本节开始所描述)，它将该值转换为该类的“零”值。一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点。

下面的表显示了各类“零”值的格式。请注意如果启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。

列类型	“零”值
DATETIME	'0000-00-00 00:00:00'
DATE	'0000-00-00'
TIMESTAMP	00000000000000
TIME	'00:00:00'
YEAR	0000

  “零”值是特殊值，但你可以使用表内显示的值显式保存或引用它们。你也可以使用值'0'或0来保存或引用，写起来更容易。

  MyODBC中使用的“零”日期或时间值在MyODBC 2.50.12和以上版本中被自动转换为NULL，因为ODBC不能处理此类值。

 

具体类型区别就不在这里写了。

 

另外TIMESTAMP的一个小应用在这里分享一下：

 

timestamp一个功能是在定义一个字段的时候可以default current_timestamp 和 on update current_timestamp；即当记录Item insert和update的时间。

但是有一个约定，如果需要一个表里有两个字段，一个自动记录insert的时间，另一个记录update的时间，这个时候定义是不支持的；

 

 

mysql> create table demo(demo_insert timestamp default current_timestamp,demo_updated timestamp on update current_timestamp,demo_value varchar(100));


ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

 

 

现在把那条SQL稍微变一下：

mysql> create table demo(demo_insert timestamp default 0,demo_updated timestamp on update current_timestamp,demo_value varchar(100));                 
Query OK, 0 rows affected (0.02 sec)

 

 

 

这样是OK的。

 

现在看看如何应用：

 

mysql> show create table demo\G;
*************************** 1. row ***************************
       Table: demo
Create Table: CREATE TABLE `demo` (
  `demo_insert` timestamp NOT NULL default '0000-00-00 00:00:00',
  `demo_updated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `demo_value` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)


mysql> insert into demo (demo_insert, demo_value) values (null,'http://alex-j.iteye.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo;
+---------------------+---------------------+---------------------------+
| demo_insert         | demo_updated        | demo_value                |
+---------------------+---------------------+---------------------------+
| 2010-08-24 17:22:53 | 0000-00-00 00:00:00 | http://alex-j.iteye.com |
+---------------------+---------------------+---------------------------+
1 row in set (0.00 sec)


mysql> update demo set demovalue='alex-j.iteye.com';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from demo;                            
+---------------------+---------------------+--------------------+
| demo_insert         | demo_updated        | demo_value         |
+---------------------+---------------------+--------------------+
| 2010-08-24 17:22:53 | 2010-08-24 17:24:45 | alex-j.iteye.com |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)

 

为什么可以这样用，我们看看文档说明

TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp.

If you insert NULL into a TIMESTAMP column, the current date and time is inserted。

 

所以为什么我们写第一条Insert SQL的时候

insert into demo (demo_insert, demo_value) values (null, 'http://alex-j.iteye.com');