ansaurus

Question

mysql timestamp updates when record is amended

Answer 1

A:

if you don't want this field to be updated automatically, just do not use timestamp type field at all
Use datetime instead.

Col. Shrapnel 2010-09-09 06:53:21

Bit of a dumb question here but... My database has thousands of records, if I change the database table so that the timestamp field is a datetime will it screw up my site, for example my sql inserts won't work, sales times won't be formatted correctly?

Mike Sav 2010-09-09 07:02:12

@Mike yes it's bit of a dumb question. You have to change your code as well. My answer was not about your legacy code and how to deal with silly design flaws. But just about database design, how to use your tool properly. Consequences for the legacy code is another matter. Although there is nothing bad in code rewriting. It is called refactoring and it's part of your job.

Col. Shrapnel 2010-09-09 07:15:35

however you're lucky because in mysql5 timestamp has the same format as datetime, afaik

Col. Shrapnel 2010-09-09 07:16:58

Design flaws! You've hit the nail on the head! I've learned alot from my design flaws! Thanks for the help.

Mike Sav 2010-09-09 07:22:45

Answer 2

A:

Check the table definitions, if the CURRENT-TIMESTAMP option is really disabled.

If its disabled, it should look like...

CREATE TABLE `sometable` (
  ...somefields...
  `Sale_Time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' INT(11) DEFAULT NULL,
  ...somefields...
) ENGINE=someengine

...otherwise the CURRENT-TIMESTAMP would be visible in this line. When it is disabled, it doesn't update on an update, unless some other database function (Trigger or such) changes it while updating fields.

Björn 2010-09-09 06:57:25

ansaurus

tags:

views:

answers:

mysql timestamp updates when record is amended

related questions