Choosing the Right MySQL Date and Time Type: A Complete Guide

MySQL’s Five Temporal Data Types

MySQL provides five column types for storing temporal data: DATE , DATETIME , TIMESTAMP , YEAR , and TIME . Each type differs in the kind of information it stores, its storage footprint, and its valid range.

Quick Reference

DATE : stores only a date (YYYY‑MM‑DD), 3 bytes, range 1000‑01‑01 to 9999‑12‑31.

DATETIME : stores date and time, 8 bytes, range 1000‑01‑01 00:00:00 to 9999‑12‑31 23:59:59.

TIMESTAMP : stores date and time as UTC, 4 bytes, range 1970‑01‑01 00:00:00 to 2038‑01‑19 03:14:17.

YEAR : stores only a year, 1 byte, range 1901 to 2155.

TIME : stores a time of day or interval (hh:mm:ss), 3 bytes, range –838:59:59 to 838:59:59.

When to Use Each Type

DATE

Use DATE when you need only the calendar date—e.g., birthdays, hire dates, anniversaries. It is compact (3 bytes) and covers a wide year range.

YEAR

Use YEAR for storing just a year. It occupies only 1 byte but is limited to 1901‑2155; for broader ranges consider a signed SMALLINT.

TIME

Use TIME for pure time values or durations (e.g., “08:30:00” or “‑05:00:00”). Its range spans roughly ±35 days, making it suitable for intervals as well as clock times.

DATETIME vs. TIMESTAMP

Both store date + time, but they differ in storage size, range, and timezone handling.

Storage : DATETIME uses 8 bytes; TIMESTAMP uses 4 bytes.

Range : DATETIME covers 1000‑01‑01 to 9999‑12‑31; TIMESTAMP is limited to 1970‑01‑01 UTC to 2038‑01‑19 UTC (the “2038 problem”).

Timezone : DATETIME stores the literal value without conversion. TIMESTAMP converts the input from the session’s timezone to UTC on write and back to the session’s timezone on read.

Practical Example of Timezone Conversion

CREATE TABLE timezone_test (
    `timestamp` TIMESTAMP,
    `datetime` DATETIME
);
SET SESSION time_zone = '+00:00';
INSERT INTO timezone_test VALUES ('2029-02-14 08:47', '2029-02-14 08:47');
SELECT * FROM timezone_test;
-- timestamp: 2029-02-14 08:47:00, datetime: 2029-02-14 08:47:00
SET SESSION time_zone = '-05:00';
SELECT * FROM timezone_test;
-- timestamp: 2029-02-14 03:47:00, datetime: 2029-02-14 08:47:00

This demonstrates that TIMESTAMP values shift with the session timezone, while DATETIME values remain unchanged.

Choosing Between DATETIME and TIMESTAMP

If your application must store dates outside the 1970‑2038 window, or you want a stable literal value regardless of server timezone, prefer DATETIME. For audit columns such as created_at, updated_at, or deleted_at, TIMESTAMP is often convenient because it automatically tracks the current UTC time.

Conclusion

Understanding the characteristics of MySQL’s temporal types—storage size, valid range, and timezone behavior—allows you to design tables that use the smallest appropriate column, avoid the 2038 limitation, and store dates and times accurately for your specific business needs.