MySQL is giving me a hard time to update a timestamp column
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)
I would like to set the time of record 1 to Jan 1st 2040
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)
But it gives me an out of range warning
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)
Am I missing something obvious?
I think you need to change the datatype of your column from Timestamp to Datetime as for the limitation of Timestamp to take the maximum data as '2038-01-19 03:14:07' UTC..