ISO 8601 with Microseconds

Try a value

Reads it as 2024-03-04 05:06:07.123456

Use it

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

Pattern

Background

Origin

ISO 8601 is maintained by the International Organization for Standardization, which first released it in 1988.^[1] ISO 8601-1:2019 represents slightly updated contents of the previous ISO 8601:2004 standard.^[1] In ISO 8601, a combined date and time representation uses the letter T as the delimiter between the date and the time.^[1] ISO 8601 allows a decimal fraction to be added to the lowest order time element of a time representation.^[1] ISO 8601 sets no limit on the number of decimal places, which needs to be agreed to by the communicating parties.^[1] ISO 8601 marks a time in UTC by adding a Z directly after the time, without a space.^[1]

RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.^[2] RFC 3339 describes itself as a profile of the ISO 8601 standard for representation of dates and times using the Gregorian calendar.^[2] RFC 3339's grammar defines the fraction of a second, time-secfrac, as a full stop followed by one or more digits.^[2] RFC 3339 calls fractions of a second the one rarely used option in its format.^[2] RFC 3339 allows the T and Z characters to be written in lower case as t and z.^[2]

Use

Kubernetes defines a MicroTime type, described in its source as a version of Time with microsecond level precision.^[3] Kubernetes' RFC3339Micro layout is 2006-01-02T15:04:05.000000Z07:00.^[3] Kubernetes MicroTime values are converted to UTC before being formatted with the RFC3339Micro layout for JSON.^[3] In Go's time package, a decimal point followed by zeros in a layout prints a fractional second to the given number of decimal places.^[4] In Go's time package, a decimal point followed by nines in a layout prints a fractional second with trailing zeros removed.^[4] Go's RFC3339Nano layout is 2006-01-02T15:04:05.999999999Z07:00.^[4] Go's time package prints Z instead of an offset for the UTC zone when the sign in the layout is replaced with a Z.^[4]

Python's strftime directive %f writes the microsecond as a decimal number zero-padded to 6 digits.^[5] When used with Python's strptime, the %f directive accepts from one to six digits and zero pads on the right.^[5] Python's datetime.isoformat omits the fractional part when the microsecond is 0, unless a timespec such as microseconds is given.^[5] Python's datetime.isoformat writes a UTC datetime with the offset +00:00 rather than Z, as in the documentation's example 2019-05-18T15:17:00+00:00.^[5]

The timezone of JavaScript's toISOString output is always UTC, as denoted by the suffix Z.^[6] PostgreSQL's timestamp types have a resolution of 1 microsecond.^[7] PostgreSQL accepts the uppercase T between date and time on input, but uses a space rather than T on output.^[7] In a value determined to be timestamp without time zone, PostgreSQL silently ignores any time zone indication.^[7] Load these values into a time-zone-aware column, or parse the Z explicitly, so that the UTC marker is not quietly thrown away. (Meridian's view) Before loading, check whether the six digits carry real precision or are padding, since a column of .000000 fractions says less about timing than its width suggests. (Meridian's view)

Sources

1. [1]ISO 8601secondary source
2. [2]RFC 3339: Date and Time on the Internet: Timestampsstandard
3. [3]micro_time.godocumentation
4. [4]time package - time - Go Packagesdocumentation
5. [5]datetime — Basic date and time typesdocumentation
6. [6]Date.prototype.toISOString() - JavaScript | MDNdocumentation
7. [7]PostgreSQL: Documentation: 18: 8.5. Date/Time Typesdocumentation

Schema

Aliases