Unix Timestamp & Epoch Time Explained
A Unix timestamp is just a count of seconds since 1970-01-01 00:00:00 UTC — no date, no timezone, no formatting. This covers what that number actually is, how to tell seconds from milliseconds at a glance, why "UTC by definition" is the entire point, what breaks in 2038, and how to get "now" as epoch across five languages.
// what a Unix timestamp actually is
// seconds or milliseconds? count the digits
Unix time is defined in seconds, but a great deal of software — JavaScript most prominently — hands you milliseconds instead. Since a millisecond value is the second value multiplied by 1000, it simply carries three more digits, and that is the fastest reliable way to tell them apart at a glance.
// what happens when the unit is wrong
Seconds read as milliseconds — the value is treated as 1000× smaller than intended, so it lands in January 1970, a few days after the epoch. This is the classic "why is every date showing 1970" bug.
Milliseconds read as seconds — the value is treated as 1000× larger, throwing the date tens of thousands of years into the future. Some systems reject it outright; others cheerfully display an absurd year.
exp/nbf/iat claims are ordinary Unix timestamps in seconds — hand one a milliseconds value by mistake and it either looks expired immediately or effectively never expires, with no error at the point it went wrong.// why "UTC by definition" is the whole point
A Unix timestamp has no timezone attached because it doesn't need one — it's a count of seconds from a single fixed instant, so 1735689600 means the exact same moment everywhere on Earth. The timezone only re-enters the picture when you convert it to a human-readable string for display. This is precisely why storing timestamps as epoch (or any UTC value) and converting to local time only at the display layer is the correct default — the alternative, storing "local wall-clock time," runs straight into a problem epoch time was designed to avoid entirely.
2:30 AM never happens that day. During "fall back," 1:00–1:59 AM occurs twice — a stored local timestamp of 1:30 AM doesn't say which occurrence it means. Neither problem can happen to a Unix timestamp, because it isn't wall-clock time — it's a single, unambiguous instant. A scheduled cron job stored as local time can silently fire twice, not at all, or at the wrong hour on transition days; the same job stored as epoch/UTC never has this failure mode.// getting "now" as epoch, by language
Date.now() — millisecondsDateTimeOffset.UtcNow.ToUnixTimeSeconds() / ToUnixTimeMilliseconds()time.time() — float seconds, sub-second precision includedEXTRACT(EPOCH FROM NOW()) — seconds, with a fractional partUNIX_TIMESTAMP() — secondsDATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) — no built-in function, see belowdate +%s — seconds// SQL Server: DATETIME2 to epoch and back
Unlike MySQL's UNIX_TIMESTAMP()/FROM_UNIXTIME(), T-SQL has no built-in epoch functions at all — the standard pattern is DATEDIFF and DATEADD measured against the epoch literal directly, and it has a sharp edge at millisecond precision that's easy to hit without knowing it's there. (If it's the connection itself giving you trouble rather than this conversion, that's a separate guide.)
DATEDIFF(SECOND, '1970-01-01', @MyDate)DATEDIFF_BIG(MILLISECOND, '1970-01-01', @MyDate) — not DATEDIFF, see belowDATEADD(SECOND, @UnixSeconds, '1970-01-01')SELECT DATEDIFF(MILLISECOND, '1970-01-01', GETUTCDATE());
Msg 535, Level 16, State 0 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.
DATEDIFF returns a plain 32-bit int. At millisecond precision that overflows once the two dates are more than 24 days, 20 hours, 31 minutes, 23.647 seconds apart -- and any date compared against 1970 is over 50 years away, so this fails every time, not just on old data.
SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', GETUTCDATE()); -- DATEDIFF_BIG (SQL Server 2016+) returns bigint instead of int -- same syntax, no overflow
'1970-01-01' as a literal is an unspecified/naive datetime, not explicitly UTC. Compare it against GETUTCDATE(), not GETDATE() — using local time here silently shifts every result by your server's UTC offset, which is exactly the "UTC by definition" point from the top of this guide, just showing up inside a query instead of an API response.DATEDIFF_BIG(MILLISECOND, ...)enable JavaScript to compute this liveDATEDIFF(MILLISECOND, ...) here—DATEADD(SECOND, ...) round-trip—// leap seconds: ignored by design, not by accident
Real UTC occasionally inserts a leap second to stay aligned with Earth's slightly irregular rotation. POSIX time explicitly does not count them — the standard defines every day as exactly 86,400 seconds, full stop. When a leap second is inserted in reality, Unix time either repeats a second or is adjusted by the OS ("smeared" across the surrounding hours) depending on the system — either way, the raw count quietly diverges from true elapsed seconds by design, not by bug. 27 leap seconds have accumulated since 1972, and the timeline for ending them has moved up: the 28th General Conference on Weights and Measures votes on a resolution in October 2026 to stop inserting them from May 20, 2027 — sooner than the 2035 deadline originally agreed in 2022, driven by Earth's rotation now making a never-before-implemented negative leap second a real possibility if it waits that long.
// the year 2038 problem
A signed 32-bit integer tops out at 2147483647, which as a Unix timestamp is 2038-01-19 03:14:07 UTC. One second later the value overflows, and on a system that still stores time that way it wraps to a large negative number — 1901-12-13. That is the same class of bug as Y2K, with the same fix: use a wider type.
DateTimeOffset), Java, Python, and JavaScript all use 64-bit or floating-point representations that push the limit far past any practical horizon. The remaining exposure is in specific places: 32-bit embedded systems, older C code using a 32-bit time_t, and database columns explicitly typed as a 4-byte integer — MySQL's TIMESTAMP column type being the one most likely to appear in an ordinary web stack.// epoch vs. ISO 8601 — different tools for different jobs
ISO 8601's field order (year → month → day → hour...) means lexicographic string sorting matches chronological order — a rare and genuinely useful property, which is why log files and filenames often use it. Epoch numbers sort correctly too, but only when compared as actual numbers; naively comparing them as zero-padded strings of different lengths breaks the same way any numeric string comparison does. Pick epoch for compact storage and arithmetic (durations, comparisons), ISO 8601 for anything a human will read directly — logs, API responses meant for debugging, config files. This same sortability is why UUIDv7 embeds an epoch millisecond timestamp directly in the identifier — see UUID/GUID Versions Explained for why that matters for database index performance.
// frequently asked questions
Is a Unix timestamp in seconds or milliseconds?
Count the digits. Ten digits (like 1788108484) is seconds; thirteen digits is milliseconds — the millisecond value is just the second value with three more digits on the end. This holds for every timestamp between 2001 and 2286. JavaScript's Date.now() is the one common source that returns milliseconds by default; Python, PostgreSQL, MySQL, and the shell all return seconds.
Why is a Unix timestamp always UTC?
Because it's a count of seconds from one fixed instant, not wall-clock time — 1735689600 means the exact same moment everywhere on Earth. A timezone only enters the picture when that number gets converted to a human-readable string for display. Storing local time instead is ambiguous twice a year around DST transitions; epoch/UTC never has that problem.
What is the Year 2038 problem?
A signed 32-bit integer maxes out at 2147483647, which as a Unix timestamp is 2038-01-19 03:14:07 UTC. One second later it overflows and wraps to a large negative number — 1901-12-13 — on any system still storing time that way. 64-bit platforms and current .NET, Java, Python, and JavaScript are unaffected; the remaining exposure is 32-bit embedded systems and database columns explicitly typed as a 4-byte integer.
How do I get the current Unix timestamp in SQL Server?
T-SQL has no built-in epoch function, unlike MySQL's UNIX_TIMESTAMP(). Use DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) for seconds. For milliseconds, use DATEDIFF_BIG, not DATEDIFF — the plain version returns a 32-bit int that overflows for any date compared against 1970. Compare against GETUTCDATE(), not GETDATE(), or the result silently shifts by your server's local UTC offset.
Do leap seconds affect Unix time?
No — POSIX time explicitly ignores them, defining every day as exactly 86,400 seconds. When a real leap second is inserted, Unix time either repeats a second or gets "smeared" by the OS, depending on the system, but this is by design, not a bug, and it essentially never matters for ordinary application code.
// try it yourself
Convert a Unix timestamp (seconds or milliseconds, auto-detected) to a readable date in both UTC and your local timezone, or go the other way, with the Unix Timestamp / Epoch Converter — including a live clock showing the current epoch value as it ticks.
Migrating to .NET 10? Microsoft.Data.Sqlite silently changed its UTC-vs-local assumption in that release — same code, different timestamp, no error.
A Unix timestamp is a single number: the count of seconds elapsed since 1970-01-01 00:00:00 UTC. That instant is "the epoch," and it is the only thing the number is measured against — there is no date, no timezone, and no formatting stored anywhere in the value.
0is the epoch itself.1000000000is 2001-09-09. Ten-digit values like1788108484are the present day. The same number means the same instant on every machine on Earth, which is the entire reason the format exists.