List:General Discussion« Previous MessageNext Message »
From:Zhe Wang Date:February 28 2005 6:07pm
Subject:Re: problem with adding timestamp as a column
View as plain text  
Hi, Joerg,

   Thank you very much for your clear reply. Now I completely understand 
what is going on behind the screen.

    Also a big thanks to all the people who gave me kind reply.

Regards,
Zhe

Joerg Bruehe wrote:

> 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
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

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