Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 75 ยท Data Types

Date and Time Types

Learn MySQL DATE, DATETIME, TIMESTAMP, TIME and YEAR, fractional seconds, time zone conversion, the 2038 limit and how to choose between DATETIME and TIMESTAMP.

Dates and times look simple until a user in another time zone, a daylight saving change or a year-2038 date shows up. MySQL has five temporal types, and two of them, DATETIME and TIMESTAMP, look almost identical but behave very differently. Choosing well early saves painful migrations later.

This lesson explains each type, how fractional seconds work, what the session time zone does, and a practical rule for choosing between DATETIME and TIMESTAMP. MySQL's own date functions (DATE_FORMAT, DATE_ADD, TIMESTAMPDIFF) get their own lesson, Strings, Dates and Math Functions. Live examples here use SQLite's strftime() and datetime() as stand-ins, and each one is labeled.

Five temporal types

DATE A calendar day, no time.
TIME A time of day or a duration.
DATETIME Date plus time, stored as written.
TIMESTAMP A moment in UTC, shown in your zone.

YEAR holds just a four-digit year.

1 / 2

The five types

Type Format Range Storage
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31 3 bytes
TIME HH:MM:SS -838:59:59 to 838:59:59 3 bytes
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59 5 bytes
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC 4 bytes
YEAR YYYY 1901 to 2155 (and 0000) 1 byte

TIME is wider than a clock: it can hold 838 hours, which makes it usable for durations such as "125 hours of work". YEAR always has four digits in MySQL 8; the old two-digit YEAR(2) was removed.

MySQL accepts a flexible input format. The strings '2025-03-09', '20250309' and '25-03-09' all become the same DATE, but mixed forms are a source of bugs. Always write dates in ISO format (YYYY-MM-DD) and times with 24-hour clock.

Fractional seconds

Every type that includes a time takes an optional fractional seconds precision from 0 to 6 digits: DATETIME(3) keeps milliseconds, TIMESTAMP(6) keeps microseconds. The default is 0, which means fractions are rounded, not truncated, when you insert them.

SQL
CREATE TABLE events (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  occurred_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

INSERT INTO events (name) VALUES ('page_view');
-- occurred_at: 2025-03-09 14:30:12.482 (an example value)

The fractional part costs extra storage: 0 bytes for precision 0, 1 byte for 1 or 2 digits, 2 bytes for 3 or 4 digits and 3 bytes for 5 or 6 digits. If your DEFAULT uses CURRENT_TIMESTAMP(3), the column must have the same precision, otherwise MySQL rejects the definition.

SQLite stores times as text, numbers or Julian days instead of dedicated types, but its strftime('%f') shows the seconds with milliseconds, which makes a useful stand-in:

SQL Seconds with fractions (stand-in for DATETIME(3))

Press Run (or Ctrl + Enter) to see the result here.

You see 2025-03-09 14:30:12.482 for the first column and 2025-03-09 14:30:12 for the second. The input had four fractional digits (.4816), and the three-digit result was rounded up. MySQL's DATETIME(3) rounds in the same way, which is why .4816 becomes .482 there too. Check how your target server treats extra digits before relying on either, because some engines truncate.

Time zones: where DATETIME and TIMESTAMP differ

This is the important difference.

  • A DATETIME is stored exactly as you wrote it, with no zone information. If you insert '2025-03-09 14:30:00', you read back 2025-03-09 14:30:00, whatever the server or session time zone.
  • A TIMESTAMP is converted to UTC for storage and converted back to the session time zone on retrieval. Two sessions with different time_zone settings see different text for the same stored moment.
SQL
SET time_zone = '+00:00';
INSERT INTO log (created_at_ts, created_at_dt) VALUES ('2025-06-01 12:00:00', '2025-06-01 12:00:00');

SET time_zone = '+02:00';
SELECT created_at_ts, created_at_dt FROM log;
-- created_at_ts: 2025-06-01 14:00:00   (converted to the new session zone)
-- created_at_dt: 2025-06-01 12:00:00   (unchanged)

The session zone comes from the time_zone variable, which defaults to the server's system_time_zone. Named zones such as 'Europe/Berlin' or 'America/New_York' work only if the server's time zone tables have been loaded (mysql_tzinfo_to_sql on Linux, and managed services usually do this for you). Offsets like '+02:00' always work, but they do not follow daylight saving time.

Because of this conversion, a TIMESTAMP column is the right type for "when did this happen as an absolute moment". It also means that changing the server's time zone silently changes how existing rows display, which surprises teams that never set it deliberately.

You can imitate the idea with SQLite: the stored value is UTC and display applies an offset. Here +2 hours plays the role of SET time_zone = '+02:00':

SQL One stored moment, two displayed times (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Notice that the second row crosses midnight: 2025-12-24 23:30:00 UTC is already 2025-12-25 01:30:00 at +2. A report grouped by calendar day gives different answers depending on the zone, which is why you decide the reporting zone on purpose.

The 2038 problem

A TIMESTAMP is stored as a 32-bit count of seconds since 1970-01-01 00:00:00 UTC. The largest value, 2,147,483,647, is 2038-01-19 03:14:07 UTC. One second later the value would not fit, and MySQL refuses to store it. That affects anything in the future: a subscription that expires in 2040, a 30-year loan schedule, a "valid until" date.

You can see the boundary arithmetic with SQLite's epoch conversion:

SQL The last TIMESTAMP second

Press Run (or Ctrl + Enter) to see the result here.

The first column is 2147483647, the second is 2038-01-19 03:14:07 and the third is 2038-01-19 03:14:08. SQLite keeps working because it does not use 32 bits, but a MySQL TIMESTAMP column would reject that third value with an out-of-range error under strict mode. A DATETIME column has no such limit, and covers the years 1000 to 9999.

The functions UNIX_TIMESTAMP() and FROM_UNIXTIME() were extended on 64-bit platforms in MySQL 8.0.28 to work past 2038, but the TIMESTAMP column type itself still ends in 2038. Choose DATETIME for anything that may be later than that.

DATETIME versus TIMESTAMP: how to choose

Question DATETIME TIMESTAMP
Range after 2038 Yes No
Zone conversion None, stored as written Automatic to and from UTC
Storage 5 bytes (+ fraction) 4 bytes (+ fraction)
Typical use Scheduled times, future dates, local wall-clock times Audit columns such as created_at

A practical rule that works for most applications:

  1. Store UTC everywhere and convert to the user's zone in the application. This works with either type, and keeps reports consistent.
  2. Use TIMESTAMP for "this row was created or changed now" columns, when you want DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP and do not care about 2038.
  3. Use DATETIME for anything that is a future or scheduled moment, a local meeting time (for example "9:00 on 2027-03-28 in Berlin", where the zone rules may change), or when you need a very wide range.
  4. If you need the user's local wall time to survive zone rule changes, store the local DATETIME and the zone name (VARCHAR) in separate columns.

Laravel uses TIMESTAMP for created_at and updated_at by default, and its dateTime() blueprint method creates DATETIME. Many teams standardize on DATETIME with UTC to avoid both the 2038 limit and surprises from the session zone.

Auto-initialization

A TIMESTAMP or DATETIME column can fill itself in:

SQL
CREATE TABLE posts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Inserting a row sets both columns to the server's current time. Any update that changes a column also refreshes updated_at. This works for DATETIME too, not only TIMESTAMP. SQLite lacks ON UPDATE, so the live runner would need a trigger instead, which the Triggers lesson covers.

Invalid and zero dates

Old MySQL versions accepted '0000-00-00' as a "zero date" and silently turned bad input into zeros. Modern servers include NO_ZERO_DATE and STRICT_TRANS_TABLES in the default SQL mode, so an invalid date such as '2025-02-30' is an error instead. You will see how that mode works in SQL Modes and Strictness. If you inherit an old database that uses zero dates, clean them up to NULL before upgrading.

Date arithmetic has one more surprise that distinguishes MySQL from SQLite. Adding a month to January 31 in MySQL, DATE_ADD('2025-01-31', INTERVAL 1 MONTH), clamps to the last valid day and gives 2025-02-28. SQLite's date('2025-01-31', '+1 month') normalizes the invalid February 31 and gives 2025-03-03:

SQL Month arithmetic at the end of a month (SQLite behavior)

Press Run (or Ctrl + Enter) to see the result here.

The first value is 2025-03-03, a different answer from MySQL's 2025-02-28. The second column shows the portable way to ask for "the last day of February": go to the first day of March and step back one day. Do not assume date arithmetic ports between engines: test it on the target.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to a TIMESTAMP value when two sessions use different time_zone settings?

    TIMESTAMP is stored as UTC and converted to the session time zone on retrieval. DATETIME is returned exactly as stored.
  2. 2. Which type can store the date 2045-06-01 without any trouble?

    TIMESTAMP ends at 2038-01-19 03:14:07 UTC. DATETIME covers the years 1000 to 9999.
  3. 3. What does DATETIME(3) change?

    The number in parentheses is the fractional seconds precision from 0 to 6. DATETIME(3) keeps milliseconds.
  4. 4. Which choice fits a created_at audit column when the 2038 limit does not matter?

    TIMESTAMP is compact, can fill itself in with CURRENT_TIMESTAMP and stores an absolute moment, which suits audit columns.

Summary

  • DATE, TIME, DATETIME, TIMESTAMP and YEAR cover calendar days, times of day or durations, wall-clock moments, absolute moments and four-digit years.
  • Fractional seconds go from 0 to 6 digits and cost 0 to 3 extra bytes.
  • DATETIME is stored as written. TIMESTAMP is converted to UTC and back through the session time zone.
  • TIMESTAMP ends at 2038-01-19 03:14:07 UTC, so use DATETIME for anything later.
  • Store UTC, convert for display in the application, and write dates in ISO format.
  • Date arithmetic can differ between engines: always test month math on your target server.

Next: Character Sets and Collations.