MySQL
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’.
