MySQL Unix Time Table Design: INT, BIGINT, Seconds, Milliseconds, and Indexes

Sometimes it is correct to store time in MySQL as Unix time instead of DATETIME.

This usually happens when a system receives epoch values from APIs, message queues, logs, mobile clients, analytics pipelines, or event streams. In those cases, Unix time can make cross-system comparisons easier because it is a single numeric value representing a UTC-based instant.

But the schema must be explicit. The biggest mistakes are using INT for values that may exceed 2038, mixing seconds and milliseconds, and naming an integer column expires_at without documenting its unit.

Recommended column naming

Do not hide the unit.

Prefer:

expires_at_epoch_seconds BIGINT NULL

or:

expires_at_epoch_ms BIGINT NULL

Avoid:

expires_at INT NOT NULL

The name expires_at normally suggests a date-time value. If the column stores an integer, include epoch_seconds, epoch_ms, or another clear suffix.

INT is usually the wrong default

MySQL signed INT stores up to 2147483647. If that value is interpreted as Unix seconds, the useful range ends at the 2038 boundary.

That means this is fragile:

created_at_epoch_seconds INT NOT NULL

INT UNSIGNED extends the numeric range, but it still creates a custom convention and does not support milliseconds. It may be acceptable for short-lived counters or legacy compatibility, but it is not a good default for new time fields.

For new schemas, choose BIGINT for Unix time.

Seconds or milliseconds?

MySQL's UNIX_TIMESTAMP() and FROM_UNIXTIME() work with Unix seconds. JavaScript, many mobile SDKs, and many event pipelines use milliseconds.

Make the unit visible:

event_time_epoch_seconds BIGINT NOT NULL
event_time_epoch_ms BIGINT NOT NULL

Do not store milliseconds in a column named event_time or event_time_epoch and rely on memory.

Typical digit lengths:

10 digits: epoch seconds, such as 1721385000
13 digits: epoch milliseconds, such as 1721385000000
16 digits: epoch microseconds
19 digits: epoch nanoseconds

If your value has 13 digits, it does not fit the MySQL FROM_UNIXTIME() seconds expectation directly. Divide by 1000 when converting for display.

Table design for event data

For event logs, use BIGINT and index the numeric time column:

CREATE TABLE user_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  event_name VARCHAR(80) NOT NULL,
  event_time_epoch_ms BIGINT NOT NULL,
  payload JSON NULL,
  INDEX idx_event_time (event_time_epoch_ms),
  INDEX idx_user_time (user_id, event_time_epoch_ms)
);

Query a time window:

SELECT *
FROM user_events
WHERE event_time_epoch_ms >= 1721347200000
  AND event_time_epoch_ms < 1721433600000
ORDER BY event_time_epoch_ms;

This query can use the idx_event_time index efficiently because it compares numbers directly.

Table design for expiration fields

For business expiration dates, DATETIME(3) is often better. But if your application contract already uses epoch milliseconds everywhere, store it consistently:

CREATE TABLE api_tokens (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  token_hash VARBINARY(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  expires_at_epoch_ms BIGINT NULL,
  revoked_at_epoch_ms BIGINT NULL,
  created_at_epoch_ms BIGINT NOT NULL,
  UNIQUE KEY uk_token_hash (token_hash),
  INDEX idx_expires_at_epoch_ms (expires_at_epoch_ms),
  INDEX idx_user_expires (user_id, expires_at_epoch_ms)
);

Find valid tokens:

SELECT *
FROM api_tokens
WHERE revoked_at_epoch_ms IS NULL
  AND (expires_at_epoch_ms IS NULL OR expires_at_epoch_ms > 1721385000000);

Here NULL means no expiration. That is usually clearer than storing a fake far-future integer.

Generated DATETIME column for readability

If developers need to inspect rows in SQL clients, add a generated column for display.

For epoch seconds:

CREATE TABLE jobs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_at_epoch_seconds BIGINT NOT NULL,
  run_at_utc DATETIME
    GENERATED ALWAYS AS (FROM_UNIXTIME(run_at_epoch_seconds)) STORED,
  INDEX idx_run_at_epoch_seconds (run_at_epoch_seconds),
  INDEX idx_run_at_utc (run_at_utc)
);

For epoch milliseconds:

CREATE TABLE jobs_ms (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_at_epoch_ms BIGINT NOT NULL,
  run_at_utc DATETIME(3)
    GENERATED ALWAYS AS (FROM_UNIXTIME(run_at_epoch_ms / 1000.0)) STORED,
  INDEX idx_run_at_epoch_ms (run_at_epoch_ms)
);

Be careful: FROM_UNIXTIME() returns a value expressed in the MySQL session time zone. If you want stable UTC display, set the session time zone to +00:00 for application and migration connections, or do conversion in the application.

Avoid converting inside WHERE when you need an index

This can be slow:

SELECT *
FROM user_events
WHERE FROM_UNIXTIME(event_time_epoch_ms / 1000.0) >= '2026-07-19 00:00:00';

The function is applied to the column, so MySQL may not use the numeric time index effectively.

Prefer converting the boundary once, then comparing the raw number:

SELECT *
FROM user_events
WHERE event_time_epoch_ms >= 1784419200000
  AND event_time_epoch_ms < 1784505600000;

In application code, compute the epoch boundaries before sending the SQL query.

Time zone rule

Unix time represents a UTC-based instant. It does not store a time zone.

That is useful for machine events, but not always for business calendar concepts. A store's "coupon expires at local midnight" may require:

the local date-time
the store time zone
the corresponding UTC instant for comparison

If you only store epoch milliseconds, you know the instant, but you do not know the business time zone that created it. Store timezone_name such as Asia/Shanghai or America/New_York if that context matters.

Migration from INT seconds to BIGINT milliseconds

If an old table stores seconds in INT, migrate carefully:

ALTER TABLE api_tokens
  ADD COLUMN expires_at_epoch_ms BIGINT NULL;

UPDATE api_tokens
SET expires_at_epoch_ms = expires_at_epoch_seconds * 1000
WHERE expires_at_epoch_seconds IS NOT NULL;

ALTER TABLE api_tokens
  ADD INDEX idx_expires_at_epoch_ms (expires_at_epoch_ms);

Then update application code to write milliseconds. Keep both columns temporarily only during the migration window.

Add validation to prevent mixed units:

ALTER TABLE api_tokens
  ADD CONSTRAINT chk_expires_at_epoch_ms
  CHECK (
    expires_at_epoch_ms IS NULL
    OR expires_at_epoch_ms BETWEEN 946684800000 AND 4102444800000
  );

This example allows values between 2000-01-01 and 2100-01-01 in milliseconds. Adjust the range for your domain.

Recommended patterns

Use BIGINT Unix time when:

the value comes from logs, SDKs, event streams, or distributed systems
you need fast numeric range queries
you use epoch milliseconds consistently across services
you do not need MySQL date arithmetic as the primary interface

Use DATETIME(3) instead when:

humans read and edit the value in SQL
it is a business deadline rather than a machine event
you need dates far beyond common Unix function ranges
you want MySQL date functions and readable values by default

If you store Unix time, make three decisions explicit:

seconds or milliseconds
BIGINT, not signed INT, for new schemas
UTC instant vs business local time meaning

To inspect a value quickly, use the Unix Timestamp Converter and confirm whether the number is seconds or milliseconds before writing queries or migration scripts.