Created_at And Updated_at Columns For MySQL

To add these standard columns to your tables in MySQL, one for the creation date-time and another for the last updated date-time of each record, you can use the DATETIME data type, along with the DEFAULT and ON UPDATE clauses. Here’s how you can do it:

1. created_at Column:

• This column will store the date and time when the record was created.
• You can set the default value of this column to the current date and time using the DEFAULT CURRENT_TIMESTAMP clause.

1. updated_at Column:

• This column will store the date and time when the record was last updated.
• You can set the default value of this column to the current date and time using the DEFAULT CURRENT_TIMESTAMP clause.
• To automatically update this column to the current date and time every time the record is updated, you can use the ON UPDATE CURRENT_TIMESTAMP clause.

Here is an example of how to add these two columns to an existing table named your_table_name:

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

This SQL command alters the table your_table_name and adds two new columns: created_at and updated_at. The created_at column will be automatically set to the current date and time when a new record is inserted, and the updated_at column will be automatically set to the current date and time whenever the record is updated.

If you are creating a new table, you can define these columns as part of the table creation statement like this:

CREATE TABLE your_table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    column1 VARCHAR(50),
    column2 INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This SQL command creates a new table named your_table_name with an id column, two additional columns (column1 and column2), and the created_at and updated_at columns as described above.

Please note that the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses are available in MySQL 5.6 and later versions.

Getting Current Date-Time In MySQL