ISO Datetime with Microseconds (no timezone)
datetime.timestamp.iso_microsecondswas datetime.datetime in v1ISO 8601-like format with microsecond precision but no timezone indicator. Common in database exports and log files.
Worked samples
DuckDB 1.5.5 · finetype 0.6.64
| Value | As TIMESTAMP |
|---|---|
| 2024-01-15T10:30:00.123456 | 2024-01-15 10:30:00.123456 |
| 2019-12-31T23:59:59.000000 | 2019-12-31 23:59:59 |
- %Y
- four-digit year
- %m
- month, two digits
- %d
- day of month, two digits
- %H
- hour, 24-hour clock
- %M
- minutes
- %S
- seconds
- %f
- fractional seconds
Shape
^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}$| Value | Length | Shape |
|---|---|---|
| 2024-01-15T10:30:00.123456 | 26 | matches |
| 2019-12-31T23:59:59.000000 | 26 | matches |
| 2024-01-15T10:30:00.12345 | 25 | rejected: one character short |
Background
Origin
ISO 8601 was first published as ISO 8601:1988 in June 1988.[1] The revision ISO 8601-1:2019 was issued in February 2019.[1] ISO 8601 combines a date and a time using the letter T as a delimiter.[1] ISO 8601 allows a decimal fraction to be added to the lowest order time element present.[1] ISO 8601 leaves the number of decimal places to be agreed by the communicating parties.[1] Under ISO 8601, a time given with no UTC relation information is assumed to be in local time.[1] Read the six fractional digits as a habit of the system that wrote the value, not a rule of ISO 8601 itself. (Meridian's view)
Use
Python's datetime.isoformat() returns YYYY-MM-DDTHH:MM:SS.ffffff when the microsecond is not 0.[2] Python's datetime.isoformat() returns YYYY-MM-DDTHH:MM:SS, with no fraction, when the microsecond is 0.[2] Python's datetime.isoformat() appends a UTC offset only when utcoffset() does not return None.[2] Passing timespec='microseconds' to Python's isoformat() includes the full time in HH:MM:SS.ffffff format.[2] If a column mixes this shape with values that stop at the seconds, suspect a Python program using the default isoformat() and parse both shapes. (Meridian's view)
In Python's strftime(), the %f directive is the microsecond as a decimal number, zero-padded to 6 digits.[2] With Python's strptime(), the %f directive accepts from one to six digits and zero pads on the right.[2]
PostgreSQL's timestamp without time zone type has a resolution of 1 microsecond.[3] PostgreSQL accepts the T separator on input but uses a space rather than T on output.[3] PostgreSQL silently ignores any time zone indication in a value determined to be timestamp without time zone.[3] DuckDB describes its TIMESTAMP type as a naive timestamp with microsecond precision.[4] DuckDB lists DATETIME as an alias of TIMESTAMP.[4] A value with no offset says nothing about which clock it was read from, so find the zone in the source's documentation before you compare it with anything else, and do not assume UTC. (Meridian's view)
MDN states that the timezone of toISOString() output is always UTC, as denoted by the suffix Z.[5]
Sources
- [1]ISO 8601 - Wikipediasecondary source
- [2]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
- [3]8.5. Date/Time Typesdocumentation
- [4]Timestamp Typesdocumentation
- [5]Date.prototype.toISOString() - JavaScript | MDNdocumentation
Detection notes
v1 name: datetime.datetime. Distinct from iso_8601_microseconds (has no trailing Z).
Use it
$ finetype infer -i "2024-01-15T10:30:00.123456" --mode column
→ datetime.timestamp.iso_microseconds
SELECT ft_infer('2024-01-15T10:30:00.123456');
-- → 'datetime.timestamp.iso_microseconds'strptime({col}, '%Y-%m-%dT%H:%M:%S.%f')
-- Format: %Y-%m-%dT%H:%M:%S.%fSchema
https://meridian.online/schemas/datetime.timestamp.iso_microseconds · Frictionless type datetime · %Y-%m-%dT%H:%M:%S.%f
finetype taxonomy datetime.timestamp.iso_microseconds -o json-schema