MySQL Timestamp question

[johnphpnewb]

I want to add a database password_updated_at column to my user_accounts table. I’ve been playing with sql via the console and i see that using timestamp not null creates an auto updated timestamp anytime something changes in the row. This is great for monitoring database row changes but i don’t want this action. I only want a timestamp if a user changes their password. I found a possible solution at stackoverflow but i want to know if it is the correct method or not:

ALTER TABLE `customers` 
ADD COLUMN `s_timestamp` TIMESTAMP NULL DEFAULT NULL;

https://stackoverflow.com/questions/20520443/mysql-timestamp-to-default-null-not-current-timestamp

so, if i set the column to NULL DEFAULT NULL, then no timestamp will be used unless i add one, correct? thus, update x password_updated_at = Now() where y = z;

is this method the best way to document such changes?

Thanlk you.

[thinsoldier]

I might be wrong but I think automatic timestamps at the mysql server level can only be used for 1 field and will be changed every time something in the row changes.

If you only want the password_updated_at field to change only when that specific event happens you could (probably should) do it in the php code.

[johnphpnewb]

Hello,@thinsoldier, Thank you for the reply. I figured that i need to UPDATE the column after the password form has been submitted (and passes validation). I was wondering if there is a different method. Thank you for confirmation. Really, i was wondering if there is a data type for timestamp to set a column to only hold timestamp data instead of an actual timestamp with auto initialization. I’m glad that you took time to weigh in on the matter.

I hope that you have a pleasant day

[astonecipher]

last_updated DATETIME null

Then when an update is made you update the column. Every table we have at work has 4 columns regardless of the table or system, AddedDate, UserAdded, DateEdited, UserEdited. Lets us keep track of changes [though we have audit tables as well].

[johnphpnewb]

Dear @astonecipher, Thank You! datetime null works like a charm.

Every table we have at work has 4 columns regardless of the table or system, AddedDate, UserAdded, DateEdited, UserEdited. Lets us keep track of changes [though we have audit tables as well].

This is priceless info. Thank you. I didn’t think about an audit table. I suppose I am too busy trying to put my site together. Thank you for mentioning this info. No wonder you are an App Architect