MySQL TIMESTAMP Update Warning

Question

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?

[This SO question](http://stackoverflow.com/questions/1002665/workaround-for-mysql-limited-timestamp-range) may interest you. — Ende Neu, Aug 26 '14 at 18:48
Ouch, I didn't see that comming. Changing the structure is a bit intrusive! — MonoThreaded, Aug 26 '14 at 18:49

score 3 · Answer 1 · answered Aug 26 '14 at 18:49

3

Here is the answer you're looking for:

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.

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

The date you're trying to set is to high.

answered Aug 26 '14 at 18:49

Joël Salamin

3,538
3
22
33

Nice catch. It worked with 2038-01-01. This means the y2k bug will be January 20th 2038 :) – MonoThreaded Aug 26 '14 at 18:51
Exactly, 23 years remaining before enjoying this y2k v2 bug ;-) – Joël Salamin Aug 26 '14 at 19:10

Rahul Tripathi · Accepted Answer · 2014-08-26T18:53:24.053

2

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..

edited Aug 26 '14 at 18:53

answered Aug 26 '14 at 18:47

Rahul Tripathi

168,305
31
280
331

The time was indeed out of range. It must be <= 2038 – MonoThreaded Aug 26 '14 at 18:51
@MonoThreaded:- Yes that was the issue! And as I mentioned in answer you need to change the datatype! – Rahul Tripathi Aug 26 '14 at 18:52

score 1 · Answer 3 · edited May 23 '17 at 12:27

1

You need to escape the column "time" since it's a keyword in MySql.

`time`

See this answer: i can't use 'time' word in mysql query?

edited May 23 '17 at 12:27

Community

1
1

answered Aug 26 '14 at 18:48

ShayneStatzell

122
1
11

This was actually a nice catch too – MonoThreaded Aug 26 '14 at 18:56
TIME is currently no [reserved word](https://dev.mysql.com/doc/refman/5.6/en/reserved-words.html) in MySQL. – VMai Aug 26 '14 at 19:12

MySQL TIMESTAMP Update Warning

3 Answers3