1
Date and Time Data Types
2
========================
4
Of the SQL date and time types, Drizzle supports:
8
+------------+---------------------------+-----------------------+------------------------+---------------+---------------+
9
|Data Type |Lowest Value (or NULL) |Maximum Value |Description |Storage Size |Resolution |
10
+============+===========================+=======================+========================+===============+===============+
11
|TIMESTAMP |'0001-01-01 00:00:00' |'9999-12-31 23:59:59' |both date and time |8 bytes |1 microsecond |
12
+------------+---------------------------+-----------------------+------------------------+---------------+---------------+
13
|DATE |'0001-01-01' |'9999-12-31' |dates only |4 bytes |1 day +
14
+------------+---------------------------+-----------------------+------------------------+---------------+---------------+
15
|TIME |'00:00:00' |'23:59:59' |time of day |8 bytes |1 microsecond +
16
+------------+---------------------------+-----------------------+------------------------+---------------+---------------+
17
|DATETIME |'0001-01-01 00:00:00' |'9999-12-31 23:59:59' |both date and time |8 bytes |1 microsecond |
18
+------------+---------------------------+-----------------------+------------------------+---------------+---------------+
23
To create a TIMESTAMP column that uses microseconds you simply need to specify TIMESTAMP(6) in your table definition, for example: ::
27
`b` TIMESTAMP(6) NULL DEFAULT NULL
30
You can then use the following (but note that ON DEFAULT/UPDATE CURRENT_TIMESTAMP works with microseconds as well): ::
32
insert into t1 values (1, '2010-01-10 07:32:43.234567');
34
The new table now looks like this:
36
+------+----------------------------+
38
+------+----------------------------+
39
|1 |2010-01-10 07:32:43.234567 |
40
+------+----------------------------+
45
In Drizzle, valid date inputs begin at 0001-01-01 rather than 0000-00-00, which is not a valid date. NULL is also a valid DATE entry.
50
Drizzle's TIME data type has a range of 00:00:00 - 24:59:59, while MySQL's TIME data type has a range of -838:59:59 - 838:59:59.
52
To prevent data loss to this type when converting from MySQL -> Drizzle, the conversion changes TIME to an INT of the number of seconds. For example, 00:00:00 becomes 0, 01:00:00 becomes 3600, and -01:00:00 becomes -3600.