MySQL DATETIME vs TIMESTAMP for Expiration Fields: Avoiding the 2038 Problem

If you are designing an expiration field in MySQL, such as expires_at, valid_until, subscription_ends_at, or coupon_expires_at, the common question is:

Should it be TIMESTAMP, DATETIME, or a Unix timestamp stored as INT?

For most business expiration fields, the practical answer is:

expires_at DATETIME(3) NULL

Use DATETIME for long-term business dates. Do not use MySQL TIMESTAMP when the value may need to go beyond 2038. Do not switch to INT just to avoid TIMESTAMP unless you have a very specific epoch-time requirement.

The 2038 limit in MySQL TIMESTAMP

In MySQL, TIMESTAMP has a limited range. In modern MySQL documentation, the supported range ends at:

2038-01-19 03:14:07 UTC

This is the familiar 32-bit signed Unix timestamp boundary. If your expiration date may be used for subscriptions, lifetime plans, licenses, user bans, long-lived API keys, future reservations, or "never expire" placeholders, TIMESTAMP is not a good fit.

Even if your product only needs a few months today, schemas tend to live longer than expected. A column named expires_at often becomes reused for cases the original developer did not anticipate.

DATETIME has a much wider range

MySQL DATETIME supports a much wider range, up to:

9999-12-31 23:59:59

That makes it a better default for business dates and future expiration fields.

Example:

CREATE TABLE subscriptions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  expires_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  INDEX idx_expires_at (expires_at)
);

DATETIME(3) stores milliseconds. Use DATETIME(6) if you need microseconds. For most web products, seconds or milliseconds are enough.

TIMESTAMP also has time zone conversion behavior

TIMESTAMP is not just "a date-time type with a smaller range." MySQL stores TIMESTAMP values in UTC and converts them between the session time zone and UTC when storing and retrieving.

That behavior can be useful for audit columns such as:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

But it can surprise you when the value is a business deadline entered by a user in a specific time zone.

For example, a coupon that expires at 2027-01-01 00:00:00 in a store's local time is not the same kind of data as a server event timestamp. If your app needs local business meaning, store the intended value clearly and convert at the application boundary.

Should you use Unix time in INT?

Usually, no.

A signed 32-bit INT storing Unix seconds has the same 2038 problem. An unsigned 32-bit integer extends farther, but it introduces another custom convention and still does not solve all design issues.

This is risky:

expires_at INT NOT NULL

Problems:

  • unit ambiguity: seconds or milliseconds?
  • no built-in date readability in SQL clients
  • easy to accidentally compare milliseconds with seconds
  • signed INT hits the 2038 boundary
  • time-zone meaning is still not documented

If you truly need Unix epoch time, use BIGINT, and document the unit in the column name.

Good:

expires_at_epoch_seconds BIGINT NULL

or:

expires_at_epoch_ms BIGINT NULL

Do not name it simply expires_at if it stores an integer. Make the unit obvious.

DATETIME vs BIGINT Unix time

Choose DATETIME when:

  • people need to read or query the value in SQL
  • the date may go beyond 2038
  • the field represents a business deadline
  • you want standard MySQL date functions
  • you want less confusion in application code

Choose BIGINT Unix time when:

  • you exchange epoch values across many systems
  • logs, event streams, or analytics already use epoch time
  • you need exact numeric ordering independent of database date parsing
  • you store milliseconds or microseconds from an external system

For most application tables, DATETIME(3) is the better default.

How to represent "never expires"

Avoid using fake far-future dates such as 9999-12-31 unless your system consistently treats them as a sentinel.

Prefer one of these patterns:

expires_at DATETIME(3) NULL

NULL means no expiration.

Or:

expires_at DATETIME(3) NULL,
never_expires TINYINT(1) NOT NULL DEFAULT 0

Use a separate boolean only when the distinction matters in the UI or business logic.

Query examples

Find active rows:

SELECT *
FROM subscriptions
WHERE expires_at IS NULL OR expires_at > UTC_TIMESTAMP(3);

Find expired rows:

SELECT *
FROM subscriptions
WHERE expires_at IS NOT NULL
  AND expires_at <= UTC_TIMESTAMP(3);

If your application stores DATETIME in UTC, compare with UTC_TIMESTAMP(). If it stores local business time, be explicit about the time zone conversion in application code.

Migration from TIMESTAMP to DATETIME

If you already have:

expires_at TIMESTAMP NULL

and need dates beyond 2038, migrate to DATETIME:

ALTER TABLE subscriptions
  MODIFY expires_at DATETIME(3) NULL;

Before migration, check how your application sets the session time zone. Because TIMESTAMP values are time-zone converted, you should confirm the actual values you want to preserve.

Recommended migration checklist:

  1. Confirm the current session time zone used by the application.
  2. Export sample rows and compare displayed time with expected business meaning.
  3. Add tests for values near 2038 and far-future dates.
  4. Migrate in staging first.
  5. Verify indexes and expiration queries.

Use this rule for new MySQL application schemas:

  • created_at, updated_at: DATETIME(3) or TIMESTAMP if you intentionally want MySQL timestamp behavior and are comfortable with its range.
  • expires_at, valid_until, subscription_ends_at: DATETIME(3) by default.
  • epoch values from external systems: BIGINT, with _seconds or _ms in the column name.
  • never-expiring values: NULL, not a magic 2038 or 9999 date unless the sentinel is documented.

If you need to debug a numeric epoch value, use the Unix Timestamp Converter to check whether it is seconds or milliseconds and to compare UTC vs local display.