Date & Time Data Types
In this page:
DATE Type
DATE stores only a calendar day in YYYY-MM-DD format with no time component at all, which is the right fit for values like a birthday or a hire date where the clock time is irrelevant.
Example: DATE Type
CREATE TABLE people (birthday DATE);
INSERT INTO people VALUES ('1990-05-14');TIME Type
TIME stores a clock value in HH:MM:SS format and can also represent a duration or interval rather than a specific moment, such as '02:30:00' meaning two and a half hours.
Example: TIME Type
CREATE TABLE tasks (duration TIME);
INSERT INTO tasks VALUES ('02:30:00');DATETIME and TIMESTAMP
DATETIME stores a combined calendar date and time exactly as entered with no timezone conversion, whereas TIMESTAMP internally converts to UTC on save and back to the session's timezone on read — a subtle but important difference for multi-timezone apps.
Example: DATETIME and TIMESTAMP
CREATE TABLE events (
starts_at DATETIME,
logged_at TIMESTAMP
);YEAR Type
YEAR is a compact 1-byte column that stores just a year value (like 2024), which is more space-efficient than DATE when you genuinely only need to track a year, such as a car's model year.
Example: YEAR Type
CREATE TABLE cars (model_year YEAR);
INSERT INTO cars VALUES (2024);Date Formatting
Built-in functions let you reformat how a stored date displays (e.g. 'March 5, 2024' instead of 2024-03-05) or perform date arithmetic like adding 30 days, without altering the underlying stored value.
Example: Date Formatting
SELECT DATE_FORMAT('2024-03-05', '%M %d, %Y') AS formatted;Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: