Fun with the MySQL TIMESTAMP data type (not!)

We’ve been bitten by this twice in the last few days, so maybe it is useful to others: the way MySQL handles its TIMESTAMP fields is…funny.

The most ROFLish part comes from http://dev.mysql.com/doc/refman/5.0/en/datetime.html:

The TIMESTAMP data type has varying properties, depending on the MySQL version and the SQL mode the server is running in.

Sounds fun already. In real life, my experience has been that sometimes MySQL updates some TIMESTAMP fields automatically when you UPDATE things.

Later in the same page there’s a simple suggestion to work around this:

UPDATE table_name SET my_timestamp=my_timestamp, new_data='something new' ;

You’ve been warned. Either don’t use TIMESTAMP fields, or read the docs very thoroughly and check in which funny mode your server happens to run, or use the above workaround.

Comments are closed.

%d bloggers like this: