[RESOLVED] Timestamp changes during any update to the table.

fr8

Everytime I update a table the timestamp column will also update even though i do not specify it in my sql query.

UPDATE statistics SET bob=bob+1 WHERE cid='$cid' LIMIT 1

Thats my sql query. Notice that i do not set anything else like the Timestamp column name "last_login". The only workaround that I have found is this:

UPDATE statistics SET bob=bob+1, last_login=last_login WHERE cid='$cid' LIMIT 1

This works. But I have alot of pages to go through that have updates in them and I have to added the last_login option to it. I would rather not do this. Not that im lazy but for the reason that I shouldnt have to. The timestamp should not update unless I tell it so. Any ideas?

Frederick

are you using the time() function to set the timestamp or do you have a field in mysql to do it for you?

fr8

when i do set it i have mysql do it with NOW()

Frederick

Set a field for holding the time and use time() when needed.

$postdate = time();

INSERT INTO tablename (postdate) VALUES ('$postdate')

Weedpacket

From the usernotes in the MySQL manual: http://dev.mysql.com/doc/refman/4.1/en/datetime.html

Andrew White wrote:
If you need to update data in a table that has a TIMESTAMP field which is auto updating, simply set the field equal to itself.

Consult whichever version of the MySQL manual is appropriate for your version of MySQL. But the basic answer is "timestamps are supposed to do that".

... The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. ...

Sxooter

Note that only the first timestamp field does this, so you can always create a table with two, and just ignore the first one.

Personally, I find this behaviour annoying because the datetime type is the one that doesn't auto-update, but timestamp is a SQL specification type, and mysql made it retarded by making it autoupdate its time. If they'd just done that with datetime instead of timestamp, migration would be much simple.

Sadly, time travel is not available to allow us to go back in time and whack someone up side the head when this decision was made, and now, due to a need for backwards compatibility, we're all stuck with it.