Unix Timestamp (Epoch) Deep Dive: Storing, Converting and Displaying Dates

The **Unix epoch** is the moment `1970-01-01 00:00:00 UTC` — the reference point from which Unix timestamps are counted. A Unix timestamp (often called epoch time) is simply the number of seconds (or milliseconds, microseconds, or nanoseconds, depending on precision) that have elapsed since that moment.

This representation emerged from the early Unix operating system in the late 1960s and early 1970s. The choice of January 1, 1970 was somewhat arbitrary — it was a convenient, recent date that fit in the integer types available on the PDP-11 hardware Unix was originally developed for. What matters today is that this format has become a **universal standard** for storing and transmitting point-in-time values across virtually every computing platform.

Why is epoch time so widely used? Several reasons: 1. **Timezone-agnostic:** An epoch timestamp is always UTC. No timezone ambiguity — `1748447523` means exactly the same moment regardless of where the server, the user, or the database resides. 2. **Arithmetic-friendly:** Finding the difference between two times is a simple subtraction. Adding 3600 to a timestamp advances it by exactly one hour (with the caveat that leap seconds exist, discussed later). 3. **Storage-efficient:** A 64-bit integer stores any date from billions of years in the past to billions of years in the future. Compared to a formatted date string like `2026-06-15T14:32:00Z` (20 bytes), a 64-bit integer is 8 bytes — 2.5x smaller at the byte level, and far faster to index and compare in databases. 4. **Language-portable:** Every programming language, database engine, and operating system understands Unix timestamps natively.

The main cost of epoch time is human readability — `1748447523` is opaque to a developer reading a log file or debugging a database row. Good tooling (including online converters) mitigates this, but it remains the primary ergonomic tradeoff.

One of the most common bugs involving Unix timestamps is **precision mismatch** — treating a millisecond timestamp as a second timestamp or vice versa. This produces errors of a factor of 1,000, which typically manifests as dates in the year 51000 or in 1970 depending on the direction of the mistake.

The four common precision levels: ``` Seconds (s): 1748447523 10 digits (current era) Milliseconds (ms): 1748447523000 13 digits Microseconds (μs): 1748447523000000 16 digits Nanoseconds (ns): 1748447523000000000 19 digits ```

A quick heuristic for identifying precision from digit count in the current era (2020s–2030s): - **10 digits** → seconds - **13 digits** → milliseconds - **16 digits** → microseconds - **19 digits** → nanoseconds

Different systems use different precision by convention: - **Unix system calls** (`time()` in C): seconds - **JavaScript's `Date.now()`**: milliseconds - **Python's `time.time()`**: float seconds (effectively microseconds) - **PostgreSQL's `EXTRACT(EPOCH FROM ...)`**: float seconds - **Java's `System.currentTimeMillis()`**: milliseconds - **Go's `time.Now().UnixNano()`**: nanoseconds - **Snowflake IDs, Twitter/X IDs**: encode milliseconds + sequence in a 64-bit integer

When storing epoch values in a database, **document the precision in the column name**: ```sql created_at_ms BIGINT NOT NULL, -- milliseconds updated_at_s INTEGER NOT NULL, -- seconds (pre-2038 only) event_ns BIGINT NOT NULL -- nanoseconds ```

Or use a comment in the schema migration: ```sql -- Stores Unix timestamp in MILLISECONDS (JavaScript-compatible) ALTER TABLE events ADD COLUMN created_at_ms BIGINT NOT NULL; ```

This documentation prevents the next engineer from treating your millisecond column as seconds and pushing dates 33 years into the future.

Practical epoch conversion code is something every developer reaches for regularly. Here are the canonical patterns for the most common environments.

**Python:** ```python from datetime import datetime, timezone import time

# Current epoch (seconds, float) t = time.time() # 1748447523.456789

# Current epoch (integer seconds) t_int = int(time.time()) # 1748447523

# Epoch to UTC datetime utc_dt = datetime.fromtimestamp(1748447523, tz=timezone.utc) print(utc_dt.isoformat()) # 2026-06-15T14:32:03+00:00

# UTC datetime to epoch from datetime import timezone dt = datetime(2026, 6, 15, 14, 32, 3, tzinfo=timezone.utc) epoch = int(dt.timestamp()) # 1748447523

# Milliseconds epoch_ms = int(time.time() * 1000) # 1748447523456 ```

**JavaScript:** ```javascript // Current epoch in milliseconds (JS native) const epochMs = Date.now(); // 1748447523456

// Current epoch in seconds const epochS = Math.floor(Date.now() / 1000); // 1748447523

// Epoch ms to Date const d = new Date(1748447523000); console.log(d.toISOString()); // 2026-06-15T14:32:03.000Z

// Date to epoch ms const epoch = new Date('2026-06-15T14:32:03Z').getTime(); // 1748447523000

// With Temporal (2026 standard) const instant = Temporal.Instant.fromEpochMilliseconds(1748447523000); console.log(instant.toString()); // 2026-06-15T14:32:03Z ```

**SQL (PostgreSQL):** ```sql -- Current epoch as integer seconds SELECT EXTRACT(EPOCH FROM NOW())::BIGINT; -- Returns: 1748447523

-- Epoch seconds to TIMESTAMPTZ SELECT TO_TIMESTAMP(1748447523); -- Returns: 2026-06-15 14:32:03+00

-- TIMESTAMPTZ to epoch seconds SELECT EXTRACT(EPOCH FROM created_at)::BIGINT FROM events;

-- Epoch milliseconds (JavaScript-compatible) SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT; ```

**SQL (MySQL):** ```sql SELECT UNIX_TIMESTAMP(); -- seconds SELECT FROM_UNIXTIME(1748447523); -- to datetime SELECT UNIX_TIMESTAMP('2026-06-15 14:32:03'); -- to epoch ```

The **Year 2038 problem** (Y2K38) is the epoch equivalent of the Y2K bug: on **2038-01-19 03:14:07 UTC**, a 32-bit signed integer storing Unix timestamps will overflow. At that moment, the counter reaches `2,147,483,647` (the maximum value of a 32-bit signed integer), and the next second it wraps to `-2,147,483,648`, which represents `1901-12-13 20:45:52 UTC`.

Systems affected by Y2K38 as of 2026: - **Legacy embedded systems** with 32-bit `time_t` (industrial controllers, medical devices, SCADA systems) - **MySQL `TIMESTAMP` type** — stored as a 32-bit integer, max value `2038-01-19 03:14:07`. Any row insertion or update that sets a `TIMESTAMP` column beyond this date will either error or wrap. Use `DATETIME` in MySQL for dates beyond 2038. - **Older PHP code** on 32-bit builds where `time()` returns 32-bit integers - **Some C libraries on 32-bit ARM** embedded boards that have not updated `time_t` to 64-bit - **Legacy database schemas** that store epoch timestamps in `INT` columns (instead of `BIGINT`)

**The fix** is straightforward for software: 1. Use **64-bit integers** for all epoch storage. A signed 64-bit integer can represent dates up to approximately `292 billion years` in the future — well beyond any practical concern. 2. In MySQL: replace `TIMESTAMP` columns with `DATETIME` for future dates; use `BIGINT` for raw epoch columns. 3. In C: ensure `time_t` is 64-bit on your platform (`sizeof(time_t) == 8`). On Linux kernels 5.6+ (2020), 32-bit ARM has been updated to use 64-bit time. 4. In database schemas: change `INT` epoch columns to `BIGINT`.

The 2038 deadline is close enough that any system expected to still be running in 2038 should be audited and fixed now. Migration from `INT` to `BIGINT` in a large production database requires careful planning — an `ALTER TABLE` on a billion-row table is a significant operation that needs online schema change tooling.

Unix timestamps have an intentional design quirk: they **ignore leap seconds**. This means that Unix time is not perfectly linear — every time a leap second is inserted (or theoretically removed), Unix clocks either repeat or skip a value.

Leap seconds are added to keep UTC synchronized with the Earth's irregular rotation. As of 2026, 27 leap seconds have been inserted since 1972. The Unix timestamp definition pretends these did not happen: a Unix day is always exactly 86,400 seconds, even on days when 86,401 seconds elapsed in real astronomical time.

**Practical implications:**

1. **High-precision time measurement:** If you are measuring intervals that span a leap second using Unix timestamps, you will be off by one second. For most applications, this is irrelevant. For GPS timing, financial market settlement systems, and telecommunications, it matters significantly.

2. **Leap second tables:** Applications that need to know how many seconds have actually elapsed between two UTC instants must consult a leap second table. The IETF maintains this table and it is distributed via `tzdata`. Python's `datetime` module and the `Temporal` API do not account for leap seconds by design; POSIX specifies this behavior explicitly.

3. **Leap second smearing:** Major cloud providers (Google Cloud, AWS) use **leap second smearing** — distributing the extra second across a 24-hour window by slightly slowing their NTP clocks. This means their servers never actually experience a repeated second, at the cost of a very slight clock inaccuracy across the smear window. If your system uses cloud VM clocks, you are already using smeared time.

4. **The ITU proposal:** The ITU has been debating eliminating leap seconds since 2015. In November 2022, the World Radiocommunication Conference passed a resolution to abolish leap seconds by 2035, with tolerance accumulated until 2135. If this proceeds, Unix timestamp simplicity will align with reality by the mid-2130s.

For everyday applications, the practical guidance is: use epoch timestamps for storage and arithmetic, use a proper timezone-aware library for display, and accept that sub-second leap-second accuracy is a specialized concern you likely do not need.

The final step in working with epoch timestamps is displaying them in a human-readable format appropriate to the user's locale and context. This involves three decisions: which timezone to display in, which date format to use, and how to handle relative time ("3 hours ago" vs an absolute datetime).

**Absolute date display — locale-aware formatting:** ```javascript const epochMs = 1748447523000; const d = new Date(epochMs);

// US English, user's local timezone const formatter = new Intl.DateTimeFormat('en-US', { dateStyle: 'medium', timeStyle: 'short', timeZone: 'America/New_York' // or user's IANA timezone }); console.log(formatter.format(d)); // Output: Jun 15, 2026, 10:32 AM

// ISO 8601 (for APIs and logs — unambiguous) console.log(d.toISOString()); // 2026-06-15T14:32:03.000Z ```

**Relative time — "N minutes ago":** Relative time is intuitive for recent events but breaks down for distant past or future dates. A common strategy: - `< 1 minute ago`: "just now" - `1–60 minutes`: "N minutes ago" - `1–24 hours`: "N hours ago" - `1–7 days`: "N days ago" - `> 7 days`: absolute date

The `Intl.RelativeTimeFormat` API handles locale-appropriate relative formatting: ```javascript const rtf = new Intl.RelativeTimeFormat('en', { numeric: 'auto' });

function relativeTime(epochMs) { const diffS = (epochMs - Date.now()) / 1000; if (Math.abs(diffS) < 60) return rtf.format(Math.round(diffS), 'second'); if (Math.abs(diffS) < 3600) return rtf.format(Math.round(diffS / 60), 'minute'); if (Math.abs(diffS) < 86400) return rtf.format(Math.round(diffS / 3600), 'hour'); return rtf.format(Math.round(diffS / 86400), 'day'); }

console.log(relativeTime(Date.now() - 125000)); // "2 minutes ago" ```

**Log formatting:** In server logs, always use ISO 8601 with UTC explicitly (`2026-06-15T14:32:03.456Z`), never local time. This makes log correlation across servers in different regions trivial — every timestamp is directly comparable without mental timezone math.

Choosing how to store epoch timestamps in a database involves tradeoffs between precision, portability, query ergonomics, and storage efficiency. Here is the full landscape of options with their tradeoffs.

**Option 1: Database-native timestamp type (recommended)** ```sql -- PostgreSQL created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- MySQL created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) -- (3 = millisecond precision) ``` Pros: Human-readable in DB admin tools, supports native date functions (date arithmetic, truncation, range queries), indexed efficiently with `BTREE`. Cons: Less portable across DB engines; MySQL `TIMESTAMP` has Y2K38 limitation.

**Option 2: BIGINT epoch milliseconds (JavaScript-compatible)** ```sql created_at_ms BIGINT NOT NULL -- Store: Date.now() in JS, int(time.time() * 1000) in Python ``` Pros: Direct compatibility with JavaScript `Date` objects, no type conversion in API layer, unambiguous precision, Y2K38-safe, extreme date range. Cons: Opaque in admin tools, requires manual date arithmetic in SQL, cannot use native date functions without conversion.

**Option 3: INTEGER epoch seconds (legacy / small tables)** ```sql created_at_s INTEGER NOT NULL ``` Pros: Compact (4 bytes), simple. Cons: Y2K38 problem, second precision only, losing favor as 4-byte cost difference is irrelevant at scale.

**Indexing considerations:** For any column used in range queries by time (`WHERE created_at BETWEEN X AND Y`, `ORDER BY created_at DESC LIMIT N`), a `BTREE` index is correct. Epoch integers and timestamps index identically well — the cardinality and monotonic-insert pattern are the same.

For **time-series data** at scale (millions of rows per day), consider: - **PostgreSQL table partitioning** by time range (`PARTITION BY RANGE (created_at)`) — allows dropping old partitions instantly rather than expensive `DELETE` - **TimescaleDB** extension for automatic time-series compression and time-bucket queries - **ClickHouse** for append-only analytical workloads with epoch-based time columns

Whatever storage option you choose, document it explicitly in your schema migrations and API documentation so every consumer of the data knows what precision and base they are receiving.