MySQL timestamp

When you insert a new row into a table, sometimes we need to record the time of insertion. For example, you have a table called User, which contains columns such as userName and userCreationDate.

To insert the creation date, I often used statement like this:
INSERT INTO USERS (userName,userCreationDate) VALUES ('yourusername',now())

Well, there's an easier way. You can just change the type of userCreationDate column from Date to Timestamp. MySQL will automatically update the column everytime you insert a new row. By the way, I used MySQL Administrator to modify the column type.

Now I've got a new problem. When the row is updated (ex: changing the userName), the userCreationDate is also updated to the current time. I don't want the creation date to be changed, but I can't find the reason by looking the table information in MySQL Administrator. So, let's use open the MySQL using the command prompt.

Type this command 'SHOW CREATE TABLE USERS'. You'll see something like this:
`userCreationDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

The problem is in the 'on update CURRENT_TIMESTAMP' statement. It means the column is automatically updated when the row is modified. To turn off this functionality, just alter the table:
ALTER TABLE USERS MODIFY COLUMN userCreationDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

That's all.