31

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).

aditsu quit because SE is EVIL
  • 4,019
  • 1
  • 34
  • 48
  • This is honestly very strange behaviour in MySQL, I'm curious as to why they might've done this. `DEFAULT CURRENT_TIMESTAMP` is still understandable, but why add the `on update` attribute? – Sumit May 10 '22 at 15:48

3 Answers3

14

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.

Ilion
  • 6,772
  • 3
  • 24
  • 47
  • 3
    Hmm, interesting. I was able to do this: `alter table tstest modify ts timestamp not null default 0;` and afterwards: `alter table tstest alter column ts drop default;` That seems to give the results I wanted. I wonder if there's a more direct way, ideally in the create table statement. – aditsu quit because SE is EVIL Mar 17 '12 at 10:07
  • So for now I can do it in 2 steps: create table (with default 0) and then drop default. But the weird thing is if I try to insert a null value, it still uses the current timestamp (and if I don't specify a value, it uses 0). mysql, y u no make sense? – aditsu quit because SE is EVIL Mar 17 '12 at 10:29
  • It appears to be a special case. See [here](http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html): _By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp._ – Ilion Mar 17 '12 at 10:40
  • Also it sounds like you are not running in strict mode. See this to really understand what's going on: http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html – Ilion Mar 17 '12 at 10:44
  • By default, yes, but even after changing the defaults, the "assigning NULL" part still seems to hold. – aditsu quit because SE is EVIL Mar 17 '12 at 10:44
  • Yes you're dealing with some implicit default behaviour due to the not null clause and (probably) not running in strict mode. – Ilion Mar 17 '12 at 10:45
  • I switched the sql mode to traditional, and the CURRENT_TIMESTAMP default is still effectively present (although I dropped it). I guess it's hardcoded. Anyway, thanks for your answers. – aditsu quit because SE is EVIL Mar 17 '12 at 11:11
  • @MirroredFate I would accept it if Ilion edits it to include the additional details discussed in the comments - especially the fact that it's not possible to avoid the CURRENT_TIMESTAMP effective default value – aditsu quit because SE is EVIL Feb 21 '14 at 23:18
  • I don't think what you wrote is correct. I inserted null in strict mode and got no error. – aditsu quit because SE is EVIL Mar 03 '14 at 17:21
  • the real question here is why MySQL does that – 8ctopus Aug 17 '22 at 09:25
0

I've not had any luck with the DROP DEFAULT command; and it looks like the explicit_defaults_for_timestamp is also affecting this (I'd rather not change these config values, incase I need to move to a new server).

But one solution I'm using is to specify "0" as the default, it stops the special behavior on update, and it basically provides the same default as DATETIME fields:

ALTER TABLE `table_name` ADD `column_name` TIMESTAMP(4) DEFAULT 0 NOT NULL;
Craig Francis
  • 1,855
  • 3
  • 22
  • 35
-1

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`;
Boris D. Teoharov
  • 2,319
  • 4
  • 30
  • 49