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?

A late answer for others who may Google this: if you use a TIMESTAMP when you do manuals edits in your database; or if you have multiple TIMESTAMP columns and do not want to touch all of them; the MySQL manual says to assign the column to itself. You explicitly set the column to its current value (ie. "updated_on = updated_on"), so that it is not changed by the UPDATE. — , Nov 12 '10 at 16:22

score 79 · Accepted Answer · edited Jan 16 '16 at 20:29

79

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

edited Jan 16 '16 at 20:29

Philippe

9,582
4
39
59

answered May 03 '09 at 16:12

Paolo Bergantino

480,997
81
517
436

Thanks! In here "NOT NULL" part is not required I think. – Yasiru G Oct 25 '17 at 04:02

score 1 · Answer 2 · edited Apr 23 '13 at 13:53

1

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Very good documentation here for time-stamp.

edited Apr 23 '13 at 13:53

Code Lღver

15,573
16
56
75

answered May 03 '09 at 16:13

user99974

203
2
3
8

I may be missing something but my table is (and was) defined this way but still the dates changed. From mysql explain: 'pur_time', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', '' – Nir May 03 '09 at 16:34

score 0 · Answer 3 · answered May 03 '09 at 16:11

0

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

answered May 03 '09 at 16:11

cherouvim

31,725
15
104
153

MySQL timestamp only on create

3 Answers3

Linked