469,625 Members | 1,088 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

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?

Jun 26 '06 #1
2 1430

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

Jun 26 '06 #2
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


Jun 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by kerri | last post: by
1 post views Thread by Craig Stadler | last post: by
reply views Thread by Steve - DND | last post: by
reply views Thread by Dimitris Milonas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.