MySQL | How to automatically update timestamp field

Author - Webner

Sometimes, we need to check when was the data last accessed. In MySQL, we had taken a field last_modified_time as timestamp datatype in a table, which was set to CURRENT_TIMESTAMP by DEFAULT.

When a new record was inserted, it automatically updated the field and set the current datetime into this field. But if we updated any other field, its value remain older.

Solution : In order to update that field, we need to explicitly specify that we want to update it on updation in the record.

last_modified_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP is the key.

Now when we update any field of a record, it will update last_modified_time column to current timestamp in same record.

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Web development or any other software development assistance please contact us at webdevelopment@webners.com

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *