on insert current_timestamp in mysql

Question

I need to alter a column with timestamp datatype. The thing is when record is inserted the current time stamp should be inserted in that column. I know there is ON UPDATE CURRENT_TIMESTAMP but for insertion i can't find a way.

Solution 1

What you're looking for is the DEFAULT keyword.

ALTER TABLE yourTable MODIFY COLUMN yourColumn timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Solution 2

What Rock 'Em wanted was to be told to use CURRENT_TIMESTAMP as the default for the column. Not on update. I use this all of the time. That way when a record gets inserted you know exactly when it happened. It is good practice in many tables to do this for audit tracking, etc.

Solution 3

Simply use DEFAULT clause, ignoring ON UPDATE clause, that's it should work for you!

ALTER TABLE yourTable MODIFY COLUMN yourColumn timestamp DEFAULT CURRENT_TIMESTAMP

Comments

[Rock 'em]

Ya I know that ON UPDATE can be used. WHat i need is something like this, alter table TAB_NAME add column COL_NAME timestamp default current_stamp ON INSERT (???????) current_timestamp

[fancyPants]

the default IS on insert. you specify no value when inserting and the column will have the default value. simple as that.

[Rock 'em]

ya that is ok for the 1st insert. If i insert some record later on that table, the "time" when inserting the record should also be inserted in a column which should happen as default. I hope am not confussing

[fancyPants]

Yes, you are confusing. You mean the same "time" should be in another column in the same table? Do you have trouble getting the "time" from the timestamp column? You know that there is the function TIME(yourTimeStampColumn) and many more?

[Rock 'em]

NAME TIME xxxx 201-01-01 10:02:12 // this time is the time when i insert the record yyyy // here the time should be inserted in the TIME column when i insert 'yyyy'

[fancyPants]

I really don't know what your problem is. INSERT INTO yourTable (Name) VALUES ('xxxx'); Do this insert and in the time column there will be the time when you do the insert. The yourColumn in my answer is of course your time column. Have a try...