Column data types and structure of query for create_at and lastupdate_at fields

Account

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;

RayPooley

Account Why are you writing SQL?
Do you not have WorkBench intalled?

Weedpacket

Well, which would be more useful to you? TIMESTAMP is stored internally as UTC and is converted to and from local time; DATETIME is stored in the local timezone, so what comes out might not be what went in if the local timezone changes. On the other hand, TIMESTAMP has a narrower range than DATETIME, though this would only be a concern if your database is going to still be operating more than fifteen years from now.
https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Account

Ok, what about the alter table query. Is it ok to use.

Account

If I use the below Alter command then what happens is that when a new record is created, both the fields create_at and lastupdate_at has the same date and time value. This should not happen. At the time of new record creation only create_at column should have the date and time, lastupdate_at should be NULL. Also the time format should be 12 hours format showing PM or AM

Below is the sample result of the query when update has taken place. The time should be 12 hrs format with AM or PM


DistrictId     District    create_at                            lastupdate_at
1                 ABC        2023-07-24 19:56:38          2023-07-24 20:00:00

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

NogDog

Account The time should be 12 hrs format with AM or PM

You'll need to convert it to the desired output format via either your PHP date/time functions or via the database's applicable date/time-to-string functions. (I.e., it's likely stored as an integer in the database, and you're just seeing its default to-string format from your query when you don't tell it to do anything differently.)

MySQL has the date_format function, which might be what you want. (I only use PostgreSQL these days, so don't know if that's the best way in MySQL?)

Account

Ok.