Introduction

In MySQL, “timestamp to datetime” can mean two different conversions, and mixing them up is where most confusion starts. Sometimes you are converting a Unix timestamp such as 1697040000 into a readable date-time. Other times you are converting between MySQL’s TIMESTAMP and DATETIME column types. The SQL you use depends on which problem you actually have.

Convert a Unix Timestamp With FROM_UNIXTIME

If your value is a Unix timestamp, use FROM_UNIXTIME().

SELECT FROM_UNIXTIME(1697040000);

That returns a MySQL date-time value representing the Unix epoch seconds in your session time zone.

You can also format it as a string explicitly:

SELECT FROM_UNIXTIME(1697040000, '%Y-%m-%d %H:%i:%s');

This is the most common answer when the source value is an integer count of seconds since 1970-01-01 00:00:00 UTC.

Be Careful With Millisecond Timestamps

Many applications store epoch time in milliseconds, not seconds. If you pass a millisecond value directly into FROM_UNIXTIME, the result will be wrong.

For example, if the value is 1697040000000, divide by 1000 first:

SELECT FROM_UNIXTIME(1697040000000 / 1000);

If the column is integer-based and you want to preserve fractional seconds, use decimal division when appropriate.

SELECT FROM_UNIXTIME(1697040000123 / 1000.0);

That distinction is one of the most common causes of “MySQL converted my timestamp to nonsense.”

Convert Data While Querying a Table

Suppose you have a table with an epoch column:

1CREATE TABLE events (
2    id INT PRIMARY KEY,
3    created_epoch BIGINT
4);

You can convert it in a query:

1SELECT
2    id,
3    created_epoch,
4    FROM_UNIXTIME(created_epoch) AS created_at
5FROM events;

If the values are milliseconds:

1SELECT
2    id,
3    FROM_UNIXTIME(created_epoch / 1000.0) AS created_at
4FROM events;

This is often enough if you only need the readable time in reports or debugging output.

Persist the Converted Value Into a DATETIME Column

If you want to store the converted value permanently, create or update a DATETIME column.

1ALTER TABLE events ADD COLUMN created_at DATETIME;
2
3UPDATE events
4SET created_at = FROM_UNIXTIME(created_epoch);

For millisecond data:

UPDATE events
SET created_at = FROM_UNIXTIME(created_epoch / 1000.0);

This is useful when migrating from application-managed epoch storage to a native MySQL temporal type.

Understand TIMESTAMP Versus DATETIME

If your source is already a MySQL TIMESTAMP column, you usually do not “convert” it with FROM_UNIXTIME. It is already a temporal value.

Example:

1CREATE TABLE logs (
2    created_ts TIMESTAMP,
3    created_dt DATETIME
4);

Copying from TIMESTAMP to DATETIME is just a direct assignment:

UPDATE logs
SET created_dt = created_ts;

The important difference is semantic:

• 'TIMESTAMP is stored in UTC internally and converted by session time zone'
• 'DATETIME stores the literal date and time you give it'

So if your issue is about time zone behavior, the fix may not be conversion at all. It may be choosing the correct column type.

Time Zone Matters

FROM_UNIXTIME returns a value interpreted in the current MySQL session time zone. If you need a specific time zone representation, be explicit.

SELECT CONVERT_TZ(FROM_UNIXTIME(1697040000), '+00:00', '-04:00');

This matters in applications where epoch values are UTC but reports are meant for local business time.

Common Pitfalls

A common mistake is confusing epoch milliseconds with epoch seconds. FROM_UNIXTIME expects seconds unless you scale the value yourself.

Another issue is using FROM_UNIXTIME on a column that is already TIMESTAMP or DATETIME. In that case, you are solving the wrong problem.

Developers also sometimes ignore time zone behavior and then think the conversion itself is wrong. The function may be correct while the session time zone is not what they expected.

Finally, do not store human-readable strings when you really need native temporal data. If you intend to sort, index, or filter by time, use DATETIME or TIMESTAMP columns.

Summary

• Use FROM_UNIXTIME() when converting Unix epoch values to MySQL date-time values.
• Divide by 1000 first if the source data is in milliseconds.
• Copy between MySQL TIMESTAMP and DATETIME columns with direct assignment, not epoch conversion.
• Check session time zone behavior when the converted value looks offset.
• Prefer native temporal column types over storing formatted date strings.