Languages
[Edit]
EN

MySQL - DATE, TIME, DATETIME, and TIMESTAMP differences

0 points
Created by:
Dirask Community
6240

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