add column with autodate when inserting new rows-CodePudding

I have table called samples that looks like this

Column        Type       Comment
id            int(11)   Auto Increment  
nvname        text  
sample        text  
client        text  
project       text  
model         text     NULL 
pathology     text     NULL 
treatment     text     NULL 
location      text     NULL 
notes         text     NULL

I would need to add a column date at the end that automatically adds date of insertion can that be done? if not should I use trigger to copy to another table with that information while inserting?

CodePudding user response：

You can add a column with a DEFAULT value as NOW():

ALTER TABLE samples
ADD COLUMN insertion_date DATETIME 
DEFAULT NOW();

CodePudding user response：

This is a job for DEFAULT CURRENT_TIMESTAMP. Add this column definition, or something like it, to your table definition.

    insert_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

You can also add this column definition if you want to know when the row was most recently updated.

    update_timestamp TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP

Then, if you don't mention the column's name when doing an INSERT or UPDATE, it gets handled automatically, robustly, and far more efficiently than a trigger.

Pretty sweet feature, huh?

CodePudding user response：

Also you can use DEFAULT (CURRENT_DATE) in next way:

CREATE TABLE test(
    id int primary key auto_increment,
    name varchar(64),
    created_at date NOT NULL DEFAULT (CURRENT_DATE)
);
INSERT INTO test (name) VALUES ('Test name');

SQL online fiddle

Result:

 ==== =========== ============ 
| id | name      | created_at |
 ==== =========== ============ 
| 1  | Test name | 2022-01-03 |
 ---- ----------- ------------