Understanding Unix Timestamps: The Developer's Guide
Learn how the Unix epoch time works, why the Year 2038 problem occurs, and how to write epoch date conversions in Javascript, Python, and SQL.
Time is one of the most complex concepts in computer science. Timezones, daylight savings adjustments, leap seconds, and regional calendars complicate systems database syncing. To solve this, computers rely on a universal standard: Unix Epoch Time.
1. What is Unix Epoch Time?
Unix time (also known as Epoch time or POSIX time) is defined as the total number of seconds elapsed since Thursday, January 1, 1970, at 00:00:00 Coordinated Universal Time (UTC). It increments continuously and is timezone-agnostic.
For example, a Unix timestamp of `1700000000` represents November 14, 2023, at 22:13:20 UTC.
2. Seconds vs Milliseconds
A frequent point of developer bugs is the precision differences between systems:
- Seconds: 10-digit number. Used by Unix/Linux kernels, APIs, PHP, and Python.
- Milliseconds: 13-digit number. Used natively by JavaScript (
Date.now()) and Java.
To convert between them in JavaScript, simply multiply or divide by 1000:
// Seconds to Milliseconds
const date = new Date(1700000000 * 1000);
// Milliseconds to Seconds
const seconds = Math.floor(Date.now() / 1000);
3. Code Reference Cheat-sheet
JavaScript / Node.js
// Get current timestamp in seconds
const timestamp = Math.floor(Date.now() / 1000);
// Parse epoch to local string
const formatted = new Date(timestamp * 1000).toLocaleString();
Python
import time
from datetime import datetime
# Current epoch seconds
seconds = int(time.time())
# Convert to datetime
dt = datetime.fromtimestamp(seconds)
SQL (PostgreSQL)
-- Current epoch timestamp
SELECT EXTRACT(EPOCH FROM NOW());
-- Convert epoch back to database timestamp
SELECT TO_TIMESTAMP(1700000000);
4. The Year 2038 Problem (Y2K38)
Historically, Unix timestamps were stored as 32-bit signed integers. The maximum value that can be represented
by a 32-bit signed integer is 2,147,483,647.
At exactly March 19, 2038, at 03:14:07 UTC, the timestamp will hit this maximum capacity.
One second later, the integer will overflow and wrap around to -2,147,483,648, making computers think it is
December 13, 1901. Modern operating systems and databases are mitigating this by upgrading core datetime variables
to 64-bit integer values, pushing the overflow limit billions of years into the future.