Learning & reference
Date and time formats, and where they break
After character encoding, dates are the most reliable source of quiet data corruption in software. The formats look interchangeable and are not, the boundaries are in odd places, and two of the most widely used systems contain deliberate bugs that you have to reproduce rather than fix.
The formats, and what each actually permits
| Format | Example | Where you meet it | Watch for |
|---|---|---|---|
ISO 8601 | 2026-09-16T14:30:00+02:00 | The umbrella standard. | Also allows 2026-W38-3, 2026-259, 20260916T143000Z and 24:00. A parser accepting "ISO 8601" may accept far more than you expect, or far less. |
RFC 3339 | 2026-09-16T14:30:00Z | JSON APIs, logs, most "ISO" fields. | A strict profile: full date and time, an offset always required, T and Z case-insensitive. No week or ordinal dates. |
RFC 5322 | Wed, 16 Sep 2026 10:52:37 +0200 | Email headers. Every .eml in the email corpus. |
English day and month abbreviations regardless of locale, and obsolete forms with two-digit years still appear in old mail. |
HTTP-date | Wed, 16 Sep 2026 12:30:00 GMT | Last-Modified, Expires, Date. |
Always GMT, always that exact shape. Second resolution only, which is why If-Modified-Since can miss a change made in the same second. |
Unix epoch | 1789561800 | Databases, logs, JWT exp. |
Seconds or milliseconds is never stated. A value near 1.7 billion is seconds; near 1.7 trillion is milliseconds. |
Excel serial | 46281 | Spreadsheets, CSV exported from them. | A day count, not a timestamp. Two incompatible systems, and one deliberate off-by-one. See below. |
The practical rule: store UTC in RFC 3339, render in the user's zone at the last possible moment, and never store a local time without its zone. Most date bugs are a conversion that happened one layer too early.
The boundaries worth testing
| Boundary | Exact value | What breaks |
|---|---|---|
| Signed 32-bit epoch overflow | 2038-01-19T03:14:07Z | Wraps to 1901. Still live in embedded systems, 32-bit builds, and MySQL TIMESTAMP, whose range ends here. DATETIME does not. |
| Unix epoch zero | 1970-01-01T00:00:00Z | The classic "1 January 1970" in a UI means an unset value was rendered as a date. |
| Negative epoch | anything before 1970 | Unsigned columns and naive parsers reject dates of birth. Surprisingly common. |
JavaScript Date range | ±8.64e15 ms from epoch | Roughly ±273,790 years. Beyond it you get Invalid Date rather than an error. |
| Leap day | 2028-02-29 | Add one year to 29 February and a naive implementation produces an invalid date. Pick a leap year in the future, not 2024. |
| Leap second | 23:59:60 | A legal ISO 8601 time that most parsers reject. The last one was inserted at the end of 2016, and in 2022 it was agreed to retire them by 2035. |
| Year 10000 | 10000-01-01 | Breaks every fixed-width YYYY format and any string comparison used for sorting. |
Excel serial dates, and the bug you must reproduce
Excel stores a date as a day count, not a timestamp, and it believes 1900 was a leap year. The bug was inherited from Lotus 1-2-3 and has been kept ever since, because fixing it would shift every date in every existing workbook.
| Serial | Excel says | Reality |
|---|---|---|
| 1 | 1900-01-01 | Correct |
| 59 | 1900-02-28 | Correct |
| 60 | 1900-02-29 | This day never existed. 1900 was not a leap year: it is divisible by 100 and not by 400. |
| 61 | 1900-03-01 | Correct, and correct for every date after it |
| 25569 | 1970-01-01 | Correct. This is the Excel-to-Unix conversion constant |
| 46281 | 2026-09-16 | Correct |
Converting safely: for any date from 1 March 1900 onward, the real date is the serial added to 30 December 1899, and that offset is only correct because it absorbs the phantom day. Before that date the arithmetic is off by one. Old Macintosh workbooks may instead use the 1904 system, where serial 0 is 1 January 1904, a difference of 1462 days: read the workbook's date-system flag rather than assuming. Test with the legacy .xls and .xlsx fixtures.
Time zones and daylight saving
An offset is not a zone
+01:00 records what the gap was at one instant. Europe/London records the
rules. For anything in the future, store the name, or a government changing its DST dates silently
moves your meeting.
The hour that does not exist
On the spring transition the clock jumps from 01:59 to 03:00. A local time of 02:30 that day is not a real instant. Many libraries return the next valid time; some throw; some silently give you the wrong day.
The hour that happens twice
On the autumn transition 01:30 occurs twice, an hour apart. A timestamp without an offset is genuinely ambiguous, and "order by created_at" puts events in the wrong sequence.
Zones change
The tz database is updated several times a year as countries change their rules. A system that pinned its copy years ago is now wrong for some regions. This is a dependency, not a constant.
Offsets are not whole hours
India is +05:30, Nepal +05:45, Chatham Islands +12:45. Any code
storing an offset as an integer number of hours is already broken.
Some zones are past +12:00
Kiribati is +14:00. The real date there can be a day ahead of UTC, which breaks
"today" calculations that assume a ±12 range.
ISO week dates drift from the calendar year
ISO 8601 weeks run Monday to Sunday, and week 1 is the week containing the first Thursday. The week-numbering year therefore does not match the calendar year at the boundary.
| Calendar date | ISO week date | Consequence |
|---|---|---|
| 2019-12-30 | 2020-W01-1 | A December date whose ISO year is 2020. |
| 2021-01-01 | 2020-W53-5 | A January date whose ISO year is 2020. |
What this breaks: grouping a report by ISO week and by calendar year gives different totals at every year boundary, and a dashboard that mixes the two will never reconcile. Some years have 53 ISO weeks, so a hard-coded 52 is also wrong.
Parsing traps
01/02/2026
2 January in the US, 1 February nearly everywhere else. Ambiguous for the first twelve days of every month and silently wrong, never an error.
Two-digit years
Still arrive from legacy systems and card expiry fields. Whatever pivot you choose is wrong for somebody's date of birth.
Seconds vs milliseconds
Nothing in an epoch integer says which it is. Feeding seconds to a millisecond parser gives 1970; the reverse gives the year 55000.
A missing Z
2026-09-16T14:30:00 has no zone. JavaScript treats the date-only form as UTC and the
date-time form as local, which is a genuine source of one-day-off bugs.
Locale-dependent month names
RFC 5322 and HTTP dates always use English abbreviations. Formatting them with a locale-aware function on a machine set to German produces headers no parser accepts.
Sorting formatted dates
ISO 8601 sorts correctly as a string, which is its best property. Almost every other format does
not, and "09/16/2026" < "10/01/2025" is true.
Test it
The epoch converter on the tools page shows seconds, milliseconds, ISO 8601 and local time side by side, so a units mix-up is visible immediately.