What Is a Unix Timestamp? Converting Epoch Time to Dates

A Unix timestamp (also called epoch time or POSIX time) is the number of seconds since 00:00:00 UTC on January 1, 1970. That moment is called the Unix epoch. For example, the timestamp 1000000000 means 01:46:40 UTC on September 9, 2001. Leap seconds aren't counted, so every day in Unix time is exactly 86,400 seconds long.

Timestamps are popular because a single integer is compact, sorts correctly, and means the same instant on every computer in the world. You'll find them in log files, API responses, databases, JSON web tokens, and file metadata. The catch is that people can't read them, so sooner or later you need to convert one.

This guide covers what the number actually measures and how to tell seconds from milliseconds. It also shows how to convert a timestamp by hand, which one-liners work in JavaScript, Python, and SQL, and why some systems will break in January 2038.

What a Unix timestamp actually counts

A Unix timestamp counts seconds from a fixed starting point. Zero is the epoch itself. Positive numbers are later, and negative numbers are earlier: -1 is 23:59:59 UTC on December 31, 1969.

Most systems store the value as an integer. Some store it as a decimal number when they need fractions of a second. Python's time.time(), for instance, returns something like 1700000000.123456.

Why leap seconds are left out

UTC occasionally adds a leap second to keep clock time in step with the Earth's slightly irregular rotation. Since 1972, 27 leap seconds have been added. The most recent one came at the end of 2016.

Unix time ignores them. It assumes every day has exactly 86,400 seconds, which keeps the math simple: divide by 86,400 and you get whole days. The trade-off is that Unix time isn't a perfectly accurate count of elapsed seconds. When a leap second happens, systems either repeat a timestamp or "smear" the extra second over several hours. For everyday date conversion you can ignore this. It only matters for precision timing.

In 2022, international timekeeping bodies decided to stop adding leap seconds by 2035, so this quirk should eventually stop growing.

Seconds vs. milliseconds: 10 digits or 13

The most common mistake with timestamps is mixing up units. Unix tools, Python, PHP, and many databases use seconds. JavaScript's Date and Java's System.currentTimeMillis() use milliseconds. Some logging and tracing systems use microseconds or nanoseconds.

You can usually tell the unit from the number of digits in a present-day value:

Every row above is 22:13:20 UTC on November 14, 2023. A seconds timestamp has had 10 digits since September 2001 and will keep 10 digits until November 2286. That makes the digit count a reliable test for current dates. It doesn't work for older values: a seconds timestamp from 1990 has only 9 digits.

When you get the unit wrong, the result is obvious once you look for it:

• If you read milliseconds as seconds, the date lands tens of thousands of years in the future.
• If you read seconds as milliseconds, the date lands in January 1970, a few weeks after the epoch.

If a converted date shows up in 1970 or in the far future, check the unit first. To switch between them, multiply or divide by 1,000 for each step in the table.

Notable Unix timestamps

These values are useful for testing a converter or sanity-checking your own code:

How to convert a Unix timestamp to a date by hand

You'll rarely need to do this manually, but walking through it once shows exactly what a converter does. Here's the method applied to 1000000000.

1. Split into days and leftover seconds. Divide by 86,400 (seconds per day). 1,000,000,000 ÷ 86,400 = 11,574 whole days, because 11,574 × 86,400 = 999,993,600. The leftover is 1,000,000,000 − 999,993,600 = 6,400 seconds.
2. Turn the leftover seconds into a time of day. 6,400 ÷ 3,600 = 1 hour, with 2,800 seconds remaining. 2,800 ÷ 60 = 46 minutes, with 40 seconds remaining. The time is 01:46:40.
3. Count whole years from 1970. The years 1970 through 2000 are 31 years. Eight of them are leap years (1972, 1976, 1980, 1984, 1988, 1992, 1996, 2000). That's 31 × 365 + 8 = 11,315 + 8 = 11,323 days, so January 1, 2001 is day 11,323.
4. Find the day within the year. 11,574 − 11,323 = 251 days into 2001, counting January 1 as day 0.
5. Subtract months. 2001 isn't a leap year. Subtract January (31) to get 220, February (28) to get 192, March (31) to get 161, April (30) to get 131, May (31) to get 100, June (30) to get 70, July (31) to get 39, and August (31) to get 8. That leaves 8 days into September. Since September 1 is day 0, that's September 9.
6. Combine. The result is 2001-09-09 01:46:40 UTC, which matches the table.

Converting a date to a timestamp

To go the other way, reverse the steps. Count the days from January 1, 1970 to your date, multiply by 86,400, then add the hours × 3,600, minutes × 60, and seconds. First convert the date and time to UTC. If you skip that step, your answer will be off by your time zone offset.

Why a Unix timestamp has no time zone

A timestamp names one instant, not a wall-clock reading. When the timestamp 1000000000 happened, it happened at the same moment everywhere. Time zones only come into play when you display that instant as a local date and time:

Note that New York shows a different calendar date. This is the source of many bugs. A converter that silently uses your computer's local time zone will show different results on different machines, even though the timestamp is the same.

Daylight saving time doesn't affect the timestamp either. The number keeps counting steadily, and only the local display jumps forward or back.

When you write a converted date down, include the offset. ISO 8601 formats such as 2001-09-09T01:46:40Z (the Z means UTC) or 2001-09-08T21:46:40-04:00 can't be misread. The internet profile of this format is defined in RFC 3339.

How to convert epoch time in JavaScript, Python, and SQL

These one-liners are long-standing and widely used. Pay attention to which ones return UTC and which use a local or session time zone.

JavaScript

JavaScript works in milliseconds, so multiply or divide by 1,000 at the boundaries.

// Current Unix timestamp in seconds
Math.floor(Date.now() / 1000)

// Timestamp (seconds) to an ISO string in UTC
new Date(1000000000 * 1000).toISOString()   // "2001-09-09T01:46:40.000Z"

// ISO string to timestamp (seconds)
Date.parse("2001-09-09T01:46:40Z") / 1000   // 1000000000

Watch out for strings without an offset. JavaScript treats a date-time string like "2001-09-09T01:46:40" as local time, but treats a date-only string like "2001-09-09" as UTC. Always include Z or an explicit offset. MDN's documentation for Date.now() covers the millisecond behavior.

Python

import time
from datetime import datetime, timezone

now = int(time.time())   # current timestamp, seconds

dt = datetime.fromtimestamp(1000000000, tz=timezone.utc)
print(dt.isoformat())    # 2001-09-09T01:46:40+00:00
print(dt.timestamp())    # 1000000000.0

Always pass tz=timezone.utc, or another time zone, explicitly. Without it, fromtimestamp() returns a "naive" local time, and .timestamp() on a naive datetime assumes it's local time. The older datetime.utcfromtimestamp() is deprecated as of Python 3.12.

SQL

Each database has its own functions:

-- PostgreSQL (displays in the session time zone)
SELECT to_timestamp(1000000000);
SELECT EXTRACT(EPOCH FROM now());

-- MySQL (FROM_UNIXTIME uses the session time zone)
SELECT FROM_UNIXTIME(1000000000);
SELECT UNIX_TIMESTAMP();

-- SQLite (returns UTC text)
SELECT datetime(1000000000, 'unixepoch');   -- 2001-09-09 01:46:40
SELECT strftime('%s', 'now');

-- SQL Server
SELECT DATEADD(second, 1000000000, '1970-01-01');

In PostgreSQL and MySQL, the displayed result depends on the session's time zone setting. If you need UTC output, set the session time zone to UTC or convert explicitly. In SQL Server, DATEADD takes an int number of seconds, so values past 2147483647 won't fit. Those need a different approach, such as adding days and seconds separately.

Command line

On Linux with GNU coreutils, date -u -d @1000000000 prints the date in UTC. On macOS and other BSD systems, the equivalent is date -u -r 1000000000. Running date +%s prints the current timestamp on both.

The year 2038 problem

Many older systems store Unix time as a signed 32-bit integer, which can't hold a value larger than 2,147,483,647. That limit falls at 03:14:07 UTC on January 19, 2038.

One second later, the counter overflows and wraps around to −2,147,483,648, which those systems read as 20:45:52 UTC on December 13, 1901. Depending on the software, that means a crash, wrong dates, expired certificates, or scheduled jobs that never run.

Who is still at risk

Modern 64-bit operating systems and languages generally use 64-bit time values, which won't overflow for billions of years. The remaining risk is in places that are easy to overlook:

• Embedded devices and older 32-bit systems that are expected to run for decades.
• File formats and network protocols with a fixed 32-bit time field.
• Database columns declared as 32-bit INT, and types with a built-in limit. MySQL's TIMESTAMP column type, for example, has a documented upper limit of 2038-01-19 03:14:07 UTC.
• Code that casts a timestamp to a 32-bit integer somewhere along the way.

You can test for the problem today. Feed a system a date after January 19, 2038, such as an expiry date 15 years out, and check whether it survives the round trip.

Unsigned 32-bit storage pushes the limit out to February 2106, but it gives up negative values, so it can't represent dates before 1970.

Quick reference for working with Unix timestamps

• Definition: seconds since 1970-01-01 00:00:00 UTC, with no leap seconds, so a day is always 86,400 seconds.
• Unit check: for present-day values, 10 digits means seconds, 13 means milliseconds, 16 means microseconds, and 19 means nanoseconds.
• Wrong-unit symptoms: a date in 1970 means you probably used seconds as milliseconds. A date far in the future means you probably used milliseconds as seconds.
• Time zones: the timestamp has none. Choose one deliberately when displaying it, and write the offset in the output.
• Sanity checks: 0 is the epoch, 1000000000 is 2001-09-09 01:46:40 UTC, and 2147483647 is 2038-01-19 03:14:07 UTC.
• Storage: use 64-bit integers for seconds or milliseconds. Never truncate to 32 bits.
• Tokens and APIs: the exp and iat fields in JSON web tokens are in seconds. Those token payloads are Base64url-encoded, which is explained in What Is Base64?

Frequently Asked Questions

Is epoch time the same as Unix time?

In everyday use, yes. "Epoch time," "Unix time," and "POSIX time" all usually mean seconds since 1970-01-01 00:00:00 UTC. Strictly speaking, an epoch is just any reference point, and some systems use different ones. Excel and GPS, for example, count from other dates, so check the documentation when a value comes from an unusual source.

Why does Unix time start in 1970?

The early Unix developers chose January 1, 1970 as a convenient round starting point near the time the system was being built. It has no astronomical or historical meaning. Once software and file formats depended on it, the date became a permanent convention.

How do I convert a Unix timestamp in Excel or Google Sheets?

Spreadsheets store dates as day counts, so divide the timestamp by 86,400 and add the epoch date: =A1/86400 + DATE(1970,1,1). Then format the cell as a date and time. The result is in UTC. To shift it to a local zone, add or subtract the offset as a fraction of a day, such as -4/24 for UTC-4. If the value has 13 digits, divide by 86,400,000 instead.

Can a Unix timestamp be negative?

Yes. Negative values are instants before the 1970 epoch. For example, -86400 is 1969-12-31 00:00:00 UTC. Most modern languages handle them correctly, but some older libraries, file formats, and unsigned storage types reject them, so test before relying on them for historical dates.

Does a Unix timestamp change when daylight saving time starts or ends?

No. The timestamp keeps increasing by one every second, regardless of daylight saving changes. Only the local clock display jumps forward or back. Storing timestamps avoids the duplicated or missing local times that happen around those transitions.