MeridianMERIDIAN

SQL Timestamp with Microseconds

datetime.timestamp.sql_microseconds

Space-separated date and time with 6-digit microsecond precision. Default output of Python str(datetime.now()) and PostgreSQL TIMESTAMP. Distinct from iso_8601_microseconds by space (not T) separator.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
2024-01-15 14:30:00.1234562024-01-15 14:30:00.123456
2019-12-31 23:59:59.0000002019-12-31 23:59:59
2023-06-01 08:15:30.7890122023-06-01 08:15:30.789012
%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

Shape

Pattern
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6}$
ValueLengthShape
2024-01-15 14:30:00.12345626matches
2019-12-31 23:59:59.00000026matches
2023-06-01 08:15:30.78901226matches
2024-01-15 14:30:00.1234525rejected: one character short

Background

Origin

RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.[1] RFC 3339 notes that ISO 8601 defines date and time separated by "T".[1] RFC 3339 adds that applications may choose, for the sake of readability, to separate the full date and the full time with a space character instead.[1]

PostgreSQL's documentation states that the SQL standard requires the use of the ISO 8601 format for date and time output.[2] PostgreSQL accepts the uppercase T separator on input but writes a space rather than T on output.[2] PostgreSQL's documentation gives readability and consistency with RFC 3339, as well as with some other database systems, as the reasons for the space.[2] PostgreSQL's timestamp type has a resolution of 1 microsecond.[2] The MySQL reference manual describes the standard SQL default fractional seconds precision as 6.[3] A space-separated timestamp with six fractional digits is best read as the readable variant RFC 3339 allows, written out to the full microsecond. (Meridian's view)

Use

Python's documentation states that, for a datetime instance d, str(d) is equivalent to d.isoformat(' ').[4] In Python's strftime, the %f directive is the microsecond as a decimal number, zero-padded to 6 digits.[4]

MySQL's DATETIME and TIMESTAMP values can include a trailing fractional seconds part in up to microseconds (6 digits) precision.[5] MySQL's default fractional seconds precision, when none is given, is 0.[3] PostgreSQL allows a precision of 0 to 6 fractional digits for its timestamp type.[2] Load this format into a column declared with microsecond precision, such as DATETIME(6) or timestamp(6), so the sixth digit survives the trip. (Meridian's view)

When Python's strptime parses %f, the directive accepts from one to six digits and zero pads on the right.[4] Python's datetime.fromisoformat allows the T separator to be replaced by any single unicode character.[4] The value carries no time zone or offset, so find out from the producing system which clock it was written in before you compare it with UTC data. (Meridian's view) Read the six digits as microseconds, not milliseconds, and keep all of them rather than truncating to three. (Meridian's view)

Sources

  1. [1]RFC 3339: Date and Time on the Internet: Timestampsstandard
  2. [2]PostgreSQL Documentation: 8.5. Date/Time Typesdocumentation
  3. [3]MySQL 8.4 Reference Manual :: 13.2.6 Fractional Seconds in Time Valuesdocumentation
  4. [4]datetime — Basic date and time typesdocumentation
  5. [5]MySQL 8.4 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Typesdocumentation

Detection notes

New in format coverage expansion. High prevalence — Python str(datetime.now()) default.

Use it

CLI · DuckDB
$ finetype infer -i "2024-01-15 14:30:00.123456" --mode column
→ datetime.timestamp.sql_microseconds

SELECT ft_infer('2024-01-15 14:30:00.123456');
-- → 'datetime.timestamp.sql_microseconds'
Cast expression
strptime({col}, '%Y-%m-%d %H:%M:%S.%f')
-- Format: %Y-%m-%d %H:%M:%S.%f

Schema

https://meridian.online/schemas/datetime.timestamp.sql_microseconds · Frictionless type datetime · %Y-%m-%d %H:%M:%S.%f

finetype taxonomy datetime.timestamp.sql_microseconds -o json-schema

Aliases

sql_micro

Type Registry