MySQL timestamp field default CURRENT_TIMESTAMP not working for existing rows

Question

In the server, I have MySQL version:

5.1.61

It has a table called test with 10 columns and 10K rows. Now I have decided to add a new column

ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

New column ts is added. but the problem is for existing rows this new column(ts)'s value is 00:000:000 Not current time.

PS: When I add new rows or update any existing row then the ts is updated with current time stamp.

why is default current_timestamp/now() not working for existing rows?

Edited: I can run a simple update SQL to update existing rows to the current time. But I am new in the database and I am trying to know if it is possible to update existing rows with a default value.

Tim Biegeleisen · Accepted Answer · 2018-04-03 09:59:05Z

2

The DEFAULT clause in a MySQL table only concerns what happens when new records get added to the table. There is no legacy behavior where the DBMS goes back to already existing records and applies some default value. In this case, if you want the already existing records to bear the current timestamp, you may do a blanket update:

UPDATE yourTable
SET ts = CURRENT_TIMESTAMP;

After this point, when you add new records and do not specify a value for the ts column, it will be assigned CURRENT_TIMESTAMP.

answered Apr 3, 2018 at 9:59

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ajay Over a year ago

he is asking why the CURRENT_TIMESTAMP is not working to the existing rows while adding a new column

Tim Biegeleisen Over a year ago

@Ajay Um...my answer explains what is happening and why it is happening. Did you bother to read it?

Ajay Over a year ago

ooh my mistake I apologize..:)

Al-Alamin Over a year ago

@TimBiegeleisen thanks for your answer. I was really trying to know if it is possible or not. I thought I made mistakes.

Collectives™ on Stack Overflow

MySQL timestamp field default CURRENT_TIMESTAMP not working for existing rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related