Update record - set timestamp

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jeffman12
Forum Newbie
Posts: 3
Joined: Sat Feb 07, 2004 2:33 am
Location: Vancouver - CA

Update record - set timestamp

Post by jeffman12 »

I am making a demo website and one of the functions is the ability for the users to post comments to news articles. When the update function is used, the timestamp for the comment updated is set to the current time. The query to update the comment does not set the "cmt_time" = now().

Why is the timestamp updating?
Has any had this problem or know of any solutions?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

MySQL manual wrote: Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:
  • The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
  • The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
  • You explicitly set the TIMESTAMP column to NULL.
jeffman12
Forum Newbie
Posts: 3
Joined: Sat Feb 07, 2004 2:33 am
Location: Vancouver - CA

Post by jeffman12 »

So I should add to the query, cmt_time=cmt_time, to keep it the same value?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

exactly.
here is example which proves that your guess is right:

Code: Select all

mysql> select * from tst;
+----------------+------+
| t              | asd  |
+----------------+------+
| 20040207220401 | NULL |
+----------------+------+
1 row in set (0.00 sec)

mysql> select now()+0;
+----------------+
| now()+0        |
+----------------+
| 20040207220650 |
+----------------+
1 row in set (0.02 sec)

mysql> update tst set t=t, asd='asd';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tst;
+----------------+------+
| t              | asd  |
+----------------+------+
| 20040207220401 | asd  |
+----------------+------+
1 row in set (0.00 sec)

mysql>
jeffman12
Forum Newbie
Posts: 3
Joined: Sat Feb 07, 2004 2:33 am
Location: Vancouver - CA

Post by jeffman12 »

Sweet, thanks for the help!
Post Reply