Unix Timestamps: The Complete Developer Guide

Timestamps seem simple until they aren't. This covers what epoch time actually is, the Y2K38 problem, how to convert correctly in every language, timezone traps, ISO 8601, and how to store dates without shooting yourself in the foot later.

A Unix timestamp is a single integer counting seconds since the "Unix epoch" — midnight on January 1, 1970, UTC. That's it. No timezones, no formatting, no ambiguity. Just a number.

The choice of 1970 as the epoch comes from when Unix was being developed. The creators needed a reference point in the recent past. 1970 was close enough to avoid impractically large numbers for dates they'd actually work with, while being far enough back that most relevant dates would be positive numbers.

Epoch:          0  →  Jan 1, 1970 00:00:00 UTC
Moon landing: -14182940  →  Jul 20, 1969 20:17:40 UTC (negative — before epoch)
Y2K:      946684800  →  Jan 1, 2000 00:00:00 UTC
1 billion:  1000000000  →  Sep 9, 2001 01:46:40 UTC
Today (early 2026): ~1743000000

The elegance of this format is in what it enables:

• Sorting: Later times always have larger numbers — no format parsing needed
• Arithmetic: "One hour from now" is just now + 3600
• Unambiguous storage: No locale, no format string, no timezone in the stored value
• Language-agnostic: An integer is an integer in every programming language

Convert timestamps instantly with our Unix Timestamp Converter.

This is the bug that has silently corrupted date handling in more codebases than I can count. JavaScript's Date object operates in milliseconds. Most other languages and databases operate in seconds. The difference is a factor of 1000, and the resulting bug usually produces dates in 1970 (dividing ms timestamp by 1000 gives a valid-looking second timestamp for ~50 years earlier) or absurdly far in the future (multiplying a seconds timestamp by 1000 again).

Quick detection: if your timestamp has 10 digits, it's seconds. 13 digits, it's milliseconds. 16 digits, microseconds.

function detectTimestampUnit(ts) {
const digits = String(Math.abs(ts)).replace('.', '').length;
if (digits <= 10) return 'seconds';
if (digits <= 13) return 'milliseconds';
return 'microseconds';
}

// Normalize to milliseconds (for JS Date) function toMs(ts) { const unit = detectTimestampUnit(ts); if (unit === 'seconds') return ts * 1000; if (unit === 'microseconds') return Math.floor(ts / 1000); return ts; }

On January 19, 2038, at 03:14:07 UTC, a catastrophic failure will hit every system that stores Unix timestamps as a signed 32-bit integer. The maximum value of a signed 32-bit integer is 2,147,483,647. The next second, it overflows to −2,147,483,648, which corresponds to December 13, 1901.

int32_t max = 2147483647;  // January 19, 2038 03:14:07 UTC

// One second later: int32_t overflow = max + 1; // = -2147483648 // Interpreted as: December 13, 1901 20:45:52 UTC

This isn't theoretical. It's the same class of bug as Y2K — a fixed-width representation running out of space. The difference is that Y2K was a 2-digit year field (trivially fixable in the format), while Y2K38 is baked into the data type used to represent time in countless embedded systems, databases, and applications.

The affected systems:

• Any C code using time_t as a 32-bit integer on 32-bit platforms
• MySQL TIMESTAMP columns (range: 1970–2038)
• Embedded systems with 32-bit processors that haven't been updated
• Old Linux kernels on 32-bit hardware
• Legacy financial and SCADA systems

The fix: Use 64-bit integers. A signed 64-bit integer can represent timestamps up to the year 292,277,026,596 — well past any concern. Most modern systems already use 64-bit internally. On 64-bit Linux, time_t is 64-bit. MySQL's DATETIME column (not TIMESTAMP) has no 2038 limit. PostgreSQL's TIMESTAMP is always 64-bit.

# MySQL: avoid TIMESTAMP for dates beyond 2038

TIMESTAMP: 1970-01-01 to 2038-01-19 (32-bit internally)

DATETIME: 1000-01-01 to 9999-12-31 (64-bit internally)

PostgreSQL: no issue — TIMESTAMP is always 64-bit

Application code: always use 64-bit integers for timestamps

JavaScript: Number is 64-bit float, fine for timestamps up to year 275,760

Python: int is arbitrary precision, no issue

Go: time.Time uses int64 nanoseconds, fine until year 2262

JavaScript

// Current timestamp
Date.now()                              // milliseconds: 1743000000000
Math.floor(Date.now() / 1000)          // seconds: 1743000000

// Millisecond timestamp → Date
new Date(1743000000000)                // Date object
new Date(1743000000000).toISOString() // "2025-03-26T20:00:00.000Z"
// Second timestamp → Date (multiply by 1000!)
new Date(1743000000 * 1000).toISOString()
// Date string → timestamp (seconds)
Math.floor(new Date('2025-03-26T20:00:00Z').getTime() / 1000)
// Specific date → timestamp
const d = new Date(Date.UTC(2025, 2, 26, 20, 0, 0)); // UTC, month is 0-indexed
Math.floor(d.getTime() / 1000)

Python

import time
from datetime import datetime, timezone

Current timestamp (float — fractional seconds)
time.time()          # 1743000000.123456
Current timestamp (integer seconds)
int(time.time())     # 1743000000
Timestamp → UTC datetime (Python 3.2+)
dt = datetime.fromtimestamp(1743000000, tz=timezone.utc)
print(dt.isoformat())  # "2025-03-26T20:00:00+00:00"
Timestamp → local time (be careful!)
datetime.fromtimestamp(1743000000)
datetime → timestamp
dt = datetime(2025, 3, 26, 20, 0, 0, tzinfo=timezone.utc)
int(dt.timestamp())   # 1743000000
String → timestamp
from datetime import datetime
dt = datetime.fromisoformat("2025-03-26T20:00:00+00:00")
int(dt.timestamp())

SQL

-- PostgreSQL
SELECT EXTRACT(EPOCH FROM NOW())::bigint;           -- current timestamp (seconds)
SELECT NOW()::timestamp;                             -- current datetime
SELECT TO_TIMESTAMP(1743000000);                     -- timestamp → timestamptz
SELECT EXTRACT(EPOCH FROM '2025-03-26'::date);      -- date → timestamp

-- MySQL
SELECT UNIX_TIMESTAMP();                             -- current timestamp
SELECT FROM_UNIXTIME(1743000000);                    -- timestamp → datetime
SELECT UNIX_TIMESTAMP('2025-03-26 20:00:00');       -- datetime → timestamp
-- SQLite (stores everything as INTEGER or REAL)
SELECT strftime('%s', 'now');                        -- current timestamp
SELECT datetime(1743000000, 'unixepoch');            -- timestamp → string
SELECT datetime(1743000000, 'unixepoch', 'localtime'); -- in local time
SELECT strftime('%s', '2025-03-26 20:00:00');       -- string → timestamp

bash / command line

# Linux (GNU date)
date +%s                                   # current timestamp
date -d @1743000000                        # timestamp → human readable
date -d @1743000000 +"%Y-%m-%d %H:%M:%S"  # custom format
date -d "2025-03-26 20:00:00 UTC" +%s     # string → timestamp

macOS (BSD date — different syntax)
date +%s                                   # current timestamp
date -r 1743000000                         # timestamp → human readable
date -j -f "%Y-%m-%d %H:%M:%S" "2025-03-26 20:00:00" +%s  # string → timestamp
Windows PowerShell
[int][double]::Parse((Get-Date -UFormat %s))        # current timestamp
[DateTimeOffset]::FromUnixTimeSeconds(1743000000)    # timestamp → DateTimeOffset

Unix timestamps are great for storage and arithmetic. They're terrible for humans to read in logs, APIs, and UIs. ISO 8601 is the standard string format that solves this.

ISO 8601 examples:
2025-03-26                          # Date only
2025-03-26T20:00:00                 # Date + time (no timezone — ambiguous)
2025-03-26T20:00:00Z                # Date + time + UTC (the Z means UTC)
2025-03-26T20:00:00+05:30          # Date + time + offset (IST)
2025-03-26T20:00:00.000Z           # With milliseconds
2025-03-26T20:00:00.000000000Z     # With nanoseconds

Durations (ISO 8601): P1Y2M3DT4H5M6S # 1 year, 2 months, 3 days, 4 hours, 5 min, 6 sec PT30M # 30 minutes P7D # 7 days

Rules that prevent headaches:

• Always include timezone. 2025-03-26T20:00:00 is ambiguous. 2025-03-26T20:00:00Z is not.
• Store in UTC. Display in local time. Never store local time.
• Use Z or +00:00 for UTC — they're equivalent but Z is shorter and more common in APIs.

// JavaScript: always use toISOString() which produces UTC
new Date().toISOString()  // "2025-03-26T20:00:00.000Z"

// Python: use isoformat() with timezone-aware datetime from datetime import datetime, timezone datetime.now(timezone.utc).isoformat() # "2025-03-26T20:00:00.000000+00:00"

For APIs, you often want the Z form:

datetime.now(timezone.utc).strftime('%Y-%m-%dT%H:%M:%SZ')

Timezones are where date handling falls apart. Unix timestamps have no timezone — they're always UTC offsets from the epoch. The confusion arises when converting to/from human-readable formats.

The Fundamental Rule

Store as UTC. Display in local time. Never store local time in a database.

// Wrong: timezone-naive date string stored
INSERT INTO events (name, scheduled_at) VALUES ('Meeting', '2025-03-26 14:00:00');
// Whose 14:00? New York? London? Undefined.

// Right: UTC timestamp stored INSERT INTO events (name, scheduled_at) VALUES ('Meeting', 1743004800); // Always 2025-03-26T20:00:00Z — same instant everywhere

// Or: explicit UTC datetime string INSERT INTO events (name, scheduled_at) VALUES ('Meeting', '2025-03-26T20:00:00Z');

JavaScript Timezone Traps

<code">// Trap 1: new Date() without explicit timezone uses LOCAL time
new Date('2025-03-26')           // Midnight local time — differs by machine
new Date('2025-03-26T00:00:00')  // Same trap

// Safe: always specify UTC
new Date('2025-03-26T00:00:00Z')  // Midnight UTC, same everywhere
new Date(Date.UTC(2025, 2, 26))   // Date.UTC() always UTC
// Trap 2: toLocaleDateString() uses system locale
new Date(1743000000000).toLocaleDateString()  // varies by system locale
// Safe: specify locale and timezone explicitly
new Date(1743000000000).toLocaleDateString('en-US', { timeZone: 'America/New_York' })
new Date(1743000000000).toLocaleDateString('en-GB', { timeZone: 'Europe/London' })

Python Timezone Traps

<code">from datetime import datetime, timezone

Trap: datetime.fromtimestamp() without timezone uses LOCAL time
datetime.fromtimestamp(1743000000)   # Local time — undefined behavior
Safe: always pass timezone
datetime.fromtimestamp(1743000000, tz=timezone.utc)  # Always UTC
Working with other timezones (use zoneinfo — Python 3.9+)
from zoneinfo import ZoneInfo
dt_utc = datetime.fromtimestamp(1743000000, tz=timezone.utc)
dt_nyc = dt_utc.astimezone(ZoneInfo('America/New_York'))
dt_tokyo = dt_utc.astimezone(ZoneInfo('Asia/Tokyo'))

Daylight Saving Time (DST) is the source of some of the most confusing bugs in date handling. The core issue: clocks "spring forward" and "fall back," creating gaps and ambiguities in local time that don't exist in UTC.

The Ambiguous Hour

When clocks fall back (e.g., 2:00 AM becomes 1:00 AM again), local times between 1:00 and 2:00 AM occur twice. There's no way to tell which "1:30 AM" you mean from the local time alone. UTC doesn't have this problem — it flows continuously.

<code">// US Fall DST 2025: clocks fall back at 2:00 AM on November 2
// UTC flows continuously:
1730534400 = 2025-11-02 06:00:00 UTC = 2025-11-02 02:00:00 EDT (clocks back)
1730538000 = 2025-11-02 07:00:00 UTC = 2025-11-02 01:00:00 EST (same local time again)

// If you stored "2025-11-02 01:30:00" (no timezone), which is it? // UTC timestamp tells you exactly which moment.

Don't Add 86400 to Get "Tomorrow"

Adding 86,400 seconds (one day) to a timestamp usually gives you tomorrow, but not always. On the day clocks spring forward, "tomorrow" is 82,800 seconds away. On fallback, it's 90,000 seconds.

<code">// Wrong: naive addition
const tomorrow = Date.now() + 86400 * 1000;  // breaks on DST change days

// Right: use date library for calendar arithmetic // JavaScript (date-fns or Temporal API) import { addDays } from 'date-fns'; const tomorrow = addDays(new Date(), 1);

// Python (add calendar days, not seconds) from datetime import timedelta from zoneinfo import ZoneInfo tz = ZoneInfo('America/New_York') tomorrow = (datetime.now(tz) + timedelta(days=1)).replace(hour=0, minute=0, second=0)

Scheduling at a Specific Local Time

If you're scheduling something at "9:00 AM New York time, every weekday," don't store a fixed UTC offset. Store the intent (9:00 AM in America/New_York timezone) and compute the actual UTC timestamp at schedule time, accounting for current DST offset.

Arithmetic with timestamps is straightforward when you stay in UTC seconds and only convert to local time for display.

<code">// Common constants (seconds)
const MINUTE = 60;
const HOUR   = 3600;
const DAY    = 86400;
const WEEK   = 604800;
// Don't use fixed values for months/years — they vary

const now = Math.floor(Date.now() / 1000);

const oneHourLater = now + HOUR; const yesterday = now - DAY; const nextWeek = now + WEEK; const sevenDaysAgo = now - (7 * DAY);

// Expiry check function isExpired(expiresAt) { return Math.floor(Date.now() / 1000) > expiresAt; }

// Duration between two timestamps function humanDuration(startTs, endTs) { const seconds = endTs - startTs; if (seconds < 60) return ${seconds}s; if (seconds < 3600) return ${Math.floor(seconds / 60)}m; if (seconds < 86400) return ${Math.floor(seconds / 3600)}h; return ${Math.floor(seconds / 86400)}d; }

<code"># Python date math
from datetime import datetime, timedelta, timezone

now = datetime.now(timezone.utc)

Safe calendar arithmetic (handles DST, month lengths)

one_month_later = now.replace(month=now.month % 12 + 1) next_year = now.replace(year=now.year + 1)

Timedelta for fixed durations

one_week = now + timedelta(weeks=1) three_days = now + timedelta(days=3) two_hours = now + timedelta(hours=2)

Difference between timestamps

diff = datetime(2026, 1, 1, tzinfo=timezone.utc) - now print(f"{diff.days} days until 2026")

This is where architectural decisions compound. Get it wrong and you'll be writing migration scripts two years from now.

The Options

Recommendations

PostgreSQL: Use TIMESTAMPTZ (timestamp with time zone). PostgreSQL stores it as UTC internally and converts on retrieval. It's the cleanest option.

<code">-- PostgreSQL: TIMESTAMPTZ is stored as UTC, displayed in session timezone
CREATE TABLE events (
id          BIGSERIAL PRIMARY KEY,
name        TEXT NOT NULL,
created_at  TIMESTAMPTZ DEFAULT NOW(),
scheduled_at TIMESTAMPTZ NOT NULL
);

-- Always store UTC, let PostgreSQL handle timezone display INSERT INTO events (name, scheduled_at) VALUES ('Launch', '2025-06-15T14:00:00Z');

-- Query: events in the last 7 days SELECT * FROM events WHERE created_at > NOW() - INTERVAL '7 days';

MySQL: Use DATETIME (not TIMESTAMP) and enforce UTC in your application. Set time_zone = '+00:00' in your MySQL session to avoid ambiguity.

<code">-- MySQL: DATETIME has no 2038 problem, range 1000–9999
CREATE TABLE events (
id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name         VARCHAR(255) NOT NULL,
created_at   DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
scheduled_at DATETIME NOT NULL
);

-- Always insert UTC values INSERT INTO events (name, scheduled_at) VALUES ('Launch', UTC_TIMESTAMP());

SQLite: Store as INTEGER Unix timestamps. SQLite has no native datetime type — it uses text, real, or integer. Integer is the most efficient and correct for timestamps.

<code">-- SQLite: store as Unix seconds INTEGER
CREATE TABLE events (
id           INTEGER PRIMARY KEY AUTOINCREMENT,
name         TEXT NOT NULL,
created_at   INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
scheduled_at INTEGER NOT NULL
);

-- Query: events scheduled in the next 24 hours SELECT * FROM events WHERE scheduled_at BETWEEN strftime('%s', 'now') AND strftime('%s', 'now') + 86400;