Seconds to Days

Ten Digits in a Column That Are Really a Date

Somewhere in almost every log line, database row and API payload there is a bare integer standing in for a moment in time. It is the number of seconds since midnight UTC on 1 January 1970, and it is stored that way because a single integer sorts, subtracts and indexes without any of the trouble a formatted date brings. The cost is that nobody can read it. Dividing by 86 400 turns that integer into a day number, and a day number is close enough to a date that you can reason about it while you are still looking at the query results.

Conversion factor: 1 d = 86 400 s, so divide seconds by 86 400. A timestamp of 1 700 000 000 is 19 675.9259 days after the epoch: 19 675 whole days lands on 14 November 2023, and the leftover 0.9259 d — 80 000 s — is 22:13:20 UTC.

What the Day Number Is Good For

Sanity-checking a suspicious value

A day count near 20 000 is a plausible recent date. One in the millions means the field was milliseconds, and one near zero means something defaulted to the epoch instead of being written.

Measuring an age, not a moment

Subtract two timestamps and the difference is a duration in seconds. Divided by 86 400 it becomes record age, token lifetime or retention window in the unit a policy is written in.

Spotting a ceiling before it arrives

The signed 32-bit limit sits at 2 147 483 647, which is day 24 855 — a finite distance away, and worth knowing when a value is stored in a four-byte column.

No timezone in the number

Epoch seconds are always UTC. Whatever offset a screen applies afterwards is presentation; the stored integer never moves, which is exactly why systems keep it in this form.

Getting From a Raw Field to Something Readable

The workflow is one paste and one glance, usually in the middle of debugging something else entirely.

Paste the field exactly as it appears

Drop 1 700 000 000 or 1 234 567 890 into the left box. Spaces are ignored and a comma is read as a decimal point, so a value copied out of a formatted table needs no tidying first.

Count the digits before you trust it

Ten digits is seconds and lands in the tens of thousands of days. Thirteen digits is milliseconds and will come back as tens of millions of days — switch the left dropdown to milliseconds and the result falls back into range.

Reverse it to build a range boundary

The swap button (↔) gives d → s, which is what you want when a retention rule says 90 days and the query needs 7 776 000 seconds subtracted from now.

Copy digits straight into the shell

The copy control hands over the bare number with no unit and no spacing, ready to paste after a date -d @ or into a where-clause. Ctrl + C in a field behaves the same.

Do not route a timestamp through the year unit: the year in the dropdown is a fixed 365.2425 days and the month a fixed 30.4375, both averages. They give a usable age, never a calendar date. Keep timestamp work in seconds and days, where every step is exact.

Epoch Landmarks and the Day Numbers Behind Them

The values that show up in test fixtures, overflow discussions and screenshots of odometer-watching, each with the day count it divides into and the UTC moment it names.

Landmark	Epoch seconds	Days since epoch	UTC date and time
The epoch itself	0	0	1970-01-01 00:00:00
One billion seconds	1 000 000 000	11 574.07	2001-09-09 01:46:40
The counting-digits moment	1 234 567 890	14 288.98	2009-02-13 23:31:30
1.5 billion seconds	1 500 000 000	17 361.11	2017-07-14 02:40:00
Two billion seconds	2 000 000 000	23 148.15	2033-05-18 03:33:20
Signed 32-bit ceiling	2 147 483 647	24 855.13	2038-01-19 03:14:07
Unsigned 32-bit ceiling	4 294 967 295	49 710.27	2106-02-07 06:28:15

Read down the day column and the shape of the problem is obvious: the whole usable range of a signed four-byte timestamp is under twenty-five thousand days, and we have already spent more than three-quarters of it. Treating the field as unsigned buys another 24 855 days and pushes the wall to 2106, but it also throws away every date before 1970 — which is why the real fix everywhere is a 64-bit field rather than a clever reinterpretation of the old one.

What This Pair Does While You Are Debugging

Paste, glance, carry on

The day count appears while you type, so identifying a stale row or a bogus default costs one paste rather than a detour into a language REPL.

Millisecond fields on the same page

Change the left dropdown to milliseconds and a thirteen-digit value converts without leaving the tab — the fastest way to settle which unit a mystery column is in.

Retention windows the other way

Swap the pair and a policy stated in days becomes the second offset a cut-off query subtracts, so both halves of the job live on one screen.

Fractions of a day survive

Up to eight decimals are kept, so the part after the decimal point is still the time of day rather than something rounded away — 0.5 is noon, 0.75 is 18:00.

Questions About Epoch Time and the 2038 Rollover

My field says 1700000000 — is that seconds or milliseconds?

Count the digits. Ten digits is seconds for any date between 2001 and 2286, and 1 700 000 000 divides into 19 675.93 days — a perfectly sensible November 2023. Thirteen digits is milliseconds, the convention in JavaScript, Java and most JSON APIs that were designed around them; run those through the seconds side and you get roughly 19.7 million days, about 53 000 years, which is the tell. Nanosecond fields with nineteen digits turn up in tracing and time-series systems. When a column mixes the two, the mismatch usually shows as records dated either just after 1970 or somewhere in the far future.

What actually happens to a 32-bit system in January 2038?

At 03:14:07 UTC on 19 January 2038 a signed 32-bit counter reaches 2 147 483 647, its largest representable value. One tick later it wraps to −2 147 483 648, which reads as 20:45:52 UTC on 13 December 1901. Systems then either report dates in the early twentieth century or refuse to advance at all, and anything doing date arithmetic — certificate validity, scheduling, billing periods — starts producing negative durations. The signed type is a deliberate early choice so that pre-1970 dates could be expressed; it is also the reason the overflow lands in 1901 rather than back at the epoch. Sixty-four-bit time fields push the ceiling far beyond any practical horizon, so the exposure now lives in embedded devices, on-disk formats and network protocols with a four-byte field baked in.

Where do leap seconds go if every POSIX day is exactly 86 400 seconds?

They are not in the count at all. POSIX defines the value as a formula over calendar fields in which a day is always 86 400 seconds, so a leap second cannot be represented — the timestamp simply repeats a value or is nudged so the extra second disappears. That is why a division by 86 400 is exact here and never accumulates drift, and also why a POSIX timestamp is not a true count of elapsed SI seconds since 1970: it is behind atomic time by the number of leap seconds inserted since, which has been 27 since 1972. Many large operators avoid the discontinuity entirely by smearing the extra second across a whole day, making each second imperceptibly longer instead of repeating one.

Why does the day count come out with a decimal fraction?

Because the fraction is the time of day. A timestamp names an instant, not a date, so dividing it by 86 400 leaves a whole number of complete days plus however far into the current one the instant falls. Multiply the fraction back by 86 400 to get the seconds past midnight UTC: 0.25 is 06:00, 0.5 is noon, 0.9259 is 22:13:20. If you only want the calendar day, take the integer part and ignore the rest — but be careful doing that with negative values, where truncating toward zero and flooring disagree, which is a classic source of dates landing a day out for anything before 1970.

Can I work out the calendar date from a day number without a library?

You can get very close in your head and exact with a little care. Divide the day number by 365.2425 for the approximate elapsed years: 19 675.93 ÷ 365.2425 ≈ 53.87, so 1970 plus about 53.9 years puts you in late 2023, and 0.87 of a year is roughly day 318, which is 14 November. That matches the exact answer, but the method only works because the divisor is the average Gregorian year length; it will drift by a day or two around leap years and it is not a substitute for real date arithmetic. For an exact result, keep the day number as an offset from 1970-01-01 and let a date library — or date -d @1700000000 at a shell — add it, since that path counts real months and leap days instead of an average.