mysql timestamp only on create

Question

I use timestamp on mysql 5.x (with PHP) to remember event times. During development I had to update the table with a query that changes something in all columns. The timestamp was then reset to current time.

How can I make timestamp change only on inserts and not on updates or replace ?

Answer 1

You can use a default value for that field and not include it in the insert or update query.

Answer 2

Here's all you need to know. In short, though, I think this should do it:

ALTER TABLE `mytable`
CHANGE `mydatefield` `mydatefield`
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Answer 3

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Very good documentation here http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

Answer 4

I suggest using DATETIME for that column. It won't change when the record is updated.

For me, I use TIMESTAMP for tracking when the record was last touched, although in general I avoid using it.