List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 28 2005 5:20pm
Subject:Re: problem with adding timestamp as a column
View as plain text  
Hi!

Am Mo, den 28.02.2005 schrieb Hank um 17:09: [top-posting reordered!]
> On Mon, 28 Feb 2005 10:05:32 -0500, Zhe Wang <zwang@stripped> wrote:
> > Hi, there,
> > 
> >  I have MySQL 4.1.10. I need to add a timestamp column to an existing
> > table. I am having a problem of setting the newly added column to be the
> > current time.
> > 
> >  This is what I did:
> > 
> > CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3);
> > ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
> > SELECT * FROM try;
> > 
> > +------+---------------------+
> > | id   | timestamp           |
> > +------+---------------------+
> > |    1 | 0000-00-00 00:00:00 |
> > |    2 | 0000-00-00 00:00:00 |
> > |    3 | 0000-00-00 00:00:00 |
> > +------+---------------------+
> > 
> >  I've  read the on-line manual regarding the change in timestamp, still
> > couldn't figure out why 0's got inserted instead of a meaningful current

They did not get inserted - see below.

> > time stamp. I would greatly appreciate if someone can let me know what
> > the correct way is. Unfortunately I cannot recreate the table.
> > 
> >   [[...]]
> > 
> I'd suggest not using the keyword "timestamp" as a column name.  I'd
> suggest using "ts" or "tstamp" or something like that.

I second that - avoid using keywords as identifiers!

> 
> To update the records to the current timestamp....:
> 
> update try set tstamp=null;
> 
> should do it.  I don't know why the default isn't working, though.

The "default" is a value that is used when a row is inserted but no
value for this columnt is provided. It is applied at insert time, but
not at select time - here simply the existing value is returned.
"ALTER TABLE ADD" creates the new column, but it does no inserts.

The general procedure for such situations is:
1) Add the new column to the table.
2) Add code handling this column to all your triggers, stored
procedures, web interfaces, application programs, ... that enter data
into your DB.
3) Use an Update command to set the default value into this column in
all rows where it is still empty or NULL. (These are the rows inserted
before the respective application provided a value.)
4) Add code handling this column to all data output functions. (Now you
are sure all DB contents is valid.)

If necessary: Repeat steps 2), 3), and 4) if you missed an application,
or if your logic demanded you to have valid contents immediately.

HTH,
Joerg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Are you MySQL certified?  www.mysql.com/certification

Thread
problem with adding timestamp as a columnZhe Wang28 Feb
  • Re: problem with adding timestamp as a columnHank28 Feb
    • innodb_buffer_pool_size - max_connections?Deluxe Web28 Feb
      • INNODB MONITOR OUTPUTDeluxe Web3 Mar
    • Re: problem with adding timestamp as a columnJoerg Bruehe28 Feb
      • Re: problem with adding timestamp as a columnZhe Wang28 Feb