Date & Time Types — MySQL

MySQL Date and Time Types

Handling dates and times correctly is one of the trickier aspects of database design. MySQL provides five dedicated temporal types, each with different ranges, storage sizes, and timezone behaviors. Choosing the wrong type or mishandling timezones leads to bugs that are notoriously hard to diagnose.

Overview of Temporal Types

Type	Range	Storage	Timezone Aware?
DATE	1000-01-01 to 9999-12-31	3 bytes	No
TIME[(fsp)]	-838:59:59 to 838:59:59	3-6 bytes	No
DATETIME[(fsp)]	1000-01-01 00:00:00 to 9999-12-31 23:59:59	5-8 bytes	No
TIMESTAMP[(fsp)]	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC	4-7 bytes	Yes — stored as UTC
YEAR	1901 to 2155	1 byte	No

The optional (fsp) suffix specifies fractional seconds precision (0-6). For example, DATETIME(3) stores millisecond precision.

DATE

DATE stores a calendar date without any time component — year, month, day only. It is ideal for birth dates, publication dates, due dates, and any date that has no meaningful time-of-day component.

SQL

CREATE TABLE events (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name  VARCHAR(200) NOT NULL,
  event_date  DATE NOT NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert dates
INSERT INTO events (event_name, event_date) VALUES
  ('Product Launch', '2024-03-15'),
  ('Annual Review',  '2024-12-01');

-- Date comparisons
SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Date arithmetic
SELECT
  event_name,
  event_date,
  DATEDIFF(event_date, CURDATE()) AS days_until,
  DATE_ADD(event_date, INTERVAL 7 DAY) AS plus_one_week
FROM events;

-- Format dates for display
SELECT event_name, DATE_FORMAT(event_date, '%W, %M %e, %Y') AS formatted_date
FROM events;

-- DATE_FORMAT result example:
-- Friday, March 15, 2024

TIME

TIME stores a time value — hours, minutes, seconds — but not a date. Notably, TIME can also represent elapsed time (duration), not just time-of-day. The range extends far beyond 24 hours precisely for this reason.

SQL

CREATE TABLE appointments (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  start_time TIME NOT NULL,
  end_time   TIME NOT NULL
);

INSERT INTO appointments (start_time, end_time) VALUES
  ('09:00:00', '10:30:00'),
  ('14:15:00', '15:00:00');

-- Duration of each appointment
SELECT
  start_time,
  end_time,
  TIMEDIFF(end_time, start_time) AS duration,
  TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 60 AS duration_minutes
FROM appointments;

-- Filter by time range
SELECT * FROM appointments WHERE start_time BETWEEN '08:00:00' AND '12:00:00';

-- Current time functions
SELECT CURTIME();         -- current time (no date)
SELECT NOW();             -- current date AND time

DATETIME

DATETIME combines a date and time into one value. Unlike TIMESTAMP, DATETIME is not timezone-aware — it stores exactly what you give it and returns exactly that, regardless of the server's timezone setting.

This makes DATETIME predictable and portable, but it means you are responsible for timezone handling in your application.

SQL

CREATE TABLE blog_posts (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(500) NOT NULL,
  body         MEDIUMTEXT NOT NULL,
  published_at DATETIME,   -- NULL = draft
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                           ON UPDATE CURRENT_TIMESTAMP
);

-- Insert with explicit datetime
INSERT INTO blog_posts (title, body, published_at)
VALUES ('Hello World', 'My first post.', '2024-03-15 10:30:00');

-- Query posts from a date range
SELECT title, published_at
FROM blog_posts
WHERE published_at >= '2024-01-01 00:00:00'
  AND published_at < '2025-01-01 00:00:00';

-- Fractional seconds (microseconds)
CREATE TABLE precision_log (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)  -- microsecond precision
);

TIMESTAMP

TIMESTAMP is similar to DATETIME but with two critical differences:

Stored as UTC: MySQL converts TIMESTAMP values from the session timezone to UTC when storing, and from UTC back to the session timezone when retrieving.
Narrower range: Only 1970-01-01 to 2038-01-19 (the Unix 32-bit timestamp limit).

TIMESTAMP's timezone conversion is both its biggest advantage and its most common source of bugs.

SQL

CREATE TABLE audit_log (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  action     VARCHAR(100) NOT NULL,
  user_id    INT UNSIGNED,
  -- Auto-set on insert, auto-update on every UPDATE
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
             ON UPDATE CURRENT_TIMESTAMP
);

-- Demonstrating timezone conversion
SET SESSION time_zone = 'America/New_York';    -- UTC-5 in winter

INSERT INTO audit_log (action, user_id) VALUES ('login', 42);

-- Shows Eastern time
SELECT created_at FROM audit_log WHERE user_id = 42;
-- 2024-01-15 09:00:00

SET SESSION time_zone = 'UTC';

-- Same row, now shows UTC
SELECT created_at FROM audit_log WHERE user_id = 42;
-- 2024-01-15 14:00:00  (5 hours later = UTC)

Warning

The year 2038 problem: TIMESTAMP columns cannot store dates after 2038-01-19 03:14:07 UTC. Any application that stores future dates in a TIMESTAMP column and plans to run past 2038 will fail. Use DATETIME for dates that may extend beyond 2038 (appointment scheduling, subscription expiry, etc.).

TIMESTAMP Auto-Update Patterns

SQL

-- Pattern 1: Only auto-set on creation (immutable created_at)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

-- Pattern 2: Auto-update on every change (rolling updated_at)
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- Pattern 3: Both columns together (most common pattern)
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
           ON UPDATE CURRENT_TIMESTAMP

-- Verify the auto-update behavior
UPDATE audit_log SET action = 'logout' WHERE id = 1;
SELECT updated_at FROM audit_log WHERE id = 1;
-- updated_at is now the current timestamp

Note

Prior to MySQL 5.6, a table could have only one TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP. This restriction was removed in 5.6. In MySQL 8.0, DATETIME also supports DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

DATETIME vs TIMESTAMP: Which to Choose

Criteria	Use DATETIME	Use TIMESTAMP
Date range needed	Before 1970 or after 2038	Only 1970-2038 needed
Timezone handling	Handle in application	Let MySQL convert timezone
Auto-update on save	Yes (MySQL 8.0+)	Yes (all versions)
Storage	5-8 bytes	4-7 bytes (slightly smaller)
Common use cases	Birth dates, future appointments, publishing dates	Audit logs, event times, row tracking

Tip

A common best practice: store all TIMESTAMP values in UTC at the application level too. Set the MySQL server timezone to UTC (default-time-zone='+00:00' in my.cnf). Then TIMESTAMP and DATETIME behave identically for UTC values, but DATETIME is safer for future-proofing beyond 2038.

YEAR

YEAR stores a 4-digit year using only 1 byte. Its range is 1901 to 2155. It's useful for publication years, model years, and fiscal years.

SQL

CREATE TABLE books (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(300) NOT NULL,
  publication_year YEAR,
  isbn           CHAR(17)
);

INSERT INTO books (title, publication_year) VALUES
  ('Clean Code', 2008),
  ('The Pragmatic Programmer', 1999),
  ('Designing Data-Intensive Applications', 2017);

-- Filter by year range
SELECT title, publication_year
FROM books
WHERE publication_year BETWEEN 2000 AND 2020;

-- Current year
SELECT YEAR(CURDATE());   -- 2024

Date and Time Functions

SQL

-- Current date/time functions
SELECT NOW();            -- current date and time: 2024-01-15 14:30:00
SELECT CURDATE();        -- current date only: 2024-01-15
SELECT CURTIME();        -- current time only: 14:30:00
SELECT UTC_TIMESTAMP();  -- current UTC datetime regardless of session timezone
SELECT UNIX_TIMESTAMP(); -- seconds since epoch: 1705325400

-- Date extraction
SELECT
  YEAR('2024-03-15'),   -- 2024
  MONTH('2024-03-15'),  -- 3
  DAY('2024-03-15'),    -- 15
  DAYNAME('2024-03-15'), -- Friday
  WEEKDAY('2024-03-15'), -- 4 (0=Monday, 6=Sunday)
  QUARTER('2024-03-15'); -- 1

-- Date arithmetic
SELECT DATE_ADD('2024-01-15', INTERVAL 30 DAY);   -- 2024-02-14
SELECT DATE_SUB('2024-01-15', INTERVAL 1 YEAR);   -- 2023-01-15
SELECT DATE_ADD('2024-01-15', INTERVAL '1-6' YEAR_MONTH); -- 2025-07-15
SELECT DATEDIFF('2024-12-31', '2024-01-01');       -- 365

-- Date formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 2024-01-15 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');           -- 15/01/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');      -- Monday, January 15, 2024

Common Pitfalls

Pitfall 1: Filtering a TIMESTAMP with BETWEEN

SQL

-- This misses the last day because BETWEEN is inclusive
-- but '2024-01-31' without time = '2024-01-31 00:00:00'
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- MISSES rows from 2024-01-31 after midnight!

-- Correct approach:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';

Pitfall 2: Applying Functions to Indexed Datetime Columns

SQL

-- BAD: wrapping an indexed column in a function prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: use a range condition so the index is used
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Pitfall 3: Storing Dates as Strings

SQL

-- BAD: date stored as VARCHAR
CREATE TABLE events_bad (
  event_date VARCHAR(10)  -- '2024-03-15' stored as text
);
-- Date arithmetic, comparison, and indexing all break or work poorly

-- GOOD: use the DATE type
CREATE TABLE events_good (
  event_date DATE NOT NULL
);

Warning

Never store dates as VARCHAR or INT (Unix timestamp). Use MySQL's native date types — they validate input, support date arithmetic functions, have proper index support, and communicate intent clearly to anyone reading the schema.