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 TypeStorage Required
YEAR1 byte
DATE3 byte
TIME3 byte
DATETIME8 byte
TIMESTAMP4 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)
122012
122112
121912
921992

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