TIMESTAMP with microseconds

Back in 2005 a user requested on the MySQL bug tracker that the TIME/DATE based data types store microseconds.  I personally don't think this is an unreasonable request and judging by the many posts to the bug report by users between then and now this is something quite a few people would like to see.

In Drizzle we asked 'What if...' and Brian came up with the answer.  We now (in trunk and in next week's release) have TIMESTAMP and NOW() with microsecond precision.

To create a TIMESTAMP column that uses microseconds you simply need to specify TIMESTAMP(6) in your table definition, for example:

CREATE TABLE `t1` (
 `a` INT DEFAULT NULL,
 `b` TIMESTAMP(6) NULL DEFAULT NULL
) ENGINE=InnoDB

You can then use the following (note that ON DEFAULT/UPDATE CURRENT_TIMESTAMP works with microseconds as well):

drizzle> insert into t1 values (1, '2010-01-10 07:32:43.234567');
Query OK, 1 row affected (0.07 sec)

drizzle> select * from t1;
+------+----------------------------+
| a    | b                          |
+------+----------------------------+
|    1 | 2010-01-10 07:32:43.234567 |
+------+----------------------------+
1 row in set (0 sec)