Convert Unix Timestamp in MySQL — FROM_UNIXTIME and UNIX_TIMESTAMP Guide

MySQL has two main functions for Unix timestamp work: FROM_UNIXTIME(ts) converts a Unix timestamp to a DATETIME, and UNIX_TIMESTAMP(date) goes the other direction. Both look simple but have timezone behavior that has bitten enough developers to be worth understanding before you write production queries.

This guide covers both functions, the format string variants, the milliseconds problem, and the timezone settings that change query results without warning.

Convert Unix Timestamp to a Date with FROM_UNIXTIME

The basic call takes a Unix timestamp and returns a DATETIME in the session's current timezone:

SELECT FROM_UNIXTIME(1711000000);
-- 2024-03-21 03:46:40  (in EST)
-- 2024-03-21 07:46:40  (in UTC)

-- With a format string
SELECT FROM_UNIXTIME(1711000000, '%Y-%m-%d %H:%i:%s');
-- 2024-03-21 03:46:40

SELECT FROM_UNIXTIME(1711000000, '%M %D, %Y');
-- March 21st, 2024

The format characters are MySQL-specific — %Y is year, %m is zero-padded month, %M is full month name, %D adds an English suffix, %H is 24-hour, %h is 12-hour. Full reference is in the MySQL DATE_FORMAT documentation.

The timezone gotcha

FROM_UNIXTIME returns the result in your session's current timezone, not UTC. Two clients connecting to the same database from different timezones will see different output for the same query. To force UTC:

SELECT CONVERT_TZ(FROM_UNIXTIME(1711000000), @@session.time_zone, '+00:00');
-- always 2024-03-21 07:46:40

-- Or set the session zone first
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1711000000);
-- 2024-03-21 07:46:40

Convert a Date to Unix Timestamp with UNIX_TIMESTAMP

-- Current Unix timestamp
SELECT UNIX_TIMESTAMP();
-- e.g. 1744070000

-- From a literal datetime
SELECT UNIX_TIMESTAMP('2024-03-21 07:46:40');
-- 1711000000 (interprets the string in session timezone)

-- From a column
SELECT UNIX_TIMESTAMP(created_at) FROM orders;

UNIX_TIMESTAMP also reads the session timezone when converting a string or DATETIME column to epoch seconds. If the value was stored without explicit zone info, MySQL assumes the session zone. Set SET time_zone = '+00:00' at the start of any script that does timestamp arithmetic to make the behavior predictable.

Milliseconds in MySQL

UNIX_TIMESTAMP supports microseconds via fractional seconds (MySQL 5.6+):

SELECT UNIX_TIMESTAMP('2024-03-21 07:46:40.123456');
-- 1711000000.123456

SELECT UNIX_TIMESTAMP() * 1000;
-- current time in milliseconds (integer)

For pure millisecond timestamps stored as BIGINT, divide by 1000 before passing to FROM_UNIXTIME: FROM_UNIXTIME(ts_ms / 1000).

Sell Custom Apparel — We Handle Printing & Free Shipping

CURRENT_TIMESTAMP, NOW, and Default Values

MySQL has three ways to get the current time. They all return the same value but have slightly different semantics in DDL:

-- All equivalent for SELECT
SELECT NOW();              -- 2026-04-08 12:34:56
SELECT CURRENT_TIMESTAMP;  -- 2026-04-08 12:34:56
SELECT CURRENT_TIMESTAMP();-- 2026-04-08 12:34:56

-- The Unix integer version
SELECT UNIX_TIMESTAMP();   -- 1744070096

-- Use as default in CREATE TABLE
CREATE TABLE events (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The auto-update behavior on TIMESTAMP columns is convenient but tricky — it means updating any column on a row also updates the updated_at column. If you want a column that only updates manually, use DATETIME instead of TIMESTAMP.

MySQL Timestamp Mistakes

Mistake 1: Storing dates as VARCHAR

Dates stored as strings ("2024-03-21") cannot be compared with date functions, will not use date indexes, and break sorting if anyone ever inserts a value in a different format. Always use DATETIME, TIMESTAMP, or DATE columns.

Mistake 2: TIMESTAMP vs DATETIME

TIMESTAMP converts to UTC for storage and back to session timezone on read — values are 4 bytes. DATETIME stores the literal value with no timezone conversion — 8 bytes. For most modern apps, DATETIME with explicit UTC convention is safer because it never silently changes when someone changes the server timezone.

Mistake 3: Mixing seconds and milliseconds

Application code stores milliseconds, MySQL function expects seconds. FROM_UNIXTIME(1711000000123) returns a date in the year 56270. Always check the digit count and divide if needed.

Mistake 4: Year 2038

The TIMESTAMP type is a signed 32-bit integer of seconds since epoch. It overflows on January 19, 2038. DATETIME is not affected. New tables should not use TIMESTAMP for any column where you might need to store dates after 2038.

For verifying any specific value without firing up a SQL session, the free Unix timestamp converter handles seconds and milliseconds.

Frequently Asked Questions

How do I convert a Unix timestamp to a date in MySQL?

Use FROM_UNIXTIME(timestamp) — it returns a DATETIME in the session's current timezone. To force UTC, run SET time_zone = "+00:00" at the start of your session, or wrap with CONVERT_TZ.

How do I get the current Unix timestamp in MySQL?

SELECT UNIX_TIMESTAMP() returns the current time as a Unix timestamp integer in seconds. SELECT NOW() returns it as a formatted DATETIME. Both reflect the session's current time.

Why does FROM_UNIXTIME show the wrong time in MySQL?

It returns the result in your session timezone, not UTC. Two connections from different time zones will see different output for the same query. Set time_zone explicitly with SET time_zone = "+00:00" or use CONVERT_TZ to normalize.

What is the difference between TIMESTAMP and DATETIME in MySQL?

TIMESTAMP stores 4 bytes, converts to UTC for storage, and is limited to dates between 1970 and 2038 (Y2K38 problem). DATETIME stores 8 bytes, has no automatic timezone conversion, and supports dates up to year 9999. For new applications, DATETIME with explicit UTC convention is usually safer.

How do I handle millisecond timestamps in MySQL?

MySQL FROM_UNIXTIME expects seconds. For a millisecond timestamp, divide by 1000 first: FROM_UNIXTIME(ts_ms / 1000). MySQL 5.6+ supports fractional seconds in DATETIME and TIMESTAMP columns up to 6 decimal places (microseconds).

Will MySQL TIMESTAMP columns break in 2038?

Yes. The TIMESTAMP type is a signed 32-bit integer of seconds since the Unix epoch and overflows on January 19, 2038 at 03:14:07 UTC. DATETIME is not affected. Migrate any TIMESTAMP columns that might store future dates to DATETIME well before 2038.