Need a timestamp column that doesn't change on database updates in phpMyAdmin
I'm trying to create a table that keeps track of when items in our inventory go in and out of stock. The columns are ID, OutOfStockTS, BackInStockTS, and Status (which is 0 to indicate open and 1 to indicate closed). There's no primary key here since the idea is to keep track of all instances wherein a given item goes out of stock and back in stock. These dates are then used to calculate missed sales opportunities.
I'm not concerned about implementing the processes that check for this and update the table accordingly. What does concern me is that when I first created this table, I wanted three timestamp columns; the two mentioned above and a third to keep track of when the database updates. Neither of the first two columns should change after they're initially written. However, phpMyAdmin complained and claimed that I could only have one timestamp column that uses the current TS. I got rid of the "LastUpdate" column and was able to create the table, but now it's automatically forcing my OutOfStock column to use the current timestamp. How can I fix this? I just want the two stock columns to rely on my insertion for their values, not automatic timestamps.
Reference: http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
The behavior you observe is defined behavior for the first TIMESTAMP column defined in the table.
When both DEFAULT and ON UPDATE are omitted from the column definition (when the column is added to the table), e.g.
mycol TIMESTAMP NOT NULL
MySQL (helpfully?) sees that as if you had actually specified
mycol TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
(The output from SHOW CREATE TABLE mytable will show that the column is defined as if you had specified that.)
You can disable the ON UPDATE behavior (i.e. avoid that from being added to the column definition) by specifying a DEFAULT
ALTER TABLE mytable
mycol TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
When only the DEFAULT is specified, then MySQL doesn't automatically add the ON UPDATE.
The MySQL Reference Manual documents this behavior.