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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
