NOW() in inserts updates implicitly

Sorry for cross posting, just realised myodbc wasn't the place for
this:

Hello,

I'm having a problem with records inserted using NOW() as data in a
field having that timestamp updated when I alter another field. I am
using MySQL version 5.0.20-nt. To replicate:

use test;
CREATE TABLE date_test ( id INT, inserted TIMESTAMP, randomtext
VARCHAR(25) );
INSERT INTO date_test VALUES ( 1, NOW(), "Need water" );
INSERT INTO date_test VALUES ( 2, NOW(), "Very Thirsty" );
SELECT * FROM date_test;
+------+---------------------+--------------+
| id | inserted | randomtext |
+------+---------------------+--------------+
| 1 | 2006-06-26 14:56:43 | Need water |
| 2 | 2006-06-26 14:56:43 | Very Thirsty |
+------+---------------------+--------------+
2 rows in set (0.00 sec)
UPDATE date_test SET randomtext = "blah" WHERE id = 2;
mysql> SELECT * FROM date_test;
+------+---------------------+------------+
| id | inserted | randomtext |
+------+---------------------+------------+
| 1 | 2006-06-26 14:56:43 | Need water |
| 2 | 2006-06-26 14:57:06 | blah |
+------+---------------------+------------+
2 rows in set (0.00 sec)

You can see that the timestamp for the record with id 2 has changed its
time, even though it was not a part of the UPDATE statement.

I've looked through the documentation for NOW() on the MySQL document
site, and tried a variety of google searches with no luck. Does anybody
have a solution for this?

Re: NOW() in inserts updates implicitly

raisinodd wrote:
> I'm having a problem with records inserted using NOW() as data in a
> field having that timestamp updated when I alter another field. I am
> using MySQL version 5.0.20-nt. ...

> You can see that the timestamp for the record with id 2 has changed its
> time, even though it was not a part of the UPDATE statement.

That's what a TIMESTAMP does, it stamps the time into a field
automatically ON UPDATE.
For a date created field, just use a DATETIME datatype, then it can be
populated with NOW() for the INSERT and it will persist until you
change it intentionally.

-David Hedrick Skarjune

Re: NOW() in inserts updates implicitly

That's it. Thanks very much for the quick reply!

Skarjune wrote:
> raisinodd wrote:
> > I'm having a problem with records inserted using NOW() as data in a
> > field having that timestamp updated when I alter another field. I am
> > using MySQL version 5.0.20-nt. ...
>
> > You can see that the timestamp for the record with id 2 has changed its
> > time, even though it was not a part of the UPDATE statement.
>
> That's what a TIMESTAMP does, it stamps the time into a field
> automatically ON UPDATE.
> For a date created field, just use a DATETIME datatype, then it can be
> populated with NOW() for the INSERT and it will persist until you
> change it intentionally.
>
> -David Hedrick Skarjune