Exact points in time: DATETIME
The DATETIME data type stores any moment in time in the
era that began 1 A.D. In fact, DATETIME is really a family of 28 data
types, each with a different precision. When you define a DATETIME
column, you specify its precision. The column can contain any sequence
from the list:
- year
- month
- day
- hour
- minute
- second
- fraction
Thus, you can define a DATETIME column that stores only a year, only a month and day, or a date and time that is exact to the hour or even to the millisecond. The following table shows that the size of a DATETIME value ranges from 2 to 11 bytes depending on its precision.
The advantage of DATETIME is that it can store
specific date and time values. A DATETIME column typically requires
more storage space than a DATE column, depending on the DATETIME qualifiers.
Datetime also has an inflexible display format. For information about
how to circumvent the display format, see Force the format of a DATETIME or INTERVAL value.
| Precision | Size (When f is odd, round the size to the next full byte) | Precision | Size (When f is odd, round the size to the next full byte) |
|---|---|---|---|
| year to year | 3 | day to hour | 3 |
| year to month | 4 | day to minute | 4 |
| year to day | 5 | day to second | 5 |
| year to hour | 6 | day to fraction(f) | 5 + f/2 |
| year to minute | 7 | hour to hour | 2 |
| year to second | 8 | hour to minute | 3 |
| year to fraction (f) | 8 + f/2 | hour to second | 4 |
| month to month | 2 | hour to fraction(f) | 4 + f/2 |
| month to day | 3 | minute to minute | 2 |
| month to hour | 4 | minute to second | 3 |
| month to minute | 5 | minute to fraction(f) | 3 + f/2 |
| month to second | 6 | second to second | 2 |
| month to fraction(f) | 6 + f/2 | second to fraction(f) | 2 + f/2 |
| day to day | 2 | fraction to fraction(f) | 1 + f/2 |