Date and time format style guide — ISO 8601, RFC 3339, SQL, Unix

TL;DR — which format, when

The argument about date formats is one of the small handful of programming arguments that has an actual answer. The right answer depends on where the timestamp lives — wire format, storage, log, filename, or user display — and the table below is the version we hand new engineers on their first day.

Everything else in this guide is the reasoning behind the table, plus the specific rules, citations, and code that make it bulletproof. If you only remember one thing: store UTC instants, render local strings, never store local strings.

ISO 8601 — the standard you actually want

ISO 8601 is the international standard for date and time representation, first published in 1988 and last revised as ISO 8601-1:2019 and ISO 8601-2:2019. It defines a family of formats — calendar dates, ordinal dates, week dates, time-of-day, combined datetimes, durations, intervals, and recurring intervals — all in a single coherent grammar where longer units appear before shorter ones, so strings sort lexicographically into chronological order. That property alone is why every modern wire format and most log files use it.

The full grammar is wide. In practice you will encounter five forms, in roughly descending frequency: combined datetime with offset, calendar date, time-of-day, week date, and ordinal date.

// All valid ISO 8601 strings:
2026-05-29                       // calendar date
2026-W22-5                       // week date (year-Wweek-day)
2026-149                         // ordinal date (year-day_of_year)
15:43:00                         // local time
2026-05-29T15:43:00              // combined, no timezone (ambiguous)
2026-05-29T15:43:00Z             // UTC
2026-05-29T15:43:00+01:00        // explicit offset
2026-05-29T15:43:00.123456789Z   // nanosecond precision
20260529T154300Z                 // "basic" (compact) form, no separators

The standard distinguishes basic form (no separators: 20260529T154300) from extended form (with separators: 2026-05-29T15:43:00). Both are valid ISO 8601. Extended is what humans read; basic is what filenames and certain compact protocols use. Mix them in a single string and the result is invalid and most parsers will reject it.

Two small quirks bite people. First, the fractional second separator: the original standard preferred the comma (15:43:00,123), the 2004 revision allowed the period, and the 2019 revision recommends the period. Everyone uses the period in practice. Second, the timezone designator is optional in ISO 8601 itself — a bare 2026-05-29T15:43:00 is a valid "local" time with no attached offset, which means the parser cannot resolve it to an absolute instant. That is the single biggest source of bugs in date handling, and it is why RFC 3339 makes the offset mandatory.

Parser support is universal for the extended form. Browsers, Date.parse on the web platform, and datetime.fromisoformat in Python 3.11+ all accept it.

// Modern JavaScript: Date.parse and Temporal both accept RFC 3339.
const d = new Date("2026-05-29T15:43:00Z");       // 1748533380000

// Temporal (Stage 3 as of 2026, available behind flag or polyfill):
Temporal.Instant.from("2026-05-29T15:43:00Z");
Temporal.ZonedDateTime.from("2026-05-29T15:43:00+01:00[Europe/London]");

// Going the other way:
new Date().toISOString();                          // "2026-05-29T15:43:00.000Z"

# Python 3.11+: datetime.fromisoformat accepts most of ISO 8601.
from datetime import datetime, timezone

datetime.fromisoformat("2026-05-29T15:43:00+00:00")
datetime.fromisoformat("2026-05-29T15:43:00Z")     # 3.11+ only — earlier raises ValueError

# For older Pythons or strict parsing, use the third-party `dateutil`:
from dateutil.parser import isoparse
isoparse("2026-05-29T15:43:00Z")

# Always emit timezone-aware ISO strings:
datetime.now(timezone.utc).isoformat()             # '2026-05-29T15:43:00.123456+00:00'

For a one-off conversion or to verify a tricky offset, the date format converter round-trips between ISO 8601, RFC 3339, epoch, and a few legacy strftime patterns.

RFC 3339 — ISO 8601's stricter sibling

RFC 3339 was published in 2002 as a profile of ISO 8601 specifically for use on the internet. It tightens the parts of ISO 8601 that are ambiguous or under-specified and discards the parts (week dates, ordinal dates, basic form) that complicate parsers without adding wire-format value. If you are writing an API or JSON-formatted payload in 2026, RFC 3339 is the format you actually want — the broader name "ISO 8601" is mostly used as shorthand.

The differences are small but they matter for parser correctness:

// RFC 3339 is what 95% of modern APIs return.
// It is a strict subset of ISO 8601 — only the unambiguous parts.

2026-05-29T15:43:00Z             // OK: extended format, UTC, "Z" suffix
2026-05-29T15:43:00+01:00        // OK: extended format, numeric offset
2026-05-29T15:43:00.123Z         // OK: period as fractional separator

// NOT RFC 3339:
20260529T154300Z                 // basic format — ISO 8601 only
2026-05-29T15:43:00,123Z         // comma fractional — ISO 8601 only
2026-05-29T15:43:00              // missing offset — both reject this
2026-W22-5T15:43:00Z             // week dates — ISO 8601 only

The most important constraint is the mandatory timezone designator. RFC 3339 requires either a literal Z (Zulu, equivalent to +00:00) or an explicit numeric offset like +01:00. A bare datetime without an offset is not valid RFC 3339 and a strict parser will reject it. This is the right behaviour — an instant without an offset is not an instant, it is just a wall-clock string, and conflating the two is how production systems start drifting after a DST transition.

On the T vs space separator: RFC 3339 section 5.6 allows replacing the T with a literal space "for readability", but flags it as a quirk and recommends T for everything machine-generated. Many parsers accept both, some accept only one. Pick T and move on. Use the space-separator form only when humans are typing the string by hand, never on the wire.

Newer standards build on RFC 3339 rather than ISO 8601 directly: OpenAPI format: date-time, JSON Schema format: date-time, CloudEvents, OpenTelemetry, and most modern API specs all cite RFC 3339. If a framework gives you a choice between "RFC 3339" and "ISO 8601", they almost always mean the same thing in that codebase, and the strict subset is what is actually validated.

Unix epoch and friends

The Unix epoch is the number of seconds (or milliseconds, or microseconds, or nanoseconds) since 1970-01-01T00:00:00Z. It is the oldest standard time representation still in everyday use and the simplest possible one: a single integer that names an absolute instant. No string parsing, no timezone parameter, no locale, no DST. For a long time it was the default everywhere; today it is the default in JavaScript (Date.now() returns ms), in Java (System.currentTimeMillis()), in low-level Linux APIs, and in any code where you want to do arithmetic on timestamps without first parsing them.

// Same instant, four different precisions.
1748533380              // seconds (Unix epoch, the original)
1748533380123           // milliseconds (JavaScript default, Java System.currentTimeMillis)
1748533380123456        // microseconds (Python time.time_ns() // 1000, Postgres)
1748533380123456789     // nanoseconds (Go time.Now().UnixNano(), modern Linux)

// Conversion:
const ms = Date.now();
const s  = Math.floor(ms / 1000);
const ns = BigInt(ms) * 1_000_000n;

The four precisions in use today, in increasing order: seconds (the original, still used by HTTP cookies, JWT iat/exp, and most C-era APIs), milliseconds (JavaScript default since 1995, Java since forever, MongoDB BSON), microseconds (Python time.time_ns() // 1000, Postgres internally), nanoseconds (Go time.Now().UnixNano(), Linux clock_gettime(CLOCK_REALTIME, ...), modern observability pipelines). Going between them is multiplication; the trap is when a system mixes precisions inside a single stream and the consumer cannot tell which is which. Document the precision, or pick a format that does not need documenting (ISO 8601).

The famous 2038 problem is the year-2038 overflow of signed 32-bit time_t. At 03:14:07 UTC on 19 January 2038, a 32-bit signed integer counting seconds since 1970 wraps to a negative number, breaking any system still using it. 64-bit Linux, modern macOS, recent Windows, and all major language runtimes resolved this years ago — the remaining exposure is embedded systems, old C code, file formats that hard-coded 32-bit timestamps, and 32-bit integer columns in databases that someone "optimised" in 2009. If you are designing a schema in 2026, use bigint for any epoch column. The eight bytes vs four bytes is not a meaningful storage saving and the alternative is rewriting the column in 2037.

When epoch beats ISO 8601: arithmetic-heavy code (you would call .getTime() on every value anyway), high-frequency event streams where the four bytes per timestamp add up, embedded systems with no real date library, and storage formats where lexicographic sort order does not matter. When ISO 8601 beats epoch: anything a human will ever read, anything that has to survive a format migration, anything that travels through more than one language. In practice that covers nearly everything, which is why most modern systems default to ISO 8601.

A related pattern worth knowing: Twitter's snowflake IDs (and the many imitators — Discord, Instagram, Sony, Mastodon) embed an epoch-based timestamp in the high bits of a 64-bit integer ID, with a worker ID and a sequence counter packed below it. The result is a sortable, timestamp-encoding, monotonically-increasing, distributed-friendly ID that lets you extract the creation time without a separate column. If you are reaching for UUIDs in a hot path, snowflakes are worth a look. The format is documented in Twitter's open-source snowflake project; offset from the Unix epoch is per-deployment (Twitter uses 2010-11-04 00:00:00 UTC).

For converting between formats one timestamp at a time, the epoch converter accepts seconds, ms, microseconds, and nanoseconds and renders ISO 8601 in any IANA zone.

SQL date/time types

Every SQL database has its own answer to "how do I store a timestamp", and the answers do not interoperate cleanly. The good news is that the right choice for each engine is well-defined and stable. The bad news is that the column-type name often misleads, and the wrong choice silently appears to work until the first DST transition or the first cross-region replica.

Postgres — TIMESTAMPTZ wins

Postgres has the cleanest model. TIMESTAMPTZ (alias for TIMESTAMP WITH TIME ZONE) stores a UTC instant. On insert, Postgres normalizes any offset to UTC. On select, Postgres converts to the session's TIMEZONE setting. TIMESTAMP (without TZ) stores a wall-clock string with no offset and no instant — almost never what you want. DATE stores a calendar date with no time component, which is correct for birthdays, holidays, and any value that is genuinely date-only.

-- Postgres: prefer TIMESTAMPTZ for everything that represents an instant.
CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  occurred_at timestamptz NOT NULL,           -- UTC instant
  scheduled_at timestamptz,                   -- UTC instant
  scheduled_tz text                           -- IANA zone, for recurring events
);

-- Inserts: any offset works, Postgres normalizes to UTC.
INSERT INTO events (occurred_at) VALUES ('2026-05-29T15:43:00+02:00');
INSERT INTO events (occurred_at) VALUES ('2026-05-29T13:43:00Z');     -- same row

-- TIMESTAMP (without TZ) stores wall-clock with no instant — almost always wrong.
-- DATE has no time component. Both are appropriate for civil dates only.

The Postgres docs are explicit: Date/Time Types describes the entire family. The pattern that catches people: TIMESTAMPTZ does not remember the original offset of the inserted value. If you insert '2026-05-29T15:43:00+02:00' and query it back from a UTC session, you get 2026-05-29 13:43:00+00. The original +02:00 is gone. If you need the original zone, store it as a separate text column with the IANA name.

MySQL — DATETIME vs TIMESTAMP

MySQL has two incompatible types where Postgres has one. DATETIME is a literal wall-clock value with no timezone math, stored as-is. TIMESTAMP is silently converted to UTC at write time and back to the session's @@time_zone at read time, with the conversion applied by the server using the host's tzdata. The classic gotcha: TIMESTAMP values look stable in a single session and break the moment the application connects from a different timezone, or the moment the host time zone changes for OS upgrade reasons.

-- MySQL: DATETIME vs TIMESTAMP is the classic trap.
-- DATETIME stores a literal wall-clock string. No timezone math.
-- TIMESTAMP silently converts to UTC on write and back on read, using @@time_zone.

CREATE TABLE events (
  id          bigint AUTO_INCREMENT PRIMARY KEY,
  occurred_at TIMESTAMP NOT NULL,             -- coerced UTC, 1970-2038 range
  scheduled_at DATETIME(6)                    -- wall-clock, no coercion, microsecond precision
);

-- Caveat: TIMESTAMP rows depend on the session @@time_zone at write AND read.
-- Set it explicitly:
SET time_zone = '+00:00';

The MySQL reference is clear about this in Section 11.2.2. The pragmatic answer: pin @@time_zone to '+00:00' in your connection config, treat DATETIME as the "store UTC explicitly, no surprises" type, and avoid TIMESTAMP unless you specifically want the auto-conversion. The four-byte storage saving for TIMESTAMP over DATETIME(6) is rarely worth the ambiguity.

SQLite — no native type, you pick

SQLite has no dedicated date/time type. The official guidance in the datatype docs is to store dates as ISO 8601 text, as a real number representing the Julian day, or as an integer representing Unix epoch seconds. The built-in date functions (date(), datetime(), strftime()) work on all three, so the choice is mostly about how the data is read by your application layer.

-- SQLite has no native date/time type. Choose one of:
-- 1) ISO 8601 text:    '2026-05-29T15:43:00Z'   (sortable, human-readable)
-- 2) Unix epoch real:  1748533380.123           (compact, math-friendly)
-- 3) Julian day real:  2461190.15625            (rarely useful outside astronomy)

CREATE TABLE events (
  id           INTEGER PRIMARY KEY,
  occurred_at  TEXT NOT NULL                  -- store ISO 8601 with Z
);

-- Date functions still work on text columns:
SELECT datetime(occurred_at) FROM events;
SELECT strftime('%Y-%W', occurred_at) FROM events;

For most cases ISO 8601 text wins on debuggability — you can SELECT a row in the CLI and read the timestamp. For huge tables where size matters, epoch integer wins. The Julian day option is genuinely useful in astronomy and surprisingly rarely useful elsewhere.

SQL Server, Oracle, others

SQL Server has datetimeoffset, which preserves the original offset on disk — the only mainstream RDBMS type that does. Oracle has TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE with similar semantics to Postgres TIMESTAMPTZ. Both are well documented and behave more like Postgres than like MySQL — if you are coming from MySQL, the "what timezone is this actually in" question goes away.

Human-readable formats

Human-readable date strings — "May 29, 2026", "29/05/2026", "yesterday at 3 PM" — belong in two places only: the display layer of your application and casual human writing. They do not belong in your database, your API, your logs, or any file that another program will read. The reason is that "human-readable" is locale-dependent, and storage is global.

When you do render for users, the right primitive is Intl.DateTimeFormat in the browser (or the equivalent CLDR-backed formatter on your server). It handles locale, calendar system (Gregorian, Japanese, Hebrew, Buddhist, Islamic), numbering system, and timezone correctly, and it is specified by ECMA-402.

// Intl.DateTimeFormat is the right way to render dates for users.
// It respects locale, calendar system, numbering system, and time zone.

const d = new Date("2026-05-29T15:43:00Z");

new Intl.DateTimeFormat("en-US", { dateStyle: "medium", timeStyle: "short" }).format(d);
// "May 29, 2026, 11:43 AM"

new Intl.DateTimeFormat("en-GB", { dateStyle: "medium", timeStyle: "short" }).format(d);
// "29 May 2026, 16:43"

new Intl.DateTimeFormat("ja-JP-u-ca-japanese").format(d);
// "R8/5/29"  (Reiwa year 8)

new Intl.DateTimeFormat("en-US", {
  timeZone: "America/New_York",
  dateStyle: "full",
  timeStyle: "long",
}).format(d);
// "Friday, May 29, 2026 at 11:43:00 AM EDT"

The choice between absolute ("Wed, May 29, 11:43 AM") and relative ("1 hour ago", "in 2 days") display is a UX decision, not a format decision. Relative time reads naturally for recent events and breaks down as the gap grows — "5 months ago" is less useful than "December 2025". The pragmatic pattern is to show relative for anything under a week, then switch to absolute. Modern Intl exposes Intl.RelativeTimeFormat for the relative side, also specified in ECMA-402.

The non-obvious trap with relative time: tense across DST. If you compute "24 hours ago" and a DST transition fell between then and now, the wall-clock difference is 23 or 25 hours, but the instant difference is still 24. Always compute relative from instants (epoch math), never from wall-clock strings, and the problem goes away.

Calendar week numbers

Week numbers look simple and are not. There are at least three conventions in serious use and they disagree by up to a week at the boundaries.

ISO 8601 weeks start on Monday. Week 1 of a year is the week containing the first Thursday of that calendar year — equivalently, the week containing January 4. This makes every ISO week unambiguously belong to exactly one ISO year, but the ISO year can differ from the calendar year by one for a few days at the start or end. December 31 sometimes falls in week 1 of the next ISO year; January 1 sometimes falls in week 52 or 53 of the previous ISO year. The pattern repeats every six years for most cases, every eleven years for leap-year edges. The standard is YYYY-Www-d (e.g. 2026-W22-5 for Friday 29 May 2026).

US weeks typically start on Sunday and include any week that contains January 1, so US week 1 of 2026 starts on Sunday December 28, 2025. This is the convention you see in most US-locale calendar apps, Excel's default WEEKNUM, and the %U token in strftime. It is fine for local use and miscounts when interpreted as ISO.

Middle Eastern weeks start on Saturday in some countries (Saudi Arabia, the UAE before 2022) and on Sunday in others. Calendar systems used in Iran (Persian/Jalali), Israel (Hebrew), and Saudi Arabia (Hijri) have their own week structures that occasionally surface in business contexts.

The JavaScript trap worth knowing: Date.prototype.getFullYear() returns the calendar year, not the ISO year. If you compute the ISO week and pair it with getFullYear(), you will sometimes get a string like "2026-W01" for December 30, 2025 — wrong by one year. Always compute the ISO year alongside the ISO week.

If you are doing this once and want a quick reference, the week number tool shows ISO and US week numbers side by side for any date, including the year-boundary edge cases.

Anti-patterns to avoid

Every working developer has written one of these. The point of having a style guide is to recognize them in code review before they ship.

• MM/DD/YYYY in APIs. The string 03/04/2026 means March 4 to a US reader, April 3 to a UK reader, and "invalid format" to most strict parsers. If your API accepts or returns this string, you have a bug waiting on the wrong locale to be sent. The fix is ISO 8601 — there is no ambiguity in 2026-03-04.
• "Yesterday/today/tomorrow" stored in a database. These words depend on the current moment to be meaningful. Storing them is storing a relative reference whose anchor moves. Compute relative on display, store the absolute date.
• Two-digit years. 05/29/26 still appears in spreadsheets, on banking forms, and in legacy data feeds. Y2K is a quarter century old and the problem keeps coming back because the format keeps being used. If you have any code that infers the century from a 2-digit year, the cutoff (1929? 1949? 1969?) varies by language and will eventually bite. Reject the input or normalize to four digits at the boundary.
• Local time without offset on the wire. The string 2026-05-29T15:43:00 with no offset is half a timestamp. The receiver has to guess the zone, which they will do wrong. RFC 3339 forbids this for a reason; treat it as a hard error in your API validators.
• Stripping the timezone on data ingest. An ETL pipeline that parses 2026-05-29T15:43:00+02:00 as "2026-05-29 15:43" in Europe and writes it as a naive timestamp to the warehouse has lost two hours of information that nobody can recover. Always store the UTC instant; if the local context matters (it usually does), store the IANA zone alongside.
• Floating times in calendar events. iCalendar (RFC 5545) defines a "floating time" that has no timezone and is meant to be interpreted in the viewer's local zone — useful for events like "take your medication at 9 AM" that should always be 9 AM wherever you are. Almost everyone using it actually wanted a fixed-zone recurring event, and the result is meetings that silently jump an hour on DST day. Use TZID with an IANA zone unless floating is exactly what you mean.
• Time zone abbreviations on the wire. "3 PM EST" is not parseable: there are at least three valid EST/EDT interpretations, IST means three different countries, and BST means two. Use a numeric offset (-05:00) or an IANA name (America/New_York) — never the three-letter abbreviation. The time zone abbreviations reference covers the full set of clashes.
• Date math by subtracting strings. "2026-05-29" - "2026-05-22" is not 7. Parse to a Date or Temporal object first, then subtract. The fact that you can sometimes get away with naive string subtraction in spreadsheets is part of what makes this anti-pattern durable.

Quick reference

Filename and URL conventions

# Filenames: sortable, no separators, UTC.
backup_20260529_154300.tar.gz
log_20260529T154300Z.json
photo-IMG_20260529_154300.heic

# Never:
backup_2026-05-29_3-43pm.tar.gz       # mixed separators, 12h, no offset
backup_29-05-2026.tar.gz              # locale-ambiguous order
backup_May29.tar.gz                   # not sortable, no year

Format to parser

Preferred format by language standard library

Related Zeitful tools

One-purpose tools for the conversions you actually do during the day:

• Date format converter — round-trip between ISO 8601, RFC 3339, epoch, and common strftime patterns
• Epoch converter — seconds, milliseconds, microseconds, nanoseconds, in any IANA zone
• Week number — ISO and US week numbers side by side, with year-boundary edge cases
• Time zone converter — IANA-aware time conversion across cities and offsets

FAQ