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.