Data types and structure of query for fields in mysql

Yii 2.0 General Discussions
1

Hi, I have a table called districts in mysql database with the columns as DistrictId and District. I need to add 2 more columns in the same table called create_at and lastupdate_at. create_at column should contain the date and time for when the query is inserted in the table whereas lastupdate_at column will contain the date and time of the record last updated. create_at column will have the date and time only once(when the record is inserted) where as lastupdate_at will have date and time all the time when it was updated lastly.

So what would be the data type for these columns TIMESTAMP or DATETIME.

Suppose a new record is inserted as below

DistrictId      District    create_at                                 lastupdate_at
1                  ABC         2023-07-18  05:25:15 PM       NULL

Similarly when an existing record is updated

DistrictId      District    create_at                                  lastupdate_at
1                  ABC1        2023-07-18  05:25:15 PM        2023-07-20 10:00:05 AM

Is the below query better to add the columns to the table

ALTER TABLE district
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
2

I think type for both columns should be the same. https://github.com/yiisoft/yii2/blob/master/framework/behaviors/TimestampBehavior.php supports int storing UNIX timestamp so it makes sense to choose that.

3

Ok. The below line of query will also be correct method? Instead of using TimestampBehavior.php we can configure through mysql with the below line of query. Can you tell?

ALTER TABLE district
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at DATETIME DEFAULT NULL  ON UPDATE CURRENT_TIMESTAMP;
4

Yeah. That would work. I wonder why it’s TIMESTAMP in one case and DATETIME in another though.