Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL timestamp column auto update even if there is no changes?

I have a column update_date in a table and type is timestamp. I set the deault value by using phpmyadmin drop down menu to CURRENT_TIMESTAMP. But later when ever I run sql UPDATE x SET ... it updates the timestamp column if only there is a changes in any of the columns. What I would like to achieve is that whether there is a change or not set the current time everytime the update sql runs. Is there any way of doing it in the mysql or I need to set the update_date explicitly every time the update is called?

Thank you

like image 703
akd Avatar asked Oct 10 '11 17:10

akd


People also ask

Does MySQL update automatically?

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values.

Which is better timestamp or datetime MySQL?

Timestamps are also lighter on the database and indexed faster. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.

What is difference between timestamp and datetime in MySQL?

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

How do I add a timestamp to a column in MySQL?

You can use the timestamp column as other posters mentioned. Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.


1 Answers

You need to explicitly update the column. From the MySQL manual, TIMESTAMP properties:

The auto-update TIMESTAMP column, if there is one, is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. If all other columns are set to their current values, the TIMESTAMP column does not change. Automatic updating does not apply if the TIMESTAMP column is explicitly assigned a value other than NULL.

Emphasis mine.

like image 153
Mark Byers Avatar answered Oct 01 '22 02:10

Mark Byers