False year inserted in SQL table

Question

The timestamp of the nodes in my node table have year as 201 instead of 2014 which are stored in unixtime. Is there a way in which I can update only the year of all the nodes concerned?

Answer 1

MySQL timestamp field allows a range of values between -2147483648 and 2147483648. (A detailed explanation can be found here.) The Unix time equivalent of "201-01-01 00:00:00" is -55824249600. So, MySQL timestamp field cannot save a date from year 201.

If my above assumption is wrong and a date from year 201 exists in a timestamp field on your database, I think the best solution is;

• Calculate the interval between 201-01-01 00:00:00 and 2014-01-01 00:00:00 in seconds:

  1388534400 - (-55824249600) = 57212784000
  

• Add this value to all "created" fields in your "node" table with the following SQL query:

  UPDATE node SET created = created + 57244320000;