Date data types in MySQL
MySQL provides several temporal data types. Choose the type by the information you need to store: a calendar date, a date with time, an elapsed time, an automatically converted timestamp, or a year.
| Type | Typical format | Use |
|---|---|---|
DATE | YYYY-MM-DD | Calendar dates without a time component. |
DATETIME(fsp) | YYYY-MM-DD HH:MM:SS[.fraction] | Date and time values independent of session time-zone conversion. |
TIMESTAMP(fsp) | YYYY-MM-DD HH:MM:SS[.fraction] | Stored internally in UTC and converted using the session time zone; useful for event timestamps. |
TIME(fsp) | [-]HHH:MM:SS[.fraction] | Time of day or elapsed intervals; range is approximately -838:59:59 to 838:59:59. |
YEAR | YYYY | Year values; prefer YEAR, not deprecated display-width forms such as YEAR(4). |
Example table
CREATE TABLE event_schedule (
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
event_date DATE NOT NULL,
starts_at DATETIME,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
duration TIME,
event_year YEAR,
PRIMARY KEY (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;DATE and DATETIME support a much wider calendar range than TIMESTAMP. In MySQL 8.4, TIMESTAMP is limited to the Unix-timestamp range ending in January 2038, so use DATETIME when you need dates outside that range.
Create Table DataType Numeric Copy Table & SHOW CREATE Table
Data Types
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
SQL Video Tutorials