What is a Unix timestamp?

A Unix timestamp is the number of seconds elapsed since the Unix epoch, 1970-01-01T00:00:00Z (UTC). It carries no timezone of its own, which is why it is a compact and unambiguous way to store a moment in time across databases, APIs, and log files.

How do I know whether a column stores seconds or milliseconds?

Count the digits or look at the maximum value. A current timestamp in seconds is about 10 digits and tops out near 10 billion; in milliseconds it is about 13 digits and approaches 10 trillion. If the column holds BIGINT values well above one trillion, it is almost certainly milliseconds.

What is the Year 2038 problem and does it affect SQL?

The Year 2038 problem occurs when Unix time is stored in a signed 32-bit integer and overflows at 2147483647, which corresponds to 03:14:07 UTC on 19 January 2038. Any SQL column declared as a 32-bit INTEGER is at risk; BIGINT columns are unaffected and remain valid for hundreds of billions of years.

Why does FROM_UNIXTIME return NULL for some rows?

FROM_UNIXTIME returns NULL when the argument is negative, larger than the engine's maximum, or not coercible to an integer. It also returns NULL for a string that does not look like an integer. Check that the source column is numeric and within range before assuming a missing date is real data loss.

How do I convert a SQL DATETIME back to a Unix timestamp?

Apply the engine's reverse function: UNIX_TIMESTAMP(col) in MySQL, EXTRACT(EPOCH FROM col) in PostgreSQL, and DATEDIFF_BIG(SECOND, '1970-01-01', col) in SQL Server. The result is always in seconds; divide or multiply by 1000 if your destination expects milliseconds.

How to Convert a Unix Timestamp in SQL Queries

A Unix timestamp is the integer count of seconds that have elapsed since 1970-01-01T00:00:00Z (UTC), and SQL databases routinely store, compare, and filter rows using that raw number. When that integer shows up in a column, a query result, or a log line, you usually want to turn it back into a human-readable date for debugging, reporting, or sanity-checking what was actually saved. Every mainstream database engine ships functions for both directions — MySQL gives you UNIX_TIMESTAMP() and FROM_UNIXTIME(), PostgreSQL relies on EXTRACT(EPOCH FROM …) and TO_TIMESTAMP(), and SQL Server expresses the same idea through DATEDIFF_BIG and DATEADD against the epoch anchor. The syntax differs, the units differ in subtle ways, and picking the wrong one is the classic reason a column full of perfectly valid timestamps appears to contain dates in the year 55000. This guide walks through the SQL patterns that work in each engine, calls out the unit traps that catch developers off guard, and shows where a browser-based Unix Timestamp Converter fills the gap when you just need to read a value quickly.

Where Unix Timestamps Come From in SQL Data

Most SQL platforms today prefer a native DATETIME or TIMESTAMP column type for storing moments in time, but raw epoch integers are still extremely common. Server-side frameworks often persist API request times, JWT expiry claims, message-queue publish times, and audit events as BIGINT columns populated from System.currentTimeMillis() in Java, time.time() in Python, or Date.now() in JavaScript. ETL jobs that flatten JSON logs into a warehouse frequently cast the epoch field straight into a numeric column without reformatting. You also see raw integers in cache-invalidation columns, scheduled-job next-run columns, and analytics event tables that need millisecond precision across millions of rows.

Because the integer is just a count, it is compact, sortable, and unambiguous, which is why databases tolerate it so well. The downside is that a value like 1699999999 looks like a meaningless ten-digit number until you know it means 2023-11-14T22:13:19Z. Recognizing that pattern — a column full of large integers with no obvious meaning — is the first step toward writing the conversion query you actually need.

Built-In SQL Functions for Unix Timestamp Conversion

Each major engine exposes the conversion through a different pair of functions, and each pair assumes a different default unit. Knowing exactly which function your platform calls for, and what it returns, prevents most of the confusion that surrounds timestamp work in SQL.

Database engine	Value → epoch	Epoch → value	Default unit
MySQL / MariaDB	UNIX_TIMESTAMP(date)	FROM_UNIXTIME(seconds)	Seconds
PostgreSQL	EXTRACT(EPOCH FROM timestamptz)	TO_TIMESTAMP(seconds)	Seconds (floating-point)
SQL Server	DATEDIFF_BIG(SECOND, '1970-01-01', dt)	DATEADD(SECOND, ts, '1970-01-01')	Seconds or milliseconds by choice
SQLite	strftime('%s', col)	datetime(col, 'unixepoch')	Seconds

MySQL's pair is the most ergonomic because the function names match the convention: UNIX_TIMESTAMP() reads a date and returns seconds, while FROM_UNIXTIME() does the reverse and accepts a format string if you want the output in a specific shape such as '%Y-%m-%d %H:%i:%s'. PostgreSQL's pair uses the EXTRACT keyword for the integer and TO_TIMESTAMP for the floating-point second count, which is especially convenient when your column already holds sub-second precision and you want to preserve it. SQL Server does not ship a dedicated pair, so you build the conversion from primitives: DATEDIFF_BIG measures the distance to the epoch anchor and DATEADD walks forward by the same amount. SQLite handles everything through its strftime and datetime modifiers, with the literal string 'unixepoch' marking which column should be read as seconds since 1970.

How to Convert a Unix Timestamp in SQL

The exact query depends on your engine, but the workflow is identical in every case. Work through these steps whenever you face a column of integers and need to turn them into readable dates.

Identify the column's declared type and the unit you stored. Open the table definition and look at whether the column is INTEGER, BIGINT, or NUMERIC. A 10-digit value strongly suggests seconds; a 13-digit value strongly suggests milliseconds. Confirm by sampling one row.
Pick the conversion function that matches your engine. Use FROM_UNIXTIME() in MySQL, TO_TIMESTAMP() in PostgreSQL, DATEADD(SECOND, @ts, '1970-01-01') in SQL Server, or datetime(col, 'unixepoch') in SQLite.
Normalize the unit before calling the function. If the column holds milliseconds and the function expects seconds, divide by 1000 first: FROM_UNIXTIME(ts / 1000) in MySQL, or TO_TIMESTAMP(ts::double precision / 1000) in PostgreSQL.
Cast the result to a string or your reporting type. Wrap the function in DATE_FORMAT, TO_CHAR, or FORMAT depending on the engine so the output is human readable rather than a raw internal datetime.
Verify against a known anchor. Run the query on a value you already know — for example 1700000000 — and confirm the engine returns 2023-11-14T22:13:20Z before trusting the output on the rest of the table.

A MySQL example using the most common pattern looks like this:

SELECT id, FROM_UNIXTIME(created_at) AS created_at_human FROM events LIMIT 10;

If your column actually holds milliseconds, divide first so the function receives seconds:

SELECT id, FROM_UNIXTIME(created_at / 1000) AS created_at_human FROM events LIMIT 10;

PostgreSQL follows the same shape but uses TO_TIMESTAMP, which accepts a double-precision argument so you can preserve fractional seconds when they matter:

SELECT id, TO_TIMESTAMP(created_at::double precision / 1000) AT TIME ZONE 'UTC' AS created_at_human FROM events LIMIT 10;

SQL Server reaches the same result by adding the epoch count to the epoch anchor date:

SELECT id, DATEADD(SECOND, created_at, '1970-01-01') AS created_at_human FROM dbo.events;

In every engine, the moment you see a date around the year 55000 or pinned to 1970, the unit is wrong and dividing by 1000 — or multiplying by 1000, depending on the direction — will fix it.

Convert Unix Timestamps Visually in Your Browser

SQL functions are perfect for production queries, but most debugging moments are ad hoc: you copy a single integer out of a query result, paste it into a chat, or stare at a log line and want to know what time it really represents. That is the job of the Unix Timestamp Converter, which runs entirely in the browser and turns any epoch value into four readable forms at once.

Paste the Unix value into the "Timestamp → Date" field and pick whether it is in seconds or milliseconds.
Read the results: ISO 8601 and UTC are timezone-independent, "Local time" reflects your device timezone, and "Relative" shows the distance from now.
To go the other way, type or paste a date — for example 2023-11-14T22:13:20Z — into the "Date → Timestamp" field to get the epoch in both seconds and milliseconds.

The tool auto-detects the unit from digit count, so a 10-digit number is treated as seconds and a 13-digit number is treated as milliseconds; you can still override the unit with the Seconds/Milliseconds selector if you know the column is unusual. Every conversion is computed locally with the platform Date object and explicit UTC formatting, which means the raw value never leaves the page — a useful property when the integer comes from production data you do not want to upload anywhere.

Seconds vs Milliseconds: The Most Common SQL Trap

The single most expensive mistake in Unix timestamp work in SQL is forgetting that JavaScript, Java, and most modern APIs express time in milliseconds while the underlying Unix convention uses seconds. The numbers differ by a factor of 1000, so a perfectly valid epoch stored as 1699999999000 (milliseconds) will be read by FROM_UNIXTIME() as a moment around the year 55831 — roughly 53,861 years after the Unix epoch. Two habits prevent most incidents. First, name the column clearly — created_at_epoch_ms tells a teammate what unit to expect on sight. Second, normalize at the boundary: convert to seconds the moment data enters the table, or convert to milliseconds the moment it leaves, but never mix the two inside a query without an explicit CAST.

If you inherit a table where the unit is unclear, a single diagnostic query often settles the question. Run MAX(ts) on the column: a value in the low billions is almost certainly seconds, a value near ten trillion is milliseconds. Anything in between is a strong hint that the table mixes units, which is a problem worth fixing before any reporting work proceeds. Most engines also let you compare against a known timestamp cast through the conversion function — SELECT ts FROM events WHERE FROM_UNIXTIME(ts) = '2023-11-14 22:13:20' — to confirm whether a specific row resolves to the date you expect.

Practical Tips and Debugging Habits

A few small habits save a lot of time when timestamps and SQL collide. Keep the epoch anchor in a comment at the top of every migration so the next developer sees the assumption on sight. Treat the FROM_UNIXTIME() result as UTC by default and only convert to a local zone at the presentation layer, since the database has no business knowing which city the analyst sits in. When writing WHERE clauses against epoch columns, always pass the bound through UNIX_TIMESTAMP() on the right side, never against a magic integer that someone hand-calculated from a calendar.

Unix-style epoch values also leak into adjacent formats you may eventually meet in SQL. ULIDs, for instance, embed a 48-bit millisecond timestamp at the front of the identifier, so reading the prefix gives you a usable Unix timestamp in milliseconds; if you start seeing those in your warehouse, the Decode a ULID Timestamp guide walks through the bit-level extraction. For the underlying definition of Unix time and the formal rules around the 32-bit overflow, the Unix time entry on Wikipedia is the most reliable starting point.

Finally, remember that the Year 2038 problem is not a theoretical curiosity: it is a real bug waiting in any signed 32-bit INTEGER column whose value crosses 2147483647 on 19 January 2038 at 03:14:07 UTC. Modern databases default to 64-bit types and are unaffected, but legacy schemas, embedded devices, and some older ORM mappings still store timestamps in 32-bit fields. Spotting that column type before it ships to production is one of the cheapest wins available, and a Unix Timestamp Converter gives you a way to read any raw value in plain English long before the rollover date arrives.