Working with Unix timestamps (also known as Unix time or epoch time) is common in backend systems, logs, and data pipelines

Working with Unix timestamps (also known as Unix time or epoch time) is common in backend systems, logs, and data pipelines. When working with MySQL, understanding how to convert and query Unix timestamps is critical—especially when timestamps are stored in milliseconds.

In this post, we’ll walk through a real-world SQL query that converts Unix timestamps into readable datetime formats using MySQL's FROM_UNIXTIME() function.

The Use Case

Imagine you're analyzing event records stored in a table named t_market_event_issue_record. The table contains fields like trigger_time, finish_time, create_time, and update_time—all stored as Unix time in milliseconds.

The Use Case

Imagine you're analyzing event records stored in a table named t_market_event_issue_record. The table contains fields like trigger_time, finish_time, create_time, and update_time—all stored as Unix time in milliseconds.

Here's a sample query:

SELECT
    id,
    uid,
    issue_id,
    issue_no,
    issue_type,
    issue_params,
    ext_fields,
    issue_result,
    up_seq_no,
    down_seq_no,
    FROM_UNIXTIME(trigger_time / 1000) AS trigger_time,
    FROM_UNIXTIME(finish_time / 1000) AS finish_time,
    device_id,
    ip,
    status,
    FROM_UNIXTIME(create_time / 1000) AS create_time,
    FROM_UNIXTIME(update_time / 1000) AS update_time
FROM
    t_market_event_issue_record
WHERE
    status = 2
    AND issue_id = '141'
    AND create_time BETWEEN 1738947600000 AND 1739206800000
LIMIT 100;

Here's is the result:

Why Use FROM_UNIXTIME?

MySQL stores and handles dates in DATETIME or TIMESTAMP format, but sometimes systems store time as Unix timestamps—typically the number of seconds (or milliseconds) since 1970-01-01 00:00:00 UTC.

The function FROM_UNIXTIME(unix_timestamp) converts Unix timestamps (in seconds) into MySQL's DATETIME format.

Since your timestamps are in milliseconds, you divide them by 1000:

FROM_UNIXTIME(trigger_time / 1000)

Without this division, you’d end up with invalid or out-of-range dates.

Improving Readability with Aliases

The original fields (trigger_time, finish_time, etc.) are stored in raw format. By using AS trigger_time in the SELECT clause, you alias the converted datetime column to match the original name—this makes the results clearer without changing the underlying data.

Time Range Filtering

AND create_time BETWEEN 1738947600000 AND 1739206800000

This filters records within a Unix time range. Even though you’re selecting human-readable dates using FROM_UNIXTIME, you still filter using the raw Unix time values.

To define these timestamps:

1738947600000 → corresponds to 2025-07-06 00:00:00 UTC

1739206800000 → corresponds to 2025-07-08 00:00:00 UTC

You can generate these values using:

SELECT UNIX_TIMESTAMP('2025-07-06 00:00:00') \* 1000;

Tips and Best Practices

• Milliseconds vs Seconds: Always confirm the unit of your timestamps. Divide by 1000 if they're in milliseconds.

• Readable Outputs: Use FROM_UNIXTIME() in your SELECTs to make logs or reports easier to interpret.

• Index Usage: If you're filtering by create_time (raw Unix time), ensure that field is indexed for performance.

• Testing Timestamps: Use SELECT FROM_UNIXTIME(...) to debug or inspect single timestamp values.

Summary

Using Unix timestamps in MySQL requires just a bit of conversion magic with FROM_UNIXTIME(). This small adjustment lets you keep the precision of machine timestamps while making the data human-friendly and easier to work with.

The next time you're digging into event logs or transaction histories with millisecond timestamps, use this pattern to simplify your queries and your life.