Refresh timestamp column in application or database?

Question

I have date_created and date_modified columns for each table in the database.

What are the advantages / disadvantages of setting timestamp columns in a database using MySQL triggers rather than setting them at the application level using PHP?

What is easier to maintain and have better time synchronization when deploying to many servers?

EDIT: since Johan suggested setting timestamps in the database, are these the correct implementations? Also, is it good practice to have date_created and date_modified on each table? or do you recommend adding them to specific tables?

date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP date_modified TIMESTAMP NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 

Answer 1

Base timestamp .

If you replicate in the master-slave setting, the database timestamps will replicate correctly (using the original timestamp).

You do not set timestamps in triggers (!).

Instead, you specify them in the table definition, MySQL will automatically set the correct timestamp for insertion and / or update.
You only need to set them once when you create (or modify) the table and it.

It couldn't be simpler.

Answer 2

It is up to you which tables should have timestamp columns. In our production environment, each table has these columns.

However, we also set the values ​​in the code. I believe that this was done so that the dates fully control the application. Know that one way or another you do it, replication will be beautiful. If you set the dates in the application and want the database server time, and not the application server time, just set the columns using the MySQL NOW () function.

Answer 3

Good practice: have date_created and date_modified for each table?

Sure.
I would also add date_deleted.

Refresh timestamp column in application or database?

Depending on how you plan to use these fields.
If this is some kind of article, and you want to show the last time you were editing, you should never use the mysql timestamp functions, as this will lead to unexpected results, for example, by the same time update in all records in the database.

The same goes for the trigger.
do not be lazy, set this field manually. It will cost you nothing since you are already using ORM for this, right?