What a Unix timestamp actually is
A Unix timestamp is one integer: the number of seconds elapsed since
1970-01-01T00:00:00Z. That is the whole specification. There is no time zone attached,
no calendar, no locale, no daylight-saving rule — just a count from a fixed instant, which is why the
same number means the same moment for every machine that reads it.
That design buys two properties you cannot get from a formatted date string. First, comparison is
numeric: a < b answers "did a happen first" without parsing anything. Second,
arithmetic is exact and calendar-free, because Unix time is defined so that every day contains
exactly 86,400 seconds. Leap seconds are not assigned their own value; the count repeats or holds
rather than incrementing past one. So a difference of 604,800 is always seven days, no lookup table
required.
The costs show up the moment you leave that model. A timestamp cannot express "9 a.m. local time next Tuesday" because it does not know where you are, and it cannot express a date before 1970 without going negative — which several languages will not let it do. And because the number carries no unit, nothing stops one system writing seconds into a field another system reads as milliseconds. That last failure is what most people are actually here to diagnose.
How the unit is detected
The converter picks a unit from the digit count, and the reason that works is arithmetic rather than
guesswork. Seconds have had 10 digits since 1000000000
(2001-09-09T01:46:40.000Z) and will keep having 10 until 9999999999 in November 2286.
Multiply by a thousand and every one of those values gains exactly three digits, so the millisecond
window is 13 and the microsecond window is 16. The three windows are separated by construction, not
by convention.
Epochly widens each window slightly to absorb older and future values: 1–11 digits reads as
seconds, 12–14 as milliseconds, 15–17 as microseconds and
18–20 as nanoseconds. Eleven digits of seconds already reaches the year 5138, so
nothing longer can plausibly be a second count. If you disagree with the verdict — you have a
three-digit millisecond value, say — the s / ms / µs / ns buttons force the unit and the
reasoning line updates to say the choice was overridden. The full digit-by-digit map, with the exact
date range each length covers, is on the
Unix timestamp to date page.
Getting the epoch in ten languages
Every row below uses the same instant — 1700000000, which is
2023-11-14T22:13:20.000Z — so you can compare the shapes directly. The
returns column is the one to read first: it is the difference between a timestamp that lands
on today and one that lands in 1970.
| Language | Current epoch returns | Get the current epoch | Epoch → date | Date → epoch |
|---|---|---|---|---|
Python 3 import time; from datetime import datetime, timezone | float seconds | time.time() # 1786716780.560205
int(time.time()) # 1786716780 | datetime.fromtimestamp(1700000000, tz=timezone.utc)
# 2023-11-14 22:13:20+00:00 | datetime(2023, 11, 14, 22, 13, 20,
tzinfo=timezone.utc).timestamp()
# 1700000000.0 |
| JavaScript / TypeScript | integer milliseconds | Date.now() // 1786716780560
Math.floor(Date.now() / 1000) // 1786716780 | new Date(1700000000 * 1000).toISOString()
// "2023-11-14T22:13:20.000Z" | new Date("2023-11-14T22:13:20Z").getTime() / 1000
// 1700000000 |
Java 8+ import java.time.Instant; | long milliseconds | System.currentTimeMillis(); // 1786716780560L
Instant.now().getEpochSecond(); // 1786716780L | Instant.ofEpochSecond(1700000000L)
// 2023-11-14T22:13:20Z
Instant.ofEpochMilli(1700000000000L) | Instant.parse("2023-11-14T22:13:20Z")
.getEpochSecond(); // 1700000000 |
Go import "time" | int64 seconds | time.Now().Unix() // 1786716780
time.Now().UnixMilli() // 1786716780560 (Go 1.17+) | time.Unix(1700000000, 0).UTC().Format(time.RFC3339)
// "2023-11-14T22:13:20Z" | t, _ := time.Parse(time.RFC3339, "2023-11-14T22:13:20Z")
t.Unix() // 1700000000 |
Rust (std) use std::time::{Duration, SystemTime, UNIX_EPOCH}; | u64 seconds (from a Duration) | SystemTime::now()
.duration_since(UNIX_EPOCH)
.unwrap()
.as_secs() // 1786716780u64
// .as_millis() -> u128 | let t = UNIX_EPOCH + Duration::from_secs(1_700_000_000);
// std has no calendar formatting; use chrono or the time crate | t.duration_since(UNIX_EPOCH)
.unwrap()
.as_secs() |
| PHP | int seconds | time(); // 1786716780
(int) (microtime(true) * 1000); // 1786716780560 | gmdate('c', 1700000000);
// "2023-11-14T22:13:20+00:00" | strtotime('2023-11-14 22:13:20 UTC');
// 1700000000 |
| Bash (GNU coreutils) | integer seconds | date +%s # 1786716780
date +%s%3N # 1786716780560 | date -u -d @1700000000 +'%Y-%m-%dT%H:%M:%SZ'
# 2023-11-14T22:13:20Z | date -u -d '2023-11-14 22:13:20' +%s
# 1700000000 |
| PostgreSQL | seconds with a fraction (numeric from 14, double precision before) | SELECT EXTRACT(EPOCH FROM now());
-- 1786716780.560205 | SELECT to_timestamp(1700000000);
-- 2023-11-14 22:13:20+00 | SELECT EXTRACT(EPOCH FROM
TIMESTAMPTZ '2023-11-14 22:13:20+00');
-- 1700000000 |
| MySQL | integer seconds | SELECT UNIX_TIMESTAMP();
-- 1786716780 | SELECT FROM_UNIXTIME(1700000000);
-- 2023-11-14 22:13:20 (session time zone) | SELECT UNIX_TIMESTAMP('2023-11-14 22:13:20');
-- interpreted in the session time zone |
| Excel / Google Sheets | days since 1899-12-30 (serial) | =ROUND((NOW()-25569)*86400, 0)
(only UTC if the sheet clock is UTC) | =A1/86400+25569
then format the cell as a date | =ROUND((A1-25569)*86400, 0)
where A1 holds a UTC date/time |
Seconds or milliseconds? Only two of the ten give you milliseconds
Of the ten environments above, exactly two hand back milliseconds from their most-reached-for call:
JavaScript / TypeScript and Java 8+. Seven of the
remaining eight return seconds — Python's time.time(), Go's Unix(), PHP's
time(), Rust's as_secs(), date +%s,
UNIX_TIMESTAMP() and EXTRACT(EPOCH …). The tenth, Excel / Google
Sheets, is in neither camp: it hands back a day serial counted from 1899-12-30, not
an epoch count in any unit, which is why its row needs the 25569 constant rather than a factor of
1000. A Node service writing to a Python consumer is therefore a factor of 1000 apart by default, and
neither side gets a type error, because both values are just integers.
- Python 3 — time.time() is a float, so int() truncates rather than rounds. A naive datetime (no tzinfo) passed to .timestamp() is interpreted in the machine's local zone, not UTC — that is the classic silent offset bug.
- JavaScript / TypeScript — Everything in JS is milliseconds. new Date(1700000000) without the ×1000 gives 1970-01-20, which is the single most reported "my dates are all in 1970" bug.
- Java 8+ — currentTimeMillis() is milliseconds but Instant.getEpochSecond() is seconds, and both are long. Passing one where the other is expected compiles cleanly and fails only at runtime, in the output.
- Go — time.Unix takes (seconds, nanoseconds) — the second argument is not milliseconds. time.Unix(0, ms*1e6) is the manual millisecond form on pre-1.17 Go; UnixMilli/UnixMicro exist from 1.17; UnixNano has been there since Go 1.0.
- Rust (std) — duration_since returns a Result because the clock can be earlier than UNIX_EPOCH, and Duration is unsigned — std cannot represent a pre-1970 instant as a positive u64. Pre-1970 needs a date crate.
- PHP — date() formats in the script's configured time zone while gmdate() is always UTC. strtotime() on a string with no zone suffix also uses the configured zone, so the same code gives different numbers on two servers.
- Bash (GNU coreutils) — BSD/macOS date is a different program: use date -u -r 1700000000 to expand a timestamp and date -u -j -f '%Y-%m-%d %H:%M:%S' '…' +%s to parse one. The GNU -d @… form is an error there.
- PostgreSQL — to_timestamp() returns timestamptz, which psql prints in the session TimeZone setting — the stored instant is right, the text you see is not UTC unless the session is. EXTRACT(EPOCH …) on a plain timestamp (no tz) does no conversion at all: the manual defines it as the nominal seconds since 1970-01-01 without regard to time zone, so the result does not move when you SET TimeZone. Feeding that number back through to_timestamp() silently assumes the original was UTC. EXTRACT itself returns numeric from PostgreSQL 14; earlier versions returned double precision.
- MySQL — Bare UNIX_TIMESTAMP() with no argument is defined against UTC, so the value in the row above is already a true epoch count whatever the session zone is. The session zone bites on the other two forms: UNIX_TIMESTAMP(date) reads its argument as a wall-clock time in @@session.time_zone, and FROM_UNIXTIME() prints in that zone too. They round-trip against each other, but neither matches the UTC text above unless you SET time_zone = '+00:00' first.
- Excel / Google Sheets — Serials carry no time zone, so the result is UTC only if the cell was. The serial is a binary double, so the round trip drifts by up to a fraction of a microsecond — hence the ROUND(). The constant also assumes the default 1900 date system; the legacy "1904 date system" option (File → Options → Advanced) shifts every serial and breaks 25569.
Why the Excel constant is 25569 and not 25568
Excel does not store epoch seconds; it stores a serial number of days, with serial 1 being 1900-01-01. Converting between the two needs the serial of 1970-01-01, and that number is the classic off-by-one trap in spreadsheet date maths. Here is the derivation, which you can reproduce in one line of any language:
- The true day count from 1900-01-01 to 1970-01-01 is 25,567 days (70 years, of which 17 are leap years — 1904 through 1968 — and 1900 is not one, because the Gregorian rule excludes century years that are not divisible by 400).
- If serial 1 is 1900-01-01, a correct calendar puts 1970-01-01 at serial 25,568.
- Excel counts a 1900-02-29 that never existed, so every serial from 1900-03-01 onward is one higher than it should be. That puts 1970-01-01 at 25569.
Because the phantom day sits before every date anyone converts in practice, the single constant
25569 works for all of them — the error is uniform, so it cancels.
The formulas are =A1/86400+25569 to turn epoch seconds into a serial and
=ROUND((A1-25569)*86400, 0) to go back. The ROUND is not
decoration: a serial is an IEEE 754 double, and dividing by 86,400 then multiplying back drifts by
up to a fraction of a microsecond, which is enough to turn a whole second into
999999999.9999999. Two further cautions: serials carry no time zone, so the result is
UTC only if the cell was, and the legacy 1904 date system option shifts every serial and
invalidates the constant entirely.
Three failures this tool is built to catch
1. The log line that says 1970
A dashboard shows an event at 1970-01-20T16:13:20Z. Nothing happened on that date; the
service did not exist. Paste the raw field value into the converter and the digit count settles it
instantly: the stored number is 1700000000, ten digits, seconds — but the renderer fed
it to a millisecond API. The date 1970-01-20 is the fingerprint of a current seconds value read as
milliseconds, and its neighbours (1970-01-12, 1970-01-25) mean the same thing.
2. The record dated in the year 55840
The mirror image. A 13-digit millisecond value handed to a seconds API becomes
+055840-11-08T22:13:20Z — a year so absurd that most date libraries print it in ISO
8601's expanded form with a leading plus, and some simply throw. Anything with a five- or six-digit
year is this bug and only this bug.
3. The report that is off by exactly one day
Not a unit problem — a zone problem. An event at 1700000000 is
2023-11-14T22:13:20.000Z in UTC, but 2023-11-15 in Tokyo and still 2023-11-14 in Los Angeles.
Group those by "date" in three different zones and you get three different daily totals from
identical data. The converter prints UTC and your resolved zone on adjacent lines precisely so this
is visible rather than inferred; the
date to Unix timestamp page tabulates eleven zones holding the
same wall clock at eleven different instants.
The five focused pages
- Unix timestamp to date — the decode direction on its own, with a digit-length table mapping every value length from 1 to 17 digits onto the exact calendar window it covers.
- Date to Unix timestamp — the encode direction, opening on the date picker, plus the eleven-zone spread table showing how far apart "the same time" really is.
- Milliseconds to date — 13-digit values from
Date.now()andSystem.currentTimeMillis(), with both readings of five real literals printed side by side. - Current Unix timestamp — the live count, plus the milestone table: the epoch, the first 10-digit value, the 2038 ceiling and what 2³¹ becomes if you read it as milliseconds.
- Discord timestamp generator — the
<t:UNIX:F>markup with all nine style suffixes and a live preview of each.