How do I prevent timestamp columns from updating when a MySQL row is updated?

Question

I have a users table that has a column for the date that they joined. It is a timestamp datatype. I want the value of the column to always be the current time when the user joined, so the default value is the current time. If the user's profile is updated, the user's join date is updated to the current time. In other words, say a user joins on August 2nd and then updates their profile on September 17th. The new value of the join date will be September 17th. This happens even though I don't tell my sql statements to update that row.

How can I make it so that the timestamp column is only affected when a new row is created?

check `show create table table_name` and it will tell you the definition of the column and it will be as `ON UPDATE CURRENT_TIMESTAMP` , remove that part and you are done. — Abhik Chakraborty, Aug 02 '16 at 06:50
possible duplicate of http://stackoverflow.com/q/18962757/4323504 read the first answer — Luthando Ntsekwa, Aug 02 '16 at 06:52

score 0 · Answer 1 · answered Aug 02 '16 at 06:53

Sounds like you need to configure the default constraint so that it populates the column on insertion only:

DEFAULT CURRENT_TIMESTAMP
ALTER TABLE table CHANGE datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Changing it to only be this means that any revisions will not trigger the timestamp value to be updated.

How do I prevent timestamp columns from updating when a MySQL row is updated?

1 Answers1