I know that if I create a table and set a column to Timestamp, it will
get automatically updated when the record is changed.

My question is, how do I CHANGE an existing column to make it be the
timestamp column? I tried changing it's type to timestamp, but it
doesn't get updated when I change the rows. Always stays the same.

On Jan 25, 7:58 pm, dterr...@hotmail.com wrote:
> I know that if I create a table and set a column to Timestamp, it will
> get automatically updated when the record is changed.
>
> My question is, how do I CHANGE an existing column to make it be the
> timestamp column? I tried changing it's type to timestamp, but it
> doesn't get updated when I change the rows. Always stays the same.


mysql Ver 14.12 Distrib 5.0.22

On Jan 25, 4:58 pm, dterr...@hotmail.com wrote:
> I know that if I create a table and set a column to Timestamp, it will
> get automatically updated when the record is changed.
>
> My question is, how do I CHANGE an existing column to make it be the
> timestamp column? I tried changing it's type to timestamp, but it
> doesn't get updated when I change the rows. Always stays the same.

Check the MySQL documentation for TRIGGERS.

http://dev.mysql.com/doc/refman/5.1/...e-trigger.html

On Jan 25, 9:33 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Jan 25, 4:58 pm, dterr...@hotmail.com wrote:
>
> > I know that if I create a table and set a column to Timestamp, it will
> > get automatically updated when the record is changed.
>
> > My question is, how do I CHANGE an existing column to make it be the
> > timestamp column? I tried changing it's type to timestamp, but it
> > doesn't get updated when I change the rows. Always stays the same.
>
> Check the MySQL documentation for TRIGGERS.
>
> http://dev.mysql.com/doc/refman/5.1/...e-trigger.html

I may have misread your post --

I'm suggesting that you use a datetime column and a trigger that keeps
it updated.

Forgive me if that's not what you were looking for.

dterrors@hotmail.com wrote:
> I know that if I create a table and set a column to Timestamp, it will
> get automatically updated when the record is changed.
>
> My question is, how do I CHANGE an existing column to make it be the
> timestamp column? I tried changing it's type to timestamp, but it
> doesn't get updated when I change the rows. Always stays the same.
>
>

It's only the first timestamp column created that gets this ability
so you'll need to move this column to be first in the schema with an
ALTER TABLE call or by recreating it.

--
Norman
Registered Linux user #461062

On Jan 26, 7:16 am, Norman Peelman <npeel...@cfl.rr.com> wrote:
> dterr...@hotmail.com wrote:
> > I know that if I create a table and set a column to Timestamp, it will
> > get automatically updated when the record is changed.
>
> > My question is, how do I CHANGE an existing column to make it be the
> > timestamp column? I tried changing it's type to timestamp, but it
> > doesn't get updated when I change the rows. Always stays the same.
>
> It's only the first timestamp column created that gets this ability
> so you'll need to move this column to be first in the schema with an
> ALTER TABLE call or by recreating it.
>
> --

But it's the *only* timestamp column. There are no others above or
below it.
I have to recreate the entire table to make it get updated? alter
table can't do this?

dterrors@hotmail.com wrote:
> On Jan 26, 7:16 am, Norman Peelman <npeel...@cfl.rr.com> wrote:
>> dterr...@hotmail.com wrote:
>>> I know that if I create a table and set a column to Timestamp, it will
>>> get automatically updated when the record is changed.
>>> My question is, how do I CHANGE an existing column to make it be the
>>> timestamp column? I tried changing it's type to timestamp, but it
>>> doesn't get updated when I change the rows. Always stays the same.
>> It's only the first timestamp column created that gets this ability
>> so you'll need to move this column to be first in the schema with an
>> ALTER TABLE call or by recreating it.
>>
>> --
>
> But it's the *only* timestamp column. There are no others above or
> below it.
> I have to recreate the entire table to make it get updated? alter
> table can't do this?
>
>
>

What makes you think it did not get changed?


mysql> create table xxx (a varchar(20));
Query OK, 0 rows affected (1.59 sec)

mysql> insert into xxx values ('2008-01-16 10:23');
Query OK, 1 row affected (0.22 sec)

mysql> select * from xxx
-> ;
+------------------+
| a |
+------------------+
| 2008-01-16 10:23 |
+------------------+
1 row in set (0.06 sec)


mysql> alter table xxx modify a timestamp;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from xxx;
+---------------------+
| a |
+---------------------+
| 2008-01-16 10:23:00 |
+---------------------+
Query OK, 1 rows affected (.13 sec)
Records: 1 Duplicates: 0 Warnings: 0

more:

mysql> insert into xxx values ('2008-01-26 10:23');
Query OK, 1 row affected (0.11 sec)

mysql> select * from xxx;
+---------------------+
| a |
+---------------------+
| 2008-01-16 10:23:00 |
+---------------------+
1 row in set (0.01 sec)

mysql> select cast(a as date) from xxx;
+-----------------+
| cast(a as date) |
+-----------------+
| 2008-01-26 |
+-----------------+
1 row in set (0.00 sec)

mysql> select cast(a as time) from xxx;
+-----------------+
| cast(a as time) |
+-----------------+
| 10:23:00 |
+-----------------+
1 row in set (0.01 sec)

mysql> alter table xxx modify a varchar(20);
Query OK, 1 row affected (1.40 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select cast(a as date) from xxx;
+-----------------+
| cast(a as date) |
+-----------------+
| 2008-01-26 |
+-----------------+
1 row in set (0.04 sec)

mysql> select cast(a as time) from xxx;
+-----------------+
| cast(a as time) |
+-----------------+
| 10:23:00 |
+-----------------+
1 row in set (0.01 sec)

mysql> select cast(a as datetime) from xxx;
+---------------------+
| cast(a as datetime) |
+---------------------+
| 2008-01-26 10:23:00 |
+---------------------+
1 row in set (0.01 sec)

On Jan 26, 8:48 pm, Michael Austin <maus...@firstdbasource.com> wrote:
> dterr...@hotmail.com wrote:
> > On Jan 26, 7:16 am, Norman Peelman <npeel...@cfl.rr.com> wrote:
> >> dterr...@hotmail.com wrote:
> >>> I know that if I create a table and set a column to Timestamp, it will
> >>> get automatically updated when the record is changed.
> >>> My question is, how do I CHANGE an existing column to make it be the
> >>> timestamp column? I tried changing it's type to timestamp, but it
> >>> doesn't get updated when I change the rows. Always stays the same.
> >> It's only the first timestamp column created that gets this ability
> >> so you'll need to move this column to be first in the schema with an
> >> ALTER TABLE call or by recreating it.
>
> >> --
>
> > But it's the *only* timestamp column. There are no others above or
> > below it.
> > I have to recreate the entire table to make it get updated? alter
> > table can't do this?
>
> What makes you think it did not get changed?
>
> mysql> create table xxx (a varchar(20));
> Query OK, 0 rows affected (1.59 sec)
>
> mysql> insert into xxx values ('2008-01-16 10:23');
> Query OK, 1 row affected (0.22 sec)
>
> mysql> select * from xxx
> -> ;
> +------------------+
> | a |
> +------------------+
> | 2008-01-16 10:23 |
> +------------------+
> 1 row in set (0.06 sec)
>
> mysql> alter table xxx modify a timestamp;
> Query OK, 1 row affected (0.13 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> select * from xxx;
> +---------------------+
> | a |
> +---------------------+
> | 2008-01-16 10:23:00 |
> +---------------------+
> Query OK, 1 rows affected (.13 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> more:
>
> mysql> insert into xxx values ('2008-01-26 10:23');
> Query OK, 1 row affected (0.11 sec)
>
> mysql> select * from xxx;
> +---------------------+
> | a |
> +---------------------+
> | 2008-01-16 10:23:00 |
> +---------------------+
> 1 row in set (0.01 sec)
>
> mysql> select cast(a as date) from xxx;
> +-----------------+
> | cast(a as date) |
> +-----------------+
> | 2008-01-26 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> select cast(a as time) from xxx;
> +-----------------+
> | cast(a as time) |
> +-----------------+
> | 10:23:00 |
> +-----------------+
> 1 row in set (0.01 sec)
>
> mysql> alter table xxx modify a varchar(20);
> Query OK, 1 row affected (1.40 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> select cast(a as date) from xxx;
> +-----------------+
> | cast(a as date) |
> +-----------------+
> | 2008-01-26 |
> +-----------------+
> 1 row in set (0.04 sec)
>
> mysql> select cast(a as time) from xxx;
> +-----------------+
> | cast(a as time) |
> +-----------------+
> | 10:23:00 |
> +-----------------+
> 1 row in set (0.01 sec)
>
> mysql> select cast(a as datetime) from xxx;
> +---------------------+
> | cast(a as datetime) |
> +---------------------+
> | 2008-01-26 10:23:00 |
> +---------------------+
> 1 row in set (0.01 sec)


I don't think you understand the original question. There are no
"update" statements in your response at all.

Mysql will let you have one timestamp column that automatically
updates if any other colument in the record is changed.

The problem is not that I can't convert a column's type to timestamp.
The problem is it's not updating automatically. If I had originally
created the column as a timestmap, it would work. But modifying a
column to become the timestamp doesn't.

I think I spelled this out in the original post.

On Jan 26, 10:35 pm, dterr...@hotmail.com wrote:
> On Jan 26, 8:48 pm, Michael Austin <maus...@firstdbasource.com> wrote:
>
>
>
> > dterr...@hotmail.com wrote:
> > > On Jan 26, 7:16 am, Norman Peelman <npeel...@cfl.rr.com> wrote:
> > >> dterr...@hotmail.com wrote:
> > >>> I know that if I create a table and set a column to Timestamp, it will
> > >>> get automatically updated when the record is changed.
> > >>> My question is, how do I CHANGE an existing column to make it be the
> > >>> timestamp column? I tried changing it's type to timestamp, but it
> > >>> doesn't get updated when I change the rows. Always stays the same.
> > >> It's only the first timestamp column created that gets this ability
> > >> so you'll need to move this column to be first in the schema with an
> > >> ALTER TABLE call or by recreating it.
>
> > >> --
>
> > > But it's the *only* timestamp column. There are no others above or
> > > below it.
> > > I have to recreate the entire table to make it get updated? alter
> > > table can't do this?
>
> > What makes you think it did not get changed?
>
> >mysql> create table xxx (a varchar(20));
> > Query OK, 0 rows affected (1.59 sec)
>
> >mysql> insert into xxx values ('2008-01-16 10:23');
> > Query OK, 1 row affected (0.22 sec)
>
> >mysql> select * from xxx
> > -> ;
> > +------------------+
> > | a |
> > +------------------+
> > | 2008-01-16 10:23 |
> > +------------------+
> > 1 row in set (0.06 sec)
>
> >mysql> alter table xxx modify a timestamp;
> > Query OK, 1 row affected (0.13 sec)
> > Records: 1 Duplicates: 0 Warnings: 0
>
> >mysql> select * from xxx;
> > +---------------------+
> > | a |
> > +---------------------+
> > | 2008-01-16 10:23:00 |
> > +---------------------+
> > Query OK, 1 rows affected (.13 sec)
> > Records: 1 Duplicates: 0 Warnings: 0
>
> > more:
>
> >mysql> insert into xxx values ('2008-01-26 10:23');
> > Query OK, 1 row affected (0.11 sec)
>
> >mysql> select * from xxx;
> > +---------------------+
> > | a |
> > +---------------------+
> > | 2008-01-16 10:23:00 |
> > +---------------------+
> > 1 row in set (0.01 sec)
>
> >mysql> select cast(a as date) from xxx;
> > +-----------------+
> > | cast(a as date) |
> > +-----------------+
> > | 2008-01-26 |
> > +-----------------+
> > 1 row in set (0.00 sec)
>
> >mysql> select cast(a as time) from xxx;
> > +-----------------+
> > | cast(a as time) |
> > +-----------------+
> > | 10:23:00 |
> > +-----------------+
> > 1 row in set (0.01 sec)
>
> >mysql> alter table xxx modify a varchar(20);
> > Query OK, 1 row affected (1.40 sec)
> > Records: 1 Duplicates: 0 Warnings: 0
>
> >mysql> select cast(a as date) from xxx;
> > +-----------------+
> > | cast(a as date) |
> > +-----------------+
> > | 2008-01-26 |
> > +-----------------+
> > 1 row in set (0.04 sec)
>
> >mysql> select cast(a as time) from xxx;
> > +-----------------+
> > | cast(a as time) |
> > +-----------------+
> > | 10:23:00 |
> > +-----------------+
> > 1 row in set (0.01 sec)
>
> >mysql> select cast(a as datetime) from xxx;
> > +---------------------+
> > | cast(a as datetime) |
> > +---------------------+
> > | 2008-01-26 10:23:00 |
> > +---------------------+
> > 1 row in set (0.01 sec)
>
> I don't think you understand the original question. There are no
> "update" statements in your response at all.
>
> Mysqlwill let you have one timestamp column that automatically
> updates if any other colument in the record is changed.
>
> The problem is not that I can't convert a column's type to timestamp.
> The problem is it's not updating automatically. If I had originally
> created the column as a timestmap, it would work. But modifying a
> column to become the timestamp doesn't.
>
> I think I spelled this out in the original post.

s/colument/column

On Jan 26, 7:35 pm, dterr...@hotmail.com wrote:

> Mysql will let you have one timestamp column that automatically
> updates if any other colument in the record is changed.
>
> The problem is not that I can't convert a column's type to timestamp.
> The problem is it's not updating automatically. If I had originally
> created the column as a timestmap, it would work. But modifying a
> column to become the timestamp doesn't.


Unless you're dealing with a quadrillion rows which make this
impractical -- try copying the old table into a new table that does
NOT have the timestamp column. Then alter that new table to add a NEW
timestamp column.

Failing that, create a new table that has all of the columns AND the
timestamp column, then copy the old table into it.

Sometimes working around a problem is faster than trying to figure it
out.

;-)