Bala.C Bala.C - 2 months ago 8
MySQL Question

Timestamp without change on update

In mysql I have a

timestamp
with the field name
added_on
. I created that for a table of leads for a crm. But it's getting updated whenever I update a lead. I only want the timestamp to show only the added time. I dont want that to get updated everytime when I update or make changed in the lead.

Note: while creating the timestamp field i gave default value as timestamp and it shows
ON UPDATE CURRENT_TIMESTAMP
in the extras field after creating the field.

any ideas to stop the timestamp getting updated whenever I update the row?

Answer

The issue is the ON UPDATE CURRENT_TIMESTAMP. You should be able to undo this behavior with an alter statement like this (not sure what your table name is):

ALTER TABLE leads MODIFY added_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP