DEVTOOLBOX / GUIDES this guide
Timestamps, time zones, ISO 8601 and week numbers: an ambiguity checklist for time data
Almost every time bug is a category mistake: a wall clock stored where an instant was needed, or a zone replaced by the offset it happened to carry on the day the record was written. This guide separates the four things called "the time", then fixes the boundaries you can check: 2^31-1 seconds, 8.64e15 milliseconds, the ISO 8601 and RFC 3339 grammars that overlap without agreeing, and the days that are 82,800 or 90,000 seconds long. Each section ends with a criterion and the point past which data cannot be recovered.
Four different things called "the time": instant, offset, zone, wall clock
Four distinct concepts share one name, and confusing them explains most date bugs. An instant is a point on the timeline, identical everywhere: 2026-10-23T03:26:17Z. An offset is a fixed displacement from UTC bolted onto a value, such as +08:00; it says how to read that one value and nothing about any other moment. A zone is a named rule set from the IANA tz database, such as Asia/Shanghai: the current offset plus every historical and future change. A wall clock is the bare digits a human reads, 09:00, with no location attached at all.
Choose by what the value has to survive. If you will sort, compare or subtract, you need an instant; the offset is decoration for display. If you are rendering a moment that already happened, an offset is enough, since nothing can move it. If the moment is in the future and anchored to local life, a standup at 09:00 or a market open, you need the zone identifier: the offset correct today is not the one correct after the next rule change. If the value is deliberately location-free, such as a recurring team meeting, keep it as a wall clock plus a per-user resolution rule and attach no zone.
The irreversible move is zone to offset. Once 2026-03-08T09:00:00+08:00 is stored without Asia/Shanghai, no algorithm recovers the zone: 09:00 could have been written in Tokyo, Perth or Manila, and a future tzdata update is invisible to those records. The mirror misuse treats an offset-bearing string as local: 09:00+08:00 and 09:00 in New York are twelve or thirteen hours apart, so a report grouping on the string merges rows that never shared a moment.
| Kind | What it fixes | Survives a rule change | Reach for it when |
|---|---|---|---|
| Instant | A point on the timeline | Yes, by definition | Storage, ordering, arithmetic |
| Offset | How to read one value | No, frozen forever | Rendering past events, log lines |
| Zone id | Rules for every value | Yes, via tzdata updates | Future local events, calendars |
| Wall clock | Nothing beyond the digits | Not applicable | Location-free recurring times |
Only the second row loses information at write time; the other three survive a DST change.
Epoch values: seconds versus milliseconds, leap seconds, and the 2038 boundary
Two scales are in circulation and both are called Unix time. 1761000000 with ten digits and 1761000000000 with thirteen are the same moment; the second is milliseconds. Digit counting is a heuristic, not a type: the common test is to treat anything above 1e11 as milliseconds, which is safe today because milliseconds passed 1e11 in 1973 while seconds will not until the year 5138, but it is a guess about intent rather than a fact about the data. Name the unit in the field, as created_at_ms or created_at_s, and the whole class of thousand-fold errors disappears.
Each representation has a cliff. Signed 32-bit seconds stop at 2^31-1, which is 2147483647 and equals 2038-01-19T03:14:07Z; unsigned 32-bit runs to 2106-02-07T06:28:15Z. In JavaScript, Date holds plus or minus 8.64e15 milliseconds, exactly 100,000,000 days or roughly 273,790 years from the epoch, beyond which every operation yields NaN. Sixty-four-bit signed nanoseconds run out at 2^63-1, about 2262-04-11T23:47:16Z. Precision also moves one way only: nanoseconds to microseconds to milliseconds to seconds each drops three digits, and rounding is not invertible.
Leap seconds break the intuition that Unix time measures elapsed time. POSIX time defines every day as exactly 86,400 seconds and never counts a leap second, so subtracting two Unix timestamps undercounts real elapsed time by the number of leap seconds between them. Twenty-seven have been inserted since 1972, the last at 2016-12-31T23:59:60Z, on average roughly one every eighteen months but none in a decade; TAI minus UTC currently stands at 37 seconds. RFC 3339 permits the value 23:59:60, while JavaScript Date cannot represent it and rolls over to the next day. If you compare clocks across machines, ignore leap seconds and say so.
| Representation | Last representable moment | How it fails |
|---|---|---|
| 32-bit signed seconds | 2038-01-19T03:14:07Z | Wraps to 1901, or throws |
| 32-bit unsigned seconds | 2106-02-07T06:28:15Z | Wraps silently to 1970 |
| Milliseconds in a double | 275760-09-13 | Invalid Date, NaN comparisons |
| 64-bit signed nanoseconds | 2262-04-11T23:47:16Z | Overflows when computed in ns |
Four independent cliffs. Only the first is famous, and only the third fails loudly.
ISO 8601 and RFC 3339: what each form claims and what it leaves open
ISO 8601 is a family of standards, not a single format. The current parts are ISO 8601-1:2019 for the basic rules and ISO 8601-2:2019 for extensions, together replacing ISO 8601:2004. The family accepts basic form 20261023T032617Z and extended form 2026-10-23T03:26:17Z, ordinal dates like 2026-296, week dates like 2026-W43-5, reduced precision such as 2026-10 or 2026, an end-of-day 24:00:00, durations like P1Y2M3DT4H, and intervals written either as start and end or as start and duration separated by a slash.
RFC 3339 is a profile that cuts the family down to one shape for the wire: full date, full time, a mandatory offset as Z or plus or minus hh:mm, four-digit years from 0000 to 9999, extended form only. The difference is easy to state as rejections. 20261023T032617Z is valid ISO 8601 and invalid RFC 3339. 2026-10 is valid ISO 8601 and invalid RFC 3339. 2026-10-23T03:26:17 without an offset is valid ISO 8601 and invalid RFC 3339. RFC 3339 additionally allows second 60 for positive leap seconds, which ISO 8601-1 does not define, and reserves the offset -00:00 to mean "offset unknown", a different claim from Z and from plus 00:00.
The practical rule is asymmetric: accept ISO 8601 on input, because humans and spreadsheets produce week dates, ordinal dates and offsets without a colon, then emit RFC 3339 on the wire, because exactly one spelling with a mandatory offset removes downstream parsing choices. Normalise at the boundary and store one form. What neither standard does is check the calendar: 2026-02-30 satisfies both grammars and denotes nothing. The other trap is a parser that guesses. In JavaScript, Date.parse("2026-10-23") is UTC midnight while Date.parse("2026-10-23T00:00") is local midnight, so the same calendar date silently shifts by the host offset between the two spellings.
| Form | ISO 8601-1 | RFC 3339 | Safe to exchange |
|---|---|---|---|
| 2026-10-23T03:26:17Z | Valid | Valid | Yes |
| 2026-10-23T03:26:17 | Valid | Invalid, no offset | No, means local time |
| 2026-10-23 | Valid | Invalid, no time | Only as a calendar date |
| 2026-10 | Valid | Invalid | No, a month is not a point |
| 20261023T032617Z | Valid, basic form | Invalid | No, parsers diverge |
| 2026-W43-5 | Valid | Invalid | No, readers misread it |
| 2026-02-30 | Grammar only | Grammar only | No, needs a calendar check |
Grammar acceptance is not validity: the last row passes every parser that does not consult a calendar.
Time zones: IANA identifiers, DST transitions, and days that are 23 or 25 hours
A zone identifier such as Europe/Berlin is a rule table with a history: the offsets it used, the transitions it made, and the politics that moved them. The IANA tz database ships several releases a year, so a zone id is a promise to be told about changes, not a constant. Convert with the identifier, never with the offset you observed today: the identifier stays correct after the next rule change, the offset does not.
Offsets in use span -12:00 to +14:00, and some historical entries are not whole minutes, so any hardcoded list of offsets is wrong somewhere. Daylight saving usually shifts one hour but not always: Lord Howe Island shifts thirty minutes, and zones abandon it entirely, so a rule right for a city a decade ago can be wrong today. Savings rules are legislation, and legislation changes.
Day length follows. On a spring-forward day the local day is 23 hours, 82,800 seconds; on a fall-back day it is 25 hours, 90,000 seconds. A calendar day is therefore not an invariant duration; multiplying days by 86,400 is wrong twice a year in most zones. Samoa deleted 2011-12-30 entirely when it crossed the date line, so a local date can simply not exist and a daily report keyed on local dates has an unfillable gap.
Ambiguity needs a written policy. During fall-back, a local time like 01:30 occurs twice; the usual default takes the earlier, pre-transition reading, the alternative the later one. Pick deliberately. During spring-forward, 02:30 does not exist, so either shift forward to 03:30, shift back, or reject the input; shifting forward is least surprising. Ignoring both, a nightly job runs twice or not at all, and appointments drift an hour after a deploy.
| Day type | Length in seconds | What breaks under days x 86400 |
|---|---|---|
| Spring forward | 82,800 | Adding a day lands at 01:00 local |
| Fall back | 90,000 | Two readings of 01:30 merge into one |
| Half-hour shift | 84,600 or 88,200 | Hour-only rounding loses 30 minutes |
| Date-line change | A date may vanish or repeat | Daily rollups lose or double a bucket |
A "day" is a calendar object, not a duration; only durations convert cleanly to seconds.
Calendar arithmetic: why "one month later" is ambiguous
"One month later" is not a function. From 2026-01-31, a month later has at least three defensible answers: 2026-02-28, clamping to the last day of February; 2026-03-03, overflowing because February the 31st does not exist and JavaScript normalises it; and an error, rejecting the input. None is wrong. What is wrong is inheriting a library default silently and meeting that choice in production. Decide per field: invoice periods and trial expiries clamp; anything that must land on a working day needs a second rule on top.
"One day" is not 24 hours either, and the two operations diverge across transitions. Adding 24 hours to 2026-03-08T00:00 in America/New_York, a spring-forward day, lands at 2026-03-09T01:00 local, because that local day holds only 23 hours; adding one calendar day lands at 2026-03-09T00:00. Calendar arithmetic preserves the wall clock, duration arithmetic preserves elapsed time. Name the operation in code, and prefer the calendar form when the user said "tomorrow".
Duration literals inherit the same ambiguity. In ISO 8601, P1M means a month, between 28 and 31 days, while PT1M means a minute; the same letter means both, depending on which side of the T it sits. RFC 5545 DURATION, used by iCalendar, keeps that distinction and forbids mixing weeks with other designators. Where a duration feeds a schedule, store the literal form and re-resolve it against a zone each time instead of storing seconds.
Two more cases recur. Leap-day birthdays have no legal date in three of four years, so clamping to February 28 or March 1 is a product decision, not a technical one. Durations between instants also surprise: a nominal 09:00 to 17:00 shift lasts seven hours on a spring-forward day and nine on a fall-back day.
| Operation | Anchored to | Result from 2026-03-08T00:00 New York |
|---|---|---|
| Add 1 calendar day | Wall clock | 2026-03-09T00:00, 23 hours later |
| Add 24 hours | Elapsed time | 2026-03-09T01:00 local |
| Add 1 month | Wall clock, clamped | 2026-04-08T00:00 local |
| Add P30D | Elapsed time | 2026-04-07T01:00 local |
Two rows both say "a month" and differ by a day; two say "a day" and differ by an hour.
Week numbers, quarters and cron: conventions that disagree
ISO 8601 week dates start weeks on Monday and define week 1 as the week containing the first Thursday, equivalently the week containing January 4. The week-numbered year is therefore not the calendar year near the edges: 2025-12-29 is 2026-W01-1, and 2027-01-01 belongs to 2026-W53, which makes 2026 a 53-week year. In the 400-year Gregorian cycle, 71 years carry 53 weeks. Format the week-year with the week number, or late December rows appear to belong to another year, and never compute week 1 as the first seven days of January.
Other conventions disagree on both axes. The United States convention starts weeks on Sunday and puts January 1 in week 1, so a date can be week 1 in one system and week 53 in the other. CLDR exposes this as locale data, varying the first weekday and the minimum days in week 1 by region, so a locally rendered number may not match the ISO number stored. Store and report ISO, render locale-aware only in interface copy.
Cron disagrees in ways that produce incidents. The classic five-field form runs from minute to day-of-week, numbers Sunday as 0 and 7, and ORs the two day fields when both are restricted: 0 0 1 * 1 fires on the first of the month and every Monday. Quartz uses six or seven fields, adds seconds, and numbers Sunday as 1. Classic crontab also carries no timezone, so the server or container decides, and a job at 02:30 does not run on a spring-forward day.
Quarters repeat the disagreement: calendar quarters divide twelve months evenly, retail fiscal calendars use 4-5-4 week blocks, and both are called Q1. Choose the convention once per report, write it into the schema, and store the ISO week-year beside the week number.
| Convention | Week starts | Week 1 contains | Where it bites |
|---|---|---|---|
| ISO 8601 | Monday | The first Thursday | Week-year differs from calendar year |
| United States | Sunday | January 1 | Partial week 1, no week 53 handling |
| CLDR by locale | Varies by region | Varies by region | Rendered number differs from stored |
| Excel WEEKNUM mode 1 | Sunday | January 1 | Not ISO; ISOWEEKNUM is the ISO one |
The same calendar date can be week 1 or week 53 depending on the system, with no error anywhere.
Storage and display rules that prevent most time bugs
Five storage decisions cover nearly every case, and their order matters more than the format chosen.
Column types that look harmless are not. MySQL TIMESTAMP covers 1970-01-01 to 2038-01-19 UTC and converts through the session time zone on both read and write; DATETIME stores no zone and spans 1000 to 9999. PostgreSQL timestamptz holds an instant whose rendering depends on the session TimeZone, so a report run in another session zone moves the day boundary without changing a stored value. SQLite has no date type, so text sorts lexicographically, correct only while every value is UTC with the same precision and separators.
Display rules are shorter. Convert once, at the edge, and format there. Pass an explicit timeZone to Intl.DateTimeFormat rather than trusting the runtime default, since the same code then prints a different day on CI and on a laptop. When a user picks a time, read their zone identifier from Intl.DateTimeFormat().resolvedOptions().timeZone rather than the current offset, which expires at the next transition.
Three operations are irreversible: discarding the zone id, which destroys the rules forever; lowering precision, which destroys digits; and normalising invalid input such as 2026-02-30 into a real date, which destroys the evidence of what the user meant. Adding an offset to a stored local time is always safe; removing one is not.
- Instants: UTC, either RFC 3339 with a trailing Z or an integer epoch with the unit in the field name.
- Future local events: a local time plus an IANA zone id; RFC 9557 appends exactly that annotation.
- All-day values: a plain date with no time and no zone, because a birthday is not an instant.
- Precision: fixed per column, milliseconds or microseconds or nanoseconds, chosen once.
- Report buckets: a date plus an explicit zone, never the server default.
| Value | Store as | Never store as |
|---|---|---|
| Instant, past or cross-region | UTC, RFC 3339 with Z | Local string with no offset |
| Future local event | Local time plus zone id | A single offset, or a bare local time |
| All-day date | Plain date, no time, no zone | Midnight UTC, which shifts days |
| Duration or interval | Seconds, or an ISO 8601 P form | Two wall-clock readings |
| Report bucket | Date plus an explicit zone | The server default zone |
Rows two and three are the ones teams get wrong after launch, and both are unrecoverable.
Frequently asked questions
- Is a bare number timestamp in seconds or milliseconds?
- Count digits as first aid: ten digits is seconds, thirteen is milliseconds, and values above 1e11 should be treated as milliseconds because that threshold was passed in 1973 for milliseconds and will not be reached by seconds until the year 5138. Treat it as a guess, not a fact. Confirm by converting and checking that the resulting date is plausible, then fix the source so the unit is named in the field.
- Why is 2025-12-29 already in week 1 of 2026?
- ISO 8601 defines week 1 as the week containing the first Thursday, equivalently the week containing January 4, so an ISO week can straddle the new year. 2025-12-29 is Monday of 2026-W01, and 2027-01-01 falls in 2026-W53. Always print the week-year with the week number, otherwise December rows appear to jump backwards by two years across a report boundary.
- Do I need to handle leap seconds in application code?
- Almost never, and handling them badly is worse than ignoring them. Unix time defines every day as 86,400 seconds and never counts leap seconds, so your timestamps measure continuous wall time rather than elapsed SI time. Twenty-seven leap seconds exist, the last on 2016-12-31, and no new ones have been inserted since. If you compute elapsed durations from timestamps you are off by at most that count, which matters only in astronomy and precise metrology.
- Why did a cron job run twice, or not at all, on the DST change day?
- Local clock times are not unique and not total. On a spring-forward day, 02:30 never occurs in the affected zone, so a job scheduled then is skipped; on a fall-back day, 01:30 occurs twice and depending on the implementation the job runs once or twice. Cron has no timezone of its own, so the host timezone decides. Schedule critical jobs against UTC and keep local-hour expectations in documentation.
- Should I store the offset or the IANA zone identifier?
- Store the identifier whenever the value refers to a location and the date is in the future, because the identifier carries the rules and the offset only records what the rules said on the day of writing. Keep the offset as well if you want a faithful historical record, and use it alone only for past events, log lines or cases where no zone exists. The identifier survives tzdata updates; the offset silently expires.