3

There is some funny conversion side-effect that I experience when converting DATETIME column to TIMESTAMP.

There are DATETIME values before conversion:

+---------------------+
| creation_date       | 
+---------------------+
| 2015-02-18 19:57:52 | 
| 2015-02-18 19:57:18 | 
| 2015-02-18 19:51:52 | 
| 2015-02-18 19:51:29 | 
+---------------------+

ALTER TABLE t CHANGE creation_date creation_date TIMESTAMP null DEFAULT null;

Same data after the conversion to TIMESTAMP:

+---------------------+
| creation_date       | 
+---------------------+
| 2015-02-18 19:58:52 | 
| 2015-02-18 19:58:37 | 
| 2015-02-18 19:53:52 | 
| 2015-02-18 19:52:52 | 
+---------------------+

No warnings were given during conversion.

I'm curious what is the reason of this change? Notice, that the delta for every row is different. For the first row it's one minute, for the second 79 seconds.

MySQL version: 5.1.73

vadchen
  • 1,442
  • 1
  • 11
  • 14
  • 1
    I'm on 5.1.6, but did same thing with same date values, and the values did **not** change, even changing back to `DATETIME`. I.e. works on my machine. My db server is on Linux (CentOS). – Marcus Adams Feb 19 '15 at 18:53
  • 1
    http://stackoverflow.com/questions/409286/datetime-vs-timestamp , did another transaction occur on that table right after or during your alter? – Hozikimaru Feb 19 '15 at 18:57
  • @SurgeonofDeath maybe, but why should it matter? – vadchen Feb 19 '15 at 19:35
  • @vadchen While you are running your script, some other process could have updated those values so the difference could be related to the actual update that was made by another process. If you have logs of the transactions, I would recommend checking. – Hozikimaru Feb 19 '15 at 19:42
  • Ah, no, these rows are not updated after they are inserted. Only new rows can be added in this particular table. – vadchen Feb 19 '15 at 20:26
  • Can you provide a test case (CREATE + INSERT + ALTER)? – Rick James Apr 21 '15 at 02:45

1 Answers1

0

I'm going to guess that additional (later) event records were added between your original query and your following query as hinted at by SurgeonofDeath.

Perhaps you could add some ID values to your query... and/or check that the same times identified in the original query are in fact still present in the database?

A Smith
  • 621
  • 1
  • 4
  • 10