Date & Time Data Types

Date and time types are like calendars and clocks for your table: some remember only the day, some the day plus the exact time.

In this page:

DATE Type
TIME Type
DATETIME and TIMESTAMP
YEAR Type
Date Formatting

Syntax

sql

column_name DATE       -- 'YYYY-MM-DD'
column_name TIME       -- 'HH:MM:SS'
column_name DATETIME   -- 'YYYY-MM-DD HH:MM:SS'
column_name TIMESTAMP

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

sql

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

sql

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

sql

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

sql

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

sql

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:

← Previous Lesson String Data Types Next Lesson → ENUM Data Type