CODEWITHSUNDEEP
mysqldatetime
Hacker
Hacker

Reputation: 7906

datetime data type in mysql

I had a field in my mysql table and its datatype was datetime and default value was CURRENT_TIMESTAMP. I thought it will put this value only on insert. This was getting changed both on Insert and update. Is it proper ?

I don't have ON UPDATE CURRENT_TIMESTAMP , then also its updating that column.

My question is like if i just have a column with datatype datetime and default as CURRENT_TIMESTAMP will it change the column when update on record happens even if ON UPDATE CURRENT_TIMESTAMP is not there?

Upvotes: 0

Views: 2318

Answers (2)

Quicksilver
Quicksilver

Reputation: 2710

From mysql doc

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

CREATE TABLE t1 (
  ts TIMESTAMP
);

With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

The default depends on whether the DEFAULT clause specifies CURRENT_TIMESTAMP or a constant value. With CURRENT_TIMESTAMP, the default is the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Upvotes: 0

Andrew Ty.
Andrew Ty.

Reputation: 677

It sounds as if you have:

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

However, it appears you're wanting:

DEFAULT CURRENT_TIMESTAMP

The difference between these two, is that the first will update the timestamp column on both inserts and updates, and the second will only supply a TIMESTAMP during insert and will NOT update upon update of the row.

You can check what your table is using by using the DESCRIBE function within MySQL to describe a specific table's schema.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Upvotes: 1

Related Questions