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.

DATE

The DATE type is used for values with only date part (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

TIME

The TIME type is used for values with only time part (no date 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

DATETIME

The DATETIME type is used for values that contain both date and time parts in YYYY-MM-DD hh:mm:ss.ffffff 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),
• ffffff- millisecond number.

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

Example DATETIME:

2021-10-21 11:55:44

TIMESTAMP

The TIMESTAMP type is used for values that contain both date and time parts in YYYY-MM-DD hh:mm:ss.ffffff 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),
• ffffff - millisecond number.

The TIMESTAMP has a range of 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.499999 UTC. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

Example TIMESTAMP:

2021-10-21 11:55:44

See also

1. MySQL - what is the difference between DATETIME and TIMESTAMP?

References

1. MySQL Documentation - The DATE, DATETIME, and TIMESTAMP Types