How to add created_at column to my table. So, that values of this column must be equal to now() on insert command and must be null for all old rows?

Question

I use MySQL and I have people table:

mysql> desc people;
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type         | Null | Key | Default           | Extra                                         |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id           | int          | NO   | PRI | NULL              |                                               |
| name         | varchar(255) | YES  |     | NULL              |                                               |
| updated_time | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.00 sec)

I want to add new created_at column. So, that on insert command, value of this column must be assigned to current timestamp. But, on update command, value of this column should not be updated to current timestamp. Also, for all old raws of this table, value of this column must be equal to NULL. How I can do that?

I need in this feature, because my table should correctly react to command: insert into people (id, name) values (7, 'alice');

I have seen some similar questions. For example, this: MySQL create time and update time timestamp

But, all of these answers do not match for my case. Because,

1. If we do add created_at timestamp default current_timestamp. Then all old values that were existed before the addition of this column, will be assigned to current timestamp.
2. If we do add created_at timestamp default null on update current_timestamp;. Then on update command, value of this column will be assigned to current timestamp, which is not correct.

Answer 1

As i said add the column, update the table with NULL or whatever you want to add to your created and then INSERT as before

To avoid the changes of the updated_time column, you change the column temporarily to not update the timestamp on update

Hera a complete example , with all commands

CREATE TABLE people (
  `id`INT,
  `name` varchar(25),
  `updated_time` TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 
);

INSERT INTO people
  (`id`, `name`)
VALUES
  (1,'test'),(2,'test3'),(3,'test2');

SELECT * FROM people;

id | name  | updated_time       
-: | :---- | :------------------
 1 | test  | 2020-06-15 02:03:24
 2 | test3 | 2020-06-15 02:03:24
 3 | test2 | 2020-06-15 02:03:24

ALTER TABLE people
     MODIFY 
            updated_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

ALTER TABLE people add created_at timestamp NULL default current_timestamp

SELECT * FROM people;

id | name  | updated_time        | created_at         
-: | :---- | :------------------ | :------------------
 1 | test  | 2020-06-15 02:03:24 | 2020-06-15 02:03:24
 2 | test3 | 2020-06-15 02:03:24 | 2020-06-15 02:03:24
 3 | test2 | 2020-06-15 02:03:24 | 2020-06-15 02:03:24

UPDATE people SET created_at = NULL;

SELECT * FROM people;

id | name  | updated_time        | created_at
-: | :---- | :------------------ | :---------
 1 | test  | 2020-06-15 02:03:24 | null      
 2 | test3 | 2020-06-15 02:03:24 | null      
 3 | test2 | 2020-06-15 02:03:24 | null      

ALTER TABLE people
     MODIFY 
            updated_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

INSERT INTO people
  (`id`, `name`)
VALUES
  (4,'test')

SELECT * FROM people;

id | name  | updated_time        | created_at         
-: | :---- | :------------------ | :------------------
 1 | test  | 2020-06-15 02:03:24 | null               
 2 | test3 | 2020-06-15 02:03:24 | null               
 3 | test2 | 2020-06-15 02:03:24 | null               
 4 | test  | 2020-06-15 02:03:24 | 2020-06-15 02:03:24

db<>fiddle here

Answer 2

This solution will seem to be disappointing at first glance, because I'm telling you that

add created_at timestamp default current_timestamp

is indeed the correct solution. It

• allows null values
• defaults to the current time

So, for future values this alter will guarantee to have the moment of creation. So far you agree with me, except my statement about adding the column as stated above being the solution. But now, allow me to convince you about this being the solution. If, just after running the alter command that adds the column, you set the older values to have a created_at of null, then past values are fixed as well:

update people
set created_at = null;