John John - 16 days ago 5
MySQL Question

mySQL - maximum value for a timestamp

I am using a mySQL database to store a range of inputs that feed a model. I have a number of different dates that are stored as TIMESTAMP. However, some of the values can be hundreds of years in the future. When I look in the DB, they are stored as '0000-00-00 00:00:00' when the actual timestamp should be something like '2850-12-01 00:00:00'.

While searching on Google, I noticed that the maximum value is sometime in 2038. Has anyone found a work-around for longer-dated TIMESTAMPs?

Answer

You can convert them to DATETIME, it will store what you want. Compare:

MariaDB [test]> create table t (t timestamp, d datetime);
Query OK, 0 rows affected (0.59 sec)

MariaDB [test]> insert into t values ('2850-12-01 00:00:00','2850-12-01 00:00:00');
Query OK, 1 row affected, 1 warning (0.08 sec)

MariaDB [test]> select * from t;
+---------------------+---------------------+
| t                   | d                   |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2850-12-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)