MySQL Timestamp field

Question

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?

ayush · Accepted Answer · 2011-02-03 20:43:15Z

1

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

answered Feb 3, 2011 at 20:43

ayush

14.7k11 gold badges55 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Echterbruch · Accepted Answer · 2011-02-03 20:48:43Z

1

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.

answered Feb 3, 2011 at 20:48

Patrick Echterbruch

6794 silver badges11 bronze badges

2 Comments

Or Weinberger Over a year ago

I have two, why is that a problem?

Patrick Echterbruch Over a year ago

It's not a problem as in "won't work", but TIMESTAMP columns will behave different depending on their position. If you want automatic initialization for your second column, you'll have to supress it for the first (DEFAULT 0). I think you should really read the docs on this.

Aif · Accepted Answer · 2011-02-03 20:41:13Z

0

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

answered Feb 3, 2011 at 20:41

Aif

11.3k1 gold badge32 silver badges44 bronze badges

Collectives™ on Stack Overflow

MySQL Timestamp field

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related