problem with adding timestamp as a column
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 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -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 time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. Thank you very much! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with adding timestamp as a column
I'd suggest not using the keyword timestamp as a column name. I'd suggest using ts or tstamp or something like that. 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. 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 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -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 time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. Thank you very much! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with adding timestamp as a column
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 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -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]
Re: problem with adding timestamp as a column
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 [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 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -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]
