A field that updates with server time on any row update - mysql

[Drewser33]

Is there any way to have a field that will update any time its row is updated?

 

My situation is that I will have multiple devices writing to a database, and in one case the time that is being kept track of is important.  And the devices writing to the database are a bit difficult to keep accurate times on, so instead of writing the time from the devices themselves, I was hoping I could just have a field update itself. I know it can be done on INSERT, but I do not want to do an INSERT every time.

 

Thanks!

[mac_gyver]

you can define the column to be a TIMESTAMP data type and use ON UPDATE CURRENT_TIMESTAMP in the definition to get it to only automatically update it's value when the row in updated.

 

ref: http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

[Zane]

You could set a DATETIME field called last_updated and add it to your UPDATE statement

 

UPDATE yourTable SET someField = 'foobar', last_updated = NOW() WHERE id = xxx

Edited July 3, 2014 by Zane