Languages
[Edit]
EN

MySQL - DATE, TIME, DATETIME, and TIMESTAMP differences

0 points
Created by:
Dirask Community
6810

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 - 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