Languages
[Edit]
EN

MySQL - DATE, TIME, DATETIME, and TIMESTAMP differences

0 points
Created by:
WGates
412

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

Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

MySQL - dates

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join