MeridianMERIDIAN

ISO Datetime with Microseconds (no timezone)

datetime.timestamp.iso_microseconds

ISO 8601-like format with microsecond precision but no timezone indicator. Common in database exports and log files.

Operations on this type: Parse a date or time

Try a value

ISO Datetime with Microseconds (no timezone)'s rule accepts it.

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

Use it

Shell
finetype infer -i '2024-01-15T10:30:00.123456' --mode column
# → datetime.timestamp.iso_microseconds
DuckDB
SELECT ft_infer('2024-01-15T10:30:00.123456');
-- → 'datetime.timestamp.iso_microseconds'
Cast template: {col} is your column
strptime({col}, '%Y-%m-%dT%H:%M:%S.%f')
-- Format: %Y-%m-%dT%H:%M:%S.%f

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs TIMESTAMP
2024-01-15T10:30:00.1234562024-01-15 10:30:00.123456
2019-12-31T23:59:59.0000002019-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
Standard

Pattern

Regular expression
^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}$
ValueLengthShape
2024-01-15T10:30:00.12345626matches
2019-12-31T23:59:59.00000026matches
2024-01-15T10:30:00.1234525rejected: 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. [1]ISO 8601 - Wikipediasecondary source
  2. [2]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
  3. [3]8.5. Date/Time Typesdocumentation
  4. [4]Timestamp Typesdocumentation
  5. [5]Date.prototype.toISOString() - JavaScript | MDNdocumentation

How it's detected

Distinct from iso_8601_microseconds (has no trailing Z).

Schema

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

Type Registry