MeridianMERIDIAN

SQL Timestamp with Microseconds and Offset

datetime.timestamp.sql_microseconds_offset

PostgreSQL TIMESTAMPTZ output format. Space-separated date and time with 6-digit microseconds and full timezone offset.

Operations on this type: Parse a date or time

Try a value

Use it

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

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs TIMESTAMPTZ
2024-01-15 14:30:00.123456+00:002024-01-15 14:30:00.123456+00
2023-12-31 23:59:59.000000-05:002024-01-01 04:59:59+00
2024-06-01 08:15:30.789012+05:302024-06-01 02:45:30.789012+00
%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
%z
UTC offset
Standard

Pattern

Regular expression
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6}[+-]\d{2}:\d{2}$
ValueLengthShape
2024-01-15 14:30:00.123456+00:0032matches
2023-12-31 23:59:59.000000-05:0032matches
2024-06-01 08:15:30.789012+05:3032matches
2024-01-15 14:30:00.123456+00:031rejected: 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 applications may choose, for the sake of readability, to separate the full date and the full time with a space character instead of T.[1] RFC 3339's grammar writes a numeric offset as a plus or minus sign, two digits of hours, a colon and two digits of minutes.[1]

PostgreSQL's documentation says the SQL standard requires the use of the ISO 8601 format for date and time output.[2] PostgreSQL accepts the T separator on input but uses a space rather than T on output.[2] PostgreSQL's documentation gives the reason for the space as readability and consistency with RFC 3339 as well as some other database systems.[2] PostgreSQL timestamp types have a resolution of 1 microsecond.[2] PostgreSQL lets a timestamp column declare a precision p, the number of fractional digits retained in the seconds field, from 0 to 6.[2]

Use

PostgreSQL stores a timestamp with time zone value internally as UTC and does not retain the time zone originally stated in the input.[2]

In Python, str() of a datetime is equivalent to calling isoformat with a space as the separator.[3] Python's isoformat leaves out the fractional seconds entirely when the microsecond value is 0, unless a timespec such as 'microseconds' is given.[3]

When used with strptime, Python's %f directive accepts from one to six digits and zero pads on the right.[3] The space separator is the quickest way to tell this form from its T-separated ISO 8601 sibling, and it is worth normalising one to the other before loading both into the same column. (Meridian's view)

Sources

  1. [1]RFC 3339: Date and Time on the Internet: Timestampsstandard
  2. [2]PostgreSQL: Documentation: 18: 8.5. Date/Time Typesdocumentation
  3. [3]datetime — Basic date and time typesdocumentation

How it's detected

PostgreSQL TIMESTAMPTZ default output. Distinct from iso_8601_micros_offset by space (not T) separator.

Schema

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

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

Aliases

pg_timestamptz

Type Registry