MySQL TIMESTAMP 更新警告

Question

MySQL 让我很难更新时间戳列

mysql> describe payments;
+---------+------------------+------+-----+-------------------+----------------+
| Field   | Type             | Null | Key | Default           | Extra          |
+---------+------------------+------+-----+-------------------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| user_id | int(10) unsigned | NO   |     | NULL              |                |
| cat_id  | int(10) unsigned | NO   |     | NULL              |                |
| amount  | int(11)          | NO   |     | NULL              |                |
| time    | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+---------+------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

我想将记录 1 的时间设置为 2040 年 1 月 1 日

mysql> select * from payments;
+----+---------+--------+--------+---------------------+
| id | user_id | cat_id | amount | time                |
+----+---------+--------+--------+---------------------+
|  1 |       1 |      4 |     40 | 0000-00-00 00:00:00 |
|  2 |       1 |      3 |     30 | 2030-01-01 00:00:00 |
+----+---------+--------+--------+---------------------+
2 rows in set (0.00 sec)

但是它给了我一个超出范围的警告

mysql> update payments set time='2040-01-01 00:00:00' where id=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'time' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

我是否漏掉了一些明显的东西？

Answer 1

这是您正在寻找的答案:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

来源:http://dev.mysql.com/doc/refman/5.0/en/datetime.html

您尝试设置的日期过高。