Date and Time

MySQL Data Type: Date and Time
Categories:
Tags:

Date and Time Types

Type Size Range Description
DATE 3 bytes 1000-01-01 to 9999-12-31 YYYY-MM-DD
TIME 3 bytes + fractional seconds storage -838:59:59 to 838:59:59 HH:MI:SS
DATETIME 5 bytes + fractional seconds storage 1000-01-01 00:00:00 to 9999-12-31 23:59:59 YYYY-MM-DD HH:MI:SS
TIMESTAMP 4 bytes + fractional second storage 1970-01-01 00:00:01 UTC to 2038-01-09 03:14:07 UTC YYYY-MM-DD HH:MI:SS
YEAR 1 byte 1901 to 2155 or 0 to 99 Holds the value of year either in a two digit or in a four-digit format

Difference between datetime and timestamp

  • timestamp will affect by the timezone, difference timezone will show different time
  • datetime will keep the solid value, DOESN’T affect by the timezone
  • Index Speed
    • timestamp > datetime

Reference