MySQL - DATE, TIME, DATETIME, and TIMESTAMP differences

In this article, we would like to show you the difference between DATE, TIME, DATETIME and TIMESTAMP types in MySQL.

1. DATE

The DATE type is used for values with a date part but no time part in YYYY-MM-DD format, where:

• YYYY - four-digit year format, e.g. 2021,
• MM - month number (counted from 01 to 12),
• DD - day number (from 01 to 31 - depending on the month)

The DATE type supported range is from 1000-01-01 to 9999-12-31.

Example DATE:

2021-01-01

2. TIME

The TIME type is used for values with time part in hh:mm:ss format, where:

• hh - hour number (from 00 to 24),
• mm - minute number (from 00 to 59),
• ss - second number (from 00 to 59).

Example TIME:

11:15:59

3. DATETIME

The DATETIME type is used for values that contain both date and time parts in YYYY-MM-DD hh:mm:ss format, where:

• YYYY - four-digit year format, e.g. 2021,
• MM - month number (counted from 01 to 12),
• DD - day number (from 01 to 31 - depending on the month),
• hh - hour number (from 00 to 24),
• mm - minute number (from 00 to 59),
• ss - second number (from 00 to 59).

The supported range is from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

Example DATETIME:

2021-10-21 11:55:44

4. TIMESTAMP

The TIMESTAMP type is used for values that contain both date and time parts in YYYY-MM-DD hh:mm:ss format, where:

• YYYY - four-digit year format, e.g. 2021,
• MM - month number (counted from 01 to 12),
• DD - day number (from 01 to 31 - depending on the month),
• hh - hour number (from 00 to 24),
• mm - minute number (from 00 to 59),
• ss - second number (from 00 to 59).

The TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

Example TIMESTAMP:

2021-10-21 11:55:44

See also

• MySQL - what is the difference between DATETIME and TIMESTAMP?
• MySQL Documentation - The DATE, DATETIME, and TIMESTAMP Types