Timestamp vs. Datetime

We encountered a strange behavior in our Rails application: created_on timestamp was sometimes younger than updated_at_.

For each item in our application, we had a created_on and an updated_at field in the database. These were supposed to be updated by the Rails framework at item creation and item update times respectively. However, created_on got to be updated more reliably, even for small flag changes. We tried

but things got even worse: created_on kept changing with every little update but updated_at remained untouched. So, we sometimes had a strange situation that created_on was younger than updated_at.

So what could help? Read the good old manual? Well, sometimes you just have to…

And there it was: even though Rails does not seem to distinguish much between :datetime and :timestamp, our MySQL database has the following default behaviour:

The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL.

So the innocently looking:

in create_tables.sql is completely wrong (don’t ask me how it got there, it has survived an unbelievable number of refactorings). This is made obvious by

What helped us in the end was:

Note: actually the error showed only for small flag changes, done by update_attributes. When using the full update, Rails keeps the correct old value of created_on.

2 Responses to “Timestamp vs. Datetime”

  1. afsina Says:

    and you are trolling javablogs because?

  2. Samia Says:

    “What helped us in the end was:

    mysql>alter table items modify created_on datetime NOT NULL;”

    Why did you have to change created_on field from timestamp to datetime? You could have just specified “default current_timestamp” explicitely without specifying “on update”.

    `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP

    I am missing something?