mysql40 timestamp

Hi,

I was recently forced to go back to 4.0 and I'm having a problem with a
timestamp column that gets updated whenever the row gets modified. I
want the timestamp to only carry the value for when the row is created
in the db here is my current table creation statement:

CREATE TABLE `wall_data` (
`id` int(11) NOT NULL auto_increment,
`origin` varchar(60) default NULL,
`poster` varchar(60) default NULL,
`votes` varchar(60) default '0',
`message` varchar(140) default NULL,
`posted` timestamp default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Thanks!

Dan

Daniel wrote:
> Hi,
>
> I was recently forced to go back to 4.0 and I'm having a problem with a
> timestamp column that gets updated whenever the row gets modified. I
> want the timestamp to only carry the value for when the row is created
> in the db here is my current table creation statement:
>
> CREATE TABLE `wall_data` (
> `id` int(11) NOT NULL auto_increment,
> `origin` varchar(60) default NULL,
> `poster` varchar(60) default NULL,
> `votes` varchar(60) default '0',
> `message` varchar(140) default NULL,
> `posted` timestamp default NULL,
> PRIMARY KEY (`id`)
> ) TYPE=MyISAM;
>
> Thanks!
>
> Dan
Can you post the queries that are used to update it?

"Daniel" <dcoleyoung@gmail.com> wrote:
>
> I was recently forced to go back to 4.0 and I'm having a problem with a
> timestamp column that gets updated whenever the row gets modified. I
> want the timestamp to only carry the value for when the row is created

This is not possible in MySQL versions before 4.1.2. The semantics of
the TIMESTAMP type was changed then. Please see the manual:

<http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html>


If you want to keep the value in the first TIMESTAMP column in MySQL
4.0 you have to put the column in the UPDATE statement and assign it
to itself:

CREATE TABLE foo (id int, bar int, stamp TIMESTAMP);
INSERT INTO foo (id, bar) VALUES (4711, 0);
UPDATE foo SET bar=42, stamp=stamp WHERE id=4711;


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Got it to work. Thanks!
Axel Schwenke wrote:
> "Daniel" <dcoleyoung@gmail.com> wrote:
> >
> > I was recently forced to go back to 4.0 and I'm having a problem with a
> > timestamp column that gets updated whenever the row gets modified. I
> > want the timestamp to only carry the value for when the row is created
>
> This is not possible in MySQL versions before 4.1.2. The semantics of
> the TIMESTAMP type was changed then. Please see the manual:
>
> <http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html>
>
>
> If you want to keep the value in the first TIMESTAMP column in MySQL
> 4.0 you have to put the column in the UPDATE statement and assign it
> to itself:
>
> CREATE TABLE foo (id int, bar int, stamp TIMESTAMP);
> INSERT INTO foo (id, bar) VALUES (4711, 0);
> UPDATE foo SET bar=42, stamp=stamp WHERE id=4711;
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/