How to add 'created_at' and 'updated_at' columns? [closed]

Question

I need to add 'updated_at' and 'created_at' columns to some already existing table in MySQL database. I've added those colums using MySQL Workbench, but what query should I use to make them work properly? Thanks in advance ;)

Answer 1

According to the reference manual* you can use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp.

whereas:

With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

So, for example, you could use:

CREATE TABLE t1 (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

To add the columns to an already existing table you can use:

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

Note: Link provided refers to MySQL 8.0. The syntax is the same for previous versions as well. There is some difference though for versions prior to 5.6.5. Just quoting from the manual again:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.

Answer 2

Try to use a Trigger. As example, when creating a new entry, the trigger will be activated and will execute, like an event in c# or an Action listener in Java!

With them, you can update that new entry with a creation date, or an edited date when you manipulated an entry. See this Documentation on how to use MySql Triggers on w3resource.