Is it possible to define a timestamp column that is not null and has no default and no special behavior on update?

28
votes

When I create a table with a timestamp column, that column is magically defined as NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP. Ignoring how weird this is so far, I would like to change it to have no default and no special "on update" behavior.

I found that if I change the column to be NULL, the default is magically set to NULL and the "on update" magically disappears. This is great, however I would like the column to be NOT NULL. When I change it back, both the default and "on update" (set to CURRENT_TIMESTAMP) magically reappear.

I know I could use datetime instead of timestamp, but I'm interested in timestamp because it is timezone-aware (seems to be like "timestamp with time zone" in Postgres).

2
mysqltimestamp

2 Answers

11
votes

Timestamp columns are a special case. See here: By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

For more detailed information read up on Data Type Default Values.

Specifically that situation applies when not running in strict mode. If running in strict mode, inserting a NULL will throw an error.

This should take care of it:

ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT;

If that doesn't work, doing this is supposed to leave you with the default (easily overwritten) but remove the ON UPDATE:

ALTER TABLE tableName CHANGE columnName columnName NOT NULL DEFAULT CURRENT_TIMESTAMP;

Note the repeated column name.

-1
votes

Here is a possible workaround (in case updating schema is currently not an option for some reason): Disable ON UPDATE

Basically, you can set the timestamp value to its original value like that:

UPDATE my_table
  SET `the_value_that_i_am_updating`="The new value", 
      `changed_at`=`changed_at`;