CODEWITHSUNDEEP
mysql
john.p.doe
john.p.doe

Reputation: 521

TimeStamp column always gets zero value

I have the following query to set a default value to a column: 

ALTER TABLE tableName ADD COLUMN testDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

But I see 0000-00-00 00:00:00 for testDate column in newly inserted rows.

I would like to see current date and time in it.

Can anybody explain why this might be happening?

Upvotes: 1

Views: 720

Answers (2)

userlond
userlond

Reputation: 3818

Data, you specified in your INSERT query, override default values of column, i.e. default values works if no data set for column.

See my fiddle.

Upvotes: 2

x13
x13

Reputation: 2227

Default values only work if the value is not defined in the query.

This will use the default value for test:

INSERT INTO mytable(userid, email) VALUES(42, '[email protected]');

While this will override the default value of test:

INSERT INTO mytable(userid,email,test/*here is when it goes wrong, you shouldn't mention the column in your insert statement at all*/) VALUES(42,'[email protected]','2001-09-11 00:00:00');

Upvotes: 0

Related Questions