CODEWITHSUNDEEP
mysqltimestamp
Or Weinberger
Or Weinberger

Reputation: 7482

MySQL Timestamp field

Is it possible to set a field to Timestamp but not have it change on update to current timestamp?

I'm trying to do that using phpMyAdmin and it doesn't let me remove the default on update CURRENT_TIMESTAMP

See this question and answer: Support user time zones

I am trying to use the TIMESTAMP as it will allow me to play around with the timezones easily.

Is it not possible to keep the data in that field intact when updating the same row?

Upvotes: 0

Views: 840

Answers (3)

Patrick Echterbruch
Patrick Echterbruch

Reputation: 679

The server allows any combination of DEFAULT and ON UPDATE, if phpMyAdmin doesn't let you set it, then it's maybe a bug in phpMyAdmin. Anyway, it's important to note that timestamp columns are treated specially in mysql, so if you have more than one of this type in your table, it's well possible that it's not gonna work the way you expect.

From the mysql docs:

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

  • With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
  • With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
  • With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
  • With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

Upvotes: 1

ayush
ayush

Reputation: 14588

This is the behaviour of TIMESTAMP. It can be confusing alright. Read this to work through it. Alternatively consider using a DATETIME.

Upvotes: 1

Aif
Aif

Reputation: 11220

Use the command interface. Looks like you must specify an "DEFAULT CURRENT_TIMESTAMP" attribute on table creation to get that behaviour. documentation here.

Upvotes: 0

Related Questions