TimeStamp column always gets zero value

Question

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?

Can u try to check NOW() instead should be able to accomplish you challenge? — CreativeCreator, Nov 20 '15 at 00:56
[Duplicate?](http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column) also, this only sets the value of new rows if it is not specified in the *insert query*. — x13, Nov 20 '15 at 00:59
@ThisNameBetterBeAvailable That's the query that I posted and it is not working — john.p.doe, Nov 20 '15 at 01:00
@ThisNameBetterBeAvailable You are seeing the query in the question, so there are 10 records in the table already and since I am creating new column in the table I want the default value to be current time — john.p.doe, Nov 20 '15 at 01:14
Can we get to see the **insert** query? The query that is used to add rows (collection of collums) to the table(collection of rows)? — x13, Nov 20 '15 at 01:15

userlond · Accepted Answer · 2015-11-20T01:29:53.613

2

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.

edited Nov 20 '15 at 01:29

answered Nov 20 '15 at 00:57

userlond

3,632
2
36
53

You should exclude testDate in your INSERT statement, because default value works if no data is set for column – userlond Nov 20 '15 at 00:59
its the name of the new column – john.p.doe Nov 20 '15 at 01:00
Data, you specified in your INSERT query, override default values of column, i.e. default values works if no data set for column. – userlond Nov 20 '15 at 01:04
I am sorry but I didn't get you, could you please edit your answer with a working query – john.p.doe Nov 20 '15 at 01:19
@john.p.doe, you need only to exclude testDate from your INSERT query, or set testDate = NOW(). Both works. – userlond Nov 20 '15 at 01:22

score 0 · Answer 2 · answered Nov 20 '15 at 01:20

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, 'info@example.org');

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,'bush@example.did','2001-09-11 00:00:00');

TimeStamp column always gets zero value

2 Answers2