Complete Guide to Unix Timestamps and Epoch Time
Understanding Unix timestamps
A Unix timestamp is the number of seconds elapsed since the Unix epoch: January 1, 1970 00:00:00 UTC. It is a single integer — timezone-agnostic, locale-agnostic, and unambiguous. A timestamp of 1700000000 means exactly 1,700,000,000 seconds after the epoch, which is November 14, 2023 at 22:13:20 UTC regardless of where you are in the world. This property makes Unix timestamps ideal for storing, transmitting, and comparing times across distributed systems. The downside is human unreadability — 1700000000 doesn't communicate 'November 2023' to a reader without conversion.
Seconds vs milliseconds: choosing the right precision
Two conventions exist for Unix timestamps: seconds (10 digits for current times) and milliseconds (13 digits). The original Unix convention uses seconds. JavaScript adopted milliseconds for Date.now() because sub-second precision matters for web performance measurement and animation. Most server-side timestamps (database columns, API fields, log entries) use seconds. A 10-digit timestamp like 1700000000 is seconds; a 13-digit timestamp like 1700000000000 is milliseconds. When integrating with an API, check its documentation — sending milliseconds to a seconds field or vice versa is a common source of bugs that produces dates 1000 years in the future or in 1970.
Timezones and Unix timestamps
Unix timestamps are always UTC — they have no timezone. The conversion to local time happens at display time, not storage time. When you see 1700000000 as '2023-11-14 22:13:20 UTC', that UTC is describing the display, not a property of the timestamp itself. Local time display depends on the viewing system's timezone. This is why Unix timestamps are the right format for storing and transmitting times: store as Unix, display as local. Problems arise when people store 'local time as a Unix timestamp' — a naive timestamp without timezone conversion can record a local time that is technically wrong in UTC.
Working with timestamps in databases
PostgreSQL: Use TIMESTAMPTZ (timestamp with time zone) which stores UTC internally and converts to the session timezone on display. EXTRACT(EPOCH FROM timestamp) converts to Unix seconds. TO_TIMESTAMP(unix) converts back. MySQL: UNIX_TIMESTAMP() returns the current Unix timestamp. FROM_UNIXTIME(unix) converts to DATETIME. Store with DATETIME or TIMESTAMP type — TIMESTAMP has Y2038 issues in some versions. SQLite: stores all dates as ISO text, Unix integers, or Julian day numbers. Use strftime('%s', 'now') for current Unix timestamp and datetime(unix, 'unixepoch') to convert. MongoDB: uses milliseconds internally via the Date BSON type. ISODate('2023-11-14') is stored as milliseconds since epoch.
Timestamp arithmetic and duration calculation
Because Unix timestamps are plain integers (in seconds), arithmetic is straightforward: to add one day, add 86400 (60 × 60 × 24). To add one week, add 604800. To find the duration between two events, subtract their timestamps and divide by the appropriate unit. This simple arithmetic is why timestamps are preferable to complex datetime objects for duration calculations. Edge cases: adding calendar months or years requires a proper datetime library because months have different lengths. For everything else (hours, days, weeks), raw arithmetic is exact. The offset calculator in this tool uses this arithmetic internally.
Common timestamp formats: ISO 8601, RFC 2822, Unix
ISO 8601 (2023-11-14T22:13:20.000Z) is the international standard and the output of JavaScript's .toISOString(). The trailing Z means UTC. With timezone offset: 2023-11-14T22:13:20+05:30. RFC 2822 (Tue, 14 Nov 2023 22:13:20 +0000) is used in email headers and HTTP Date headers. Unix timestamp (1700000000) is the most compact and timezone-safe. For APIs, prefer ISO 8601 because it is unambiguous, human-readable, and broadly supported by JSON parsers. In database columns, prefer TIMESTAMPTZ (PostgreSQL) or store as a Unix integer for portability.
The Y2K38 problem and 64-bit timestamps
Systems storing timestamps as 32-bit signed integers will overflow on January 19, 2038 at 03:14:07 UTC. The maximum 32-bit signed integer is 2,147,483,647. After that point, the value wraps to −2,147,483,648, which represents December 13, 1901. This is a real issue for embedded systems, older databases, and legacy POSIX code compiled for 32-bit targets. The fix is to use 64-bit integers for timestamps, which extend the valid range to approximately year 292 billion. Modern Linux, macOS, Windows, and most programming languages have already made this transition. Remaining risk areas: MySQL's TIMESTAMP column (limited to 2038), 32-bit ARM devices, and old PHP code using 32-bit time_t.