MySQL Datetime/Timestamp Behaviors

MySQL has a datetime data type that is great for storing dates across a wide range of years, 1000 to 9999., but a downside of this data type is it doesn't support timezones. This is unfortunate because if you use different database clients/applications, across time zones, the client has to know how to handle the time zone. Rails converts the time to GMT prior to persisting, and converts it back to the configured time zone on retrieval. This works fine in Rails but breaks once you leave Rails to another client that doesn't handle timezones. Another caveat is if you are using sql date/time functions with the datetime data type, it of course will be operating on GMT, not your timezone.

The timestamp data type supports unix timestamps (epoch to 2038), but it has side-effects. The main and extremely undesirable side-effect, in my case, is that it can't be null. For example, when you add a timestamp field: ALTER TABLE mytable ADD `foo_time` timestamp, it automatically gets default behavior

 `foo_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

It attaches a function to update the field automatically. So, if you run

update table set foo_time = null

MySQL will actually set the field to current time.

You can default the timestamps to 0, or another time but it can never be null.

Now if you set it to a non-null value, it works

update websites set foo_time = '2012-01-01'

In conclusion, you have to decide if timestamp support is important for your application, and if it is, be cognizant of the behaviors of this data type.

More reading here http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html