MYSQL Date And Time Types

This tutorial will help you to understand about “MYSQL Date and Time Data Types”.

Storage Requirements for Date and Time Types

TIME, DATETIME, TIMESTAMP storage details showed in following table.

Data Type Storage Required
YEAR 1 byte
DATE 3 byte
TIME 3 byte
DATETIME 8 byte
TIMESTAMP 4 byte

YEAR Type

The default year type is 1 byte type is used to represent year values. It can be declared as YEAR(4) or YEAR(2). But when we choose the YEAR(2), We will get the following issues
YEAR(2) Issule:
2012,2112,1912,1992 years insert is like this

YEAR(2) YEAR(4)
12 2012
12 2112
12 1912
92 1992

TIME Type

MySQL retrieves and displays TIME values in ‘HH:MM:SS’ format and ‘HHH:MM:SS’ format for large hours values. TIME values may range from ‘-838:59:59’ to ‘838:59:59′
Example:
1. ’11:12′ means ’11:12:00′, not ’00:11:12’
2. ‘1112’ means ’00:11:12′, not ’11:12:00′
3. ’00:00:00′ is the default time

DATE, DATETIME AND TIMESTAMP Type

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.