Tiiimestamp ain't on my side

No it ain't.

I had an existing MySQL table with a few columns, e.g. x, y, and z, and I needed to add a timestamp column `ts` to it. But when I tried, the column was filled with "<Error>" values. Read on to see how I fixed it.

Naturally, I tried this first:

ALTER TABLE mytable add column `ts` timestamp NOT NULL default CURRENT_TIMESTAMP after `z`;

I'm lookin' for good timestamps:

SELECT * FROM mytable LIMIT 2;

What do I see? (With my favorite SQL client, SQuirreL, that is...)

x y z ts
1 2 3 <Error>
4 5 6 <Error>

You'll come erroring back to meeeeee!

So why did it do that? I still don't know. It wasn't the "after" clause. What I do know is that I found mention here of a method to change a column to give it the current timestamps:

Posted by Ayden Bissessar on December 6 2010 4:28pm:

...The syntax below will work. You also need to put the column name twice, I don't know why, it just works.

"ALTER TABLE tablename CHANGE columnname columnname TIMESTAMP DEFAULT CURRENT_TIMESTAMP;"

So that's good, but it doesn't add the column in the first place. Adding it as above didn't work either. What finally worked?

ALTER TABLE mytable ADD COLUMN `ts` timestamp NULL default NULL after `z`;
ALTER TABLE mytable CHANGE `ts` `ts` timestamp NOT NULL default CURRENT_TIMESTAMP;

I also don't know why, it just works. But it appears I was very lucky to assign NULL to the timestamps initially. When timestamps are initialized to NULL changing the column sets them to the current time, just like adding a row with a NULL timestamp does. Any other initial value seems to result in "<Error>".

Hm, now that I've defeated this time thing, does that make me...a Time Lord? Who knows?

With apologies to The Stones among others, and to the BBC.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Each email address will be obfuscated in a human readable fashion or (if JavaScript is enabled) replaced with a spamproof clickable link.

More information about formatting options

CAPTCHA
This question tests whether you are a human visitor, to prevent automated spam submissions.
Question text provided by textcaptcha.com