Which format should I store timestamps in?

For interchange, RFC 3339 in UTC with the precision you need. For storage, a 64-bit integer with a documented unit (milliseconds or microseconds), or your database's native timestamptz type.

What is the LDAP / Active Directory timestamp?

Attributes such as lastLogonTimestamp and pwdLastSet use Windows FILETIME: 100-nanosecond intervals since 1601-01-01 UTC. The value 0 and the maximum 9223372036854775807 mean "never".

Do UUIDv4 values contain a timestamp?

No. Version 4 is random. Only versions 1, 6 and 7 embed a time, and the converter explains when a UUID has none.

Timestamp Format Cheat Sheet: epochs, units and date formats compared

Unix seconds	1700000000Exact; fraction shown when present
Unix seconds (integer, floor)	1700000000
Unix milliseconds	1700000000000
Unix microseconds	1700000000000000
Unix nanoseconds	1700000000000000000
Unix seconds (hex)	0x6553F100

Unix seconds

1700000000Exact; fraction shown when present

Unix seconds (integer, floor)

1700000000

Unix milliseconds

1700000000000

Unix microseconds

1700000000000000

Unix nanoseconds

1700000000000000000

Unix seconds (hex)

0x6553F100

ISO 8601 (UTC)	2023-11-14T22:13:20.000000000Z
RFC 3339 (UTC)	2023-11-14T22:13:20.000000000+00:00
RFC 9557 (offset + zone)	2023-11-14T22:13:20.000000000+00:00[UTC]
RFC 2822 (UTC)	Tue, 14 Nov 2023 22:13:20 +0000
HTTP-date (IMF-fixdate)	Tue, 14 Nov 2023 22:13:20 GMTAlways GMT; whole seconds
SQL TIMESTAMP (UTC)	2023-11-14 22:13:20.000000Microsecond precision max (PostgreSQL/MySQL)
SQL TIMESTAMP (UTC wall time)	2023-11-14 22:13:20.000000No zone stored: ambiguous without context
PostgreSQL timestamptz literal	2023-11-14 22:13:20.000000+00:00
ISO week date (UTC)	2023-W46-2
Ordinal date (UTC)	2023-318
Readable (UTC)	Tuesday, 14 November 2023, 22:13:20 UTCCanonical English; not locale-dependent

ISO 8601 (UTC)

2023-11-14T22:13:20.000000000Z

RFC 3339 (UTC)

2023-11-14T22:13:20.000000000+00:00

RFC 9557 (offset + zone)

2023-11-14T22:13:20.000000000+00:00[UTC]

RFC 2822 (UTC)

Tue, 14 Nov 2023 22:13:20 +0000

HTTP-date (IMF-fixdate)

Tue, 14 Nov 2023 22:13:20 GMTAlways GMT; whole seconds

SQL TIMESTAMP (UTC)

2023-11-14 22:13:20.000000Microsecond precision max (PostgreSQL/MySQL)

SQL TIMESTAMP (UTC wall time)

2023-11-14 22:13:20.000000No zone stored: ambiguous without context

PostgreSQL timestamptz literal

2023-11-14 22:13:20.000000+00:00

ISO week date (UTC)

2023-W46-2

Ordinal date (UTC)

2023-318

Readable (UTC)

Tuesday, 14 November 2023, 22:13:20 UTCCanonical English; not locale-dependent

JavaScript new Date(ms)	new Date(1700000000000)
Java Instant	Instant.ofEpochSecond(1700000000L, 0L)Nanosecond precision
Python datetime (UTC)	datetime(2023, 11, 14, 22, 13, 20, 0, tzinfo=timezone.utc)Microsecond precision
.NET DateTime ticks	638355968000000000100-ns ticks since 0001-01-01 (DateTime.Ticks with Kind=Utc).
Windows FILETIME / LDAP	133444736000000000100-ns intervals since 1601 UTC. Active Directory lastLogon, pwdLastSet, accountExpires.
Chrome / WebKit time	13344473600000000Microseconds since 1601 UTC (Chrome history, cookies).
Apple Cocoa / Core Data	721692800NSDate timeIntervalSinceReferenceDate, Core Data, iOS/macOS plists.
Mac HFS+ seconds	3782844800Classic Mac OS / HFS+ volume dates (seconds since 1904).
NTP seconds (era 0)	3908988800NTP era 0 wraps on 2036-02-07; era numbers are not encoded in the 32-bit field.
Excel serial 1900 (UTC wall time) rounded	45244.9259259259Days since 1900-01-00 with the Lotus 1-2-3 leap-year bug (serial 60 = fictitious 1900-02-29). No time zone.
Excel serial 1904 (UTC wall time) rounded	43782.9259259259Older Mac Excel workbooks. Days since 1904-01-01. No time zone.
Julian Date rounded	2460263.4259259259Days since noon UT, 24 Nov 4714 BC. Computed from UTC, so it ignores the UT1/TT distinction.
Modified Julian Date rounded	60262.9259259259JD - 2400000.5. Computed from UTC.
GPS seconds	1384035218Seconds since the GPS epoch, counting leap seconds (GPS = TAI - 19 s). Uses the bundled leap-second table.
TAI (TAI-UTC = 37 s)	2023-11-14T22:13:57.000000000 TAIFrom the bundled leap-second table

JavaScript new Date(ms)

new Date(1700000000000)

Java Instant

Instant.ofEpochSecond(1700000000L, 0L)Nanosecond precision

Python datetime (UTC)

datetime(2023, 11, 14, 22, 13, 20, 0, tzinfo=timezone.utc)Microsecond precision

.NET DateTime ticks

638355968000000000100-ns ticks since 0001-01-01 (DateTime.Ticks with Kind=Utc).

Windows FILETIME / LDAP

133444736000000000100-ns intervals since 1601 UTC. Active Directory lastLogon, pwdLastSet, accountExpires.

Chrome / WebKit time

13344473600000000Microseconds since 1601 UTC (Chrome history, cookies).

Apple Cocoa / Core Data

721692800NSDate timeIntervalSinceReferenceDate, Core Data, iOS/macOS plists.

Mac HFS+ seconds

3782844800Classic Mac OS / HFS+ volume dates (seconds since 1904).

NTP seconds (era 0)

3908988800NTP era 0 wraps on 2036-02-07; era numbers are not encoded in the 32-bit field.

Excel serial 1900 (UTC wall time) rounded

45244.9259259259Days since 1900-01-00 with the Lotus 1-2-3 leap-year bug (serial 60 = fictitious 1900-02-29). No time zone.

Excel serial 1904 (UTC wall time) rounded

43782.9259259259Older Mac Excel workbooks. Days since 1904-01-01. No time zone.

Julian Date rounded

2460263.4259259259Days since noon UT, 24 Nov 4714 BC. Computed from UTC, so it ignores the UT1/TT distinction.

Modified Julian Date rounded

60262.9259259259JD - 2400000.5. Computed from UTC.

GPS seconds

1384035218Seconds since the GPS epoch, counting leap seconds (GPS = TAI - 19 s). Uses the bundled leap-second table.

TAI (TAI-UTC = 37 s)

2023-11-14T22:13:57.000000000 TAIFrom the bundled leap-second table

Smallest UUIDv7 at this ms	018bcfe5-6800-7000-8000-000000000000Use as a range-query lower bound
Smallest ULID at this ms	01HF7YAT000000000000000000
Smallest Twitter / X Snowflake	1724551110456246272
Smallest Discord Snowflake	1174109840998400000
Smallest MongoDB ObjectId	6553f1000000000000000000

Smallest UUIDv7 at this ms

018bcfe5-6800-7000-8000-000000000000Use as a range-query lower bound

Smallest ULID at this ms

01HF7YAT000000000000000000

Smallest Twitter / X Snowflake

1724551110456246272

Smallest Discord Snowflake

1174109840998400000

Smallest MongoDB ObjectId

6553f1000000000000000000

Reading the table

Every example below is the same instant, 2023-11-14T22:13:20Z (Unix 1700000000), written in a different format. The epoch column is the moment each format counts from, and the unit column is what one step of the number means. Formats marked "no zone" store wall-clock time and need a time zone before they can become an instant. The values were generated by this site's conversion engine, and the unit tests check them against published reference values.

Timestamp formats for 2023-11-14T22:13:20Z
Format	Epoch	Unit	2023-11-14T22:13:20Z
Unix seconds	1970-01-01 UTC	1 s	1700000000
Unix milliseconds	1970-01-01 UTC	1 ms	1700000000000
Unix microseconds	1970-01-01 UTC	1 µs	1700000000000000
Unix nanoseconds	1970-01-01 UTC	1 ns	1700000000000000000
ISO 8601 / RFC 3339 (UTC)	text	any precision	2023-11-14T22:13:20Z
RFC 3339 with offset	text	any precision	2023-11-15T03:43:20.000+05:30
RFC 2822 (email)	text	1 s	Tue, 14 Nov 2023 22:13:20 +0000
HTTP-date (IMF-fixdate)	text, always GMT	1 s	Tue, 14 Nov 2023 22:13:20 GMT
SQL TIMESTAMP	text, no zone	1 µs	2023-11-14 22:13:20.000000
Windows FILETIME / LDAP	1601-01-01T00:00:00Z	100 ns	133444736000000000
.NET DateTime ticks	0001-01-01T00:00:00	100 ns	638355968000000000
Chrome / WebKit time	1601-01-01T00:00:00Z	1 µs	13344473600000000
Apple Cocoa / Core Data	2001-01-01T00:00:00Z	1 s	721692800
Mac HFS+ seconds	1904-01-01T00:00:00	1 s	3782844800
NTP seconds (era 0)	1900-01-01T00:00:00Z	1 s	3908988800
Excel serial (1900 system)	1899-12-30 (1900 system)	1 day (no zone)	45244.9259259259
Excel serial (1904 system)	1904-01-01	1 day (no zone)	43782.9259259259
Julian Date	-4713-11-24T12:00:00Z (proleptic Gregorian)	1 day	2460263.4259259259
Modified Julian Date	1858-11-17T00:00:00Z	1 day	60262.9259259259
GPS seconds	1980-01-06T00:00:00Z	1 s, counts leap seconds	1384035218
UUIDv7 (first 48 bits)	1970-01-01 UTC	1 ms	018bcfe5-6800-7000-8000-000000000000
ULID (first 10 chars)	1970-01-01 UTC	1 ms	01HF7YAT000000000000000000
Discord Snowflake (id >> 22)	2015-01-01 UTC	1 ms	1174109840998400000
Twitter / X Snowflake (id >> 22)	2010-11-04T01:42:54.657Z	1 ms	1724551110456246272
MongoDB ObjectId (first 4 bytes)	1970-01-01 UTC	1 s	6553f1000000000000000000

Leap seconds and GPS

Unix time, Windows FILETIME and .NET ticks all ignore leap seconds. GPS time does not: it runs ahead of UTC by the number of leap seconds since 1980, currently 18. TAI is a further 19 seconds ahead of GPS. Converting to or from GPS or TAI uses a bundled leap-second table. Results for dates after the table's expiry are labelled uncertain, because a future leap second (or the planned abolition of leap seconds) would change them.

Questions

Which format should I store timestamps in?: For interchange, RFC 3339 in UTC with the precision you need. For storage, a 64-bit integer with a documented unit (milliseconds or microseconds), or your database's native timestamptz type.
What is the LDAP / Active Directory timestamp?: Attributes such as lastLogonTimestamp and pwdLastSet use Windows FILETIME: 100-nanosecond intervals since 1601-01-01 UTC. The value 0 and the maximum 9223372036854775807 mean "never".
Do UUIDv4 values contain a timestamp?: No. Version 4 is random. Only versions 1, 6 and 7 embed a time, and the converter explains when a UUID has none.

Timestamp format cheat sheet

Reading the table

Leap seconds and GPS

Questions