Source:http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html
The MySQL timestamp is an oddity, being both a mySQL "Data Type" as well as a type of specialty column that provides a built in default. It doesn't help matters, that the timestamp was changed significantly around mysql version 4.1.
The Old TIMESTAMP
In older mysql versions, the TIMESTAMP was not in the same format as a DateTime column, and you could also set up truncation by defining the TIMESTAMP to have a fixed size. For example, you could define a TIMESTAMP column to be a TIMESTAMP(4) which would then only store the 4 digit Year portion of a DateTime value. I won't go into much detail on the pre version 4.1 TIMESTAMP, however, if you're stuck with an older version of MySQL I recommend you read the manual carefully before you attempt to use any of the information here. I'm going to concentrate on the current TIMESTAMP.
TIMESTAMP Properties
At its most fundamental, the TIMESTAMP is really nothing more than a Unix TimeStamp, which is to say, that internally it is stored as an integer value of seconds. Where a MySQL DATETIME column can be used to store any date and time from Jan 1, 1000 to 12/31/9999, the TIMESTAMP is limited in the same ways that the Unix timestamp is currently limited -- it can only store values from Jan 1, 1970 to Jan 9, 2038.
Those familiar with Unix design, will recognize the Jan 9, 2038 date as being the next big "Y2K" computing panic, and if you're young enough, you may realize a large payday in your future, selling remediation services to companies in roughly another 28 years. The folks at
http://www.y2038.com/ are already estimating this to be as much as a 10 trillion dollar jackpot, although no doubt by that time most of the coding will be done by the Morlocks from their
underground cave cities. Outsourcing of IT to Morlocks will be a major industry trend by the year 2020, mark my words.
Saving bytes
MySQL stores a timestamp as a 32 bit integer, which of course requires 4 bytes of storage. This is one reason why you might want to use a TIMESTAMP over a DATETIME, which requires 8 bytes. Primarily, people look to the TIMESTAMP because, as its name implies, it can be utilized to
stamp the time on a row at the point it's inserted. Let's take a look at a mysql TIMESTAMP in action.
First let's create a table with a single TIMESTAMP column in it. We will not refer to this column, and observe what happens when we insert a new row into the table. In case you're keeping score:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.0.45 |
+-----------+
1 row in set (0.00 sec)
CREATE TABLE atimestamp (id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, created TIMESTAMP);
mysql> describe atimestamp;
+---------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| justnow | datetime | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)
So here we have the table, along with a datetime column that we will set to the magic value of NOW().
mysql> insert into atimestamp (justnow) values (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | created |
+----+---------------------+---------------------+
| 1 | 2009-04-23 14:29:58 | 2009-04-23 14:29:58 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Just as one would hope, MySQL automatically sets the value of TIMESTAMP column I named "created" to be the same as the Server time. So if our primary goal is to have a column that keeps track of when the row was created, our TIMESTAMP does a great job.
Or does it?Stopping an UPDATE from overwriting the TIMESTAMP
What happens if, at later time, we UPDATE a column in the table?
mysql> update atimestamp set justnow = NOW();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | created |
+----+---------------------+---------------------+
| 1 | 2009-04-23 14:30:32 | 2009-04-23 14:30:32 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
This is not good. Our created time has been lost, because the default behavior of a TIMESTAMP is to update the value to NOW(),
any time the row is changed.The TIMESTAMP Defaults
Setting a column to be a MySQL TIMESTAMP is equivalent to also giving the column a default of
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. For those not familiar with Mysql Defaults, when creating a table, you can specify a default value for a column to receive if it is not specifically assigned a value during an INSERT. You do this using the DEFAULT keyword, and for all columns other than a TIMESTAMP this must be a constant value. Timestamp columns can specify the CURRENT_TIMESTAMP default which tells mySQL to default this column to NOW().
Stopping UPDATE on TIMESTAMP
The only way not to get the update behavior is to specifically declare the TIMESTAMP to
DEFAULT CURRENT_TIMESTAMP. This seems to me to defeat the purpose of having the default behavior in the first place but might be understandable if it was possible for you to have a second TIMESTAMP column, perhaps named "updated".
Unfortunately, you can't have one TIMESTAMP with DEFAULT CURRENT_TIMESTAMP, and a second one with ON UPDATE CURRENT_TIMESTAMP. More often than not, what people really want is only the DEFAULT CURRENT_TIMESTAMP behavior, so it's very important to remember this workaround, if you are using a TIMESTAMP as for example, the "signup date" in a User table. See this in action below:
CREATE TABLE atimestamp (id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> describe atimestamp;
+---------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| justnow | datetime | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)
mysql> insert into atimestamp (justnow) values (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | created |
+----+---------------------+---------------------+
| 1 | 2009-04-23 18:15:38 | 2009-04-23 18:15:38 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> update atimestamp set justnow = NOW();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | created |
+----+---------------------+---------------------+
| 1 | 2009-04-23 18:15:56 | 2009-04-23 18:15:38 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Now we have automatic timestamping on INSERT, but without the timestamp being overwritten on UPDATE.
What about an UPDATE only TIMESTAMP?
Perhaps there's a really good reason to dedicate a TIMESTAMP to only apply the time when the row is updated. I can't think of one, but for the sake of completeness, here's how you can define the Default.
CREATE TABLE atimestamp (id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, updated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP);
mysql> describe atimestamp;
+---------+-----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| justnow | datetime | YES | | NULL | |
| updated | timestamp | NO | | 0000-00-00 00:00:00 | |
+---------+-----------+------+-----+---------------------+----------------+
3 rows in set (0.00 sec)
mysql> insert into atimestamp (justnow) values (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | updated |
+----+---------------------+---------------------+
| 1 | 2009-04-24 23:33:56 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> update atimestamp set justnow = NOW();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from atimestamp;
+----+---------------------+---------------------+
| id | justnow | updated |
+----+---------------------+---------------------+
| 1 | 2009-04-24 23:34:19 | 2009-04-24 23:34:19 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Many Timestamps, but only one that's magical
MySQL will allow you to declare multiple columns of type TIMESTAMP, but
only the first timestamp in the table will have the built in Default.CREATE TABLE bigtimestamp (id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, created TIMESTAMP, updated TIMESTAMP, verified TIMESTAMP);
mysql> describe bigtimestamp;
+----------+-----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| justnow | datetime | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated | timestamp | NO | | 0000-00-00 00:00:00 | |
| verified | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------+-----------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)
mysql> insert into bigtimestamp (justnow) values (NOW());
Query OK, 1 row affected (0.01 sec)
mysql> select * from bigtimestamp;
+----+---------------------+---------------------+---------------------+---------------------+
| id | justnow | created | updated | verified |
+----+---------------------+---------------------+---------------------+---------------------+
| 1 | 2009-04-24 23:39:19 | 2009-04-24 23:39:19 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
Even with this limitation, it may still be a good idea to declare your Date columns as TIMESTAMP type, as the savings of 4 bytes per date per row can easily be significant if your table will have a lot of rows in it.
Single table Insert and Update Timestamp Workaround
It is actually possible to trick mysql into providing the default timestamp behavior for "created' and "updated" columns, despite the documented limitation of only having one default timestamp per table. If you define the first timestamp column to have a default of 0, while also being NOT NULL, you can trick mysql into supplying today's date by
explicitly setting the column to be NULL on insert. In this case, mySQL decides to be
helpful and for no logical reason, to set the value to NOW(). The second TIMESTAMP which has the explict TIMESTAMP defaults, works normally, and is set to NOW() on insert and update.
CREATE TABLE atimestamp (id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, created TIMESTAMP NOT NULL DEFAULT 0, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> describe atimestamp;
+---------+-----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| justnow | datetime | YES | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| updated | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+---------------------+----------------+
4 rows in set (0.00 sec)
mysql> insert into atimestamp (justnow) values (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from atimestamp;
+----+---------------------+---------------------+---------------------+
| id | justnow | created | updated |
+----+---------------------+---------------------+---------------------+
| 1 | 2009-04-24 23:54:23 | 0000-00-00 00:00:00 | 2009-04-24 23:54:23 |
+----+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> insert into atimestamp (created, justnow) values (NULL, NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from atimestamp;
+----+---------------------+---------------------+---------------------+
| id | justnow | created | updated |
+----+---------------------+---------------------+---------------------+
| 1 | 2009-04-24 23:54:23 | 0000-00-00 00:00:00 | 2009-04-24 23:54:23 |
| 2 | 2009-04-24 23:55:35 | 2009-04-24 23:55:35 | 2009-04-24 23:55:35 |
+----+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update atimestamp set justnow=NOW() WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from atimestamp;
+----+---------------------+---------------------+---------------------+
| id | justnow | created | updated |
+----+---------------------+---------------------+---------------------+
| 1 | 2009-04-24 23:54:23 | 0000-00-00 00:00:00 | 2009-04-24 23:54:23 |
| 2 | 2009-04-24 23:56:48 | 2009-04-24 23:55:35 | 2009-04-24 23:56:48 |
+----+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
While this technique works at present, I would consider it an undocumented hack, based on a side effect. It also requires you to explicitly specify NULL in the values for the created column. If you forget to reference the column in the INSERT statement, it will not set the value to NOW() as illustrated above. This technique works for now, but who knows what will happen in future versions of mySQL? Use at your own risk.
Posts
