MySQL Internals Manual  /  Important Algorithms and Structures  /  Date and Time Data Type Representation

10.9 Date and Time Data Type Representation

The following table shows the storage requirements for date and type data types.

Type

Storage before MySQL 5.6.4

Storage as of MySQL 5.6.4

YEAR

1 byte, little endian

Unchanged

DATE

3 bytes, little endian

Unchanged

TIME

3 bytes, little endian

3 bytes + fractional-seconds storage, big endian

TIMESTAMP

4 bytes, little endian

4 bytes + fractional-seconds storage, big endian

DATETIME

8 bytes, little endian

5 bytes + fractional-seconds storage, big endian

Before MySQL 5.6.4, date and time data types have these encodings:

  • YEAR: A one-byte integer

  • DATE: A three-byte integer packed as YYYY×16×32 + MM×32 + DD

  • TIME: A three-byte integer packed as DD×24×3600 + HH×3600 + MM×60 + SS

  • TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)

  • DATETIME: Eight bytes: A four-byte integer for date packed as YYYY×10000 + MM×100 + DD and a four-byte integer for time packed as HH×10000 + MM×100 + SS

As of MySQL 5.6.4 the TIME, TIMESTAMP, and DATETIME types can have a fractional seconds part. Storage for these types is big endian (for memcmp() compatibility purposes), with the nonfractional part followed by the fractional part. (Storage and encoding for the YEAR and DATE types remains unchanged.)

  • TIME encoding for nonfractional part:

 1 bit sign    (1= non-negative, 0= negative)
 1 bit unused  (reserved for future extensions)
10 bits hour   (0-838)
 6 bits minute (0-59) 
 6 bits second (0-59) 
---------------------
24 bits = 3 bytes
  • TIMESTAMP encoding for nonfractional part: Same as before 5.6.4, except big endian rather than little endian

  • DATETIME encoding for nonfractional part:

 1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
---------------------------
40 bits = 5 bytes

The sign bit is always 1. A value of 0 (negative) is reserved.

  • Fractional-part encoding depends on the fractional seconds precision (FSP).

FSP

Storage

0

0 bytes

1,2

1 byte

3,4

2 bytes

4,5

3 bytes


User Comments
Sign Up Login You must be logged in to post a comment.