MeridianMERIDIAN

RFC 3339 (Space Separator)

datetime.timestamp.rfc_3339

Internet datetime format, a profile of ISO 8601 that allows a space separator between date and time (RFC 3339 §5.6). Used in Atom feeds, JSON APIs, and modern web services. Distinguished from iso_8601_offset by the space separator: RFC 3339 uses space, ISO 8601 requires T. Disambiguation: Tier 2 timestamp model detects T vs space.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMPTZ
2024-01-15 10:30:00+00:002024-01-15 10:30:00+00
2019-12-31 23:59:59-05:002020-01-01 04:59:59+00
2023-06-01 12:00:00+09:002023-06-01 03:00:00+00
2024-03-15 10:30:00Z2024-03-15 10:30:00+00
%Y
four-digit year
%m
month, two digits
%d
day of month, two digits
%H
hour, 24-hour clock
%M
minutes
%S
seconds
%z
UTC offset
Standard

Shape

Pattern
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(?:\.\d+)? ?(?:[Zz]|[+-]\d{2}:?\d{2})$
ValueLengthShape
2024-01-15 10:30:00+00:0025matches
2019-12-31 23:59:59-05:0025matches
2023-06-01 12:00:00+09:0025matches
2024-03-15 10:30:00Z20matches
2024-01-15 10:30:00+00:024rejected: one character short

Background

Origin

RFC 3339, Date and Time on the Internet: Timestamps, was published in July 2002.[1] RFC 3339 was written by G. Klyne of Clearswift Corporation and C. Newman of Sun Microsystems.[1] RFC 3339 is a Standards Track document.[1] RFC 3339 defines a date and time format for use in Internet protocols that is a profile of the ISO 8601 standard.[1] RFC 3339 focuses on one common usage: timestamps for Internet protocol events.[1] RFC 3339 assumes that every time it expresses has a stated relationship, an offset, to Coordinated Universal Time.[1] RFC 3339 gives the date format 10/11/1996 as an example of one completely unsuitable for global interchange, because it is interpreted differently in different countries.[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.[1] FineType's registry labels the space-separated form datetime.timestamp.rfc_3339.[2] FineType's registry labels the T-separated form iso_8601_offset.[2]

RFC 3339 only allows hours from 00 to 23, although ISO 8601 permits the hour to be 24.[1] RFC 3339 allows the seconds value 60 at the end of a month in which a leap second occurs.[1]

Use

PostgreSQL accepts the T separator on input, but on output it uses a space rather than T.[3] PostgreSQL's documentation says the space is for readability and for consistency with RFC 3339 as well as some other database systems.[3]

Python's datetime.isoformat takes an optional one-character separator, which defaults to T, placed between the date and time portions of the result.[4] Python's documentation shows isoformat(' ') producing 2002-12-25 00:00:00-06:39.[4]

RFC 3339 says that applications generating the format should use upper case letters.[1] DuckDB's %z format specifier reads a time offset from UTC in the form ±HH:MM, ±HHMM, or ±HH.[5] When one column mixes T and space separators, normalise the separator first rather than parsing it as two formats. (Meridian's view) Load the column into a timestamp type that keeps the offset; dropping the offset turns an instant into a wall-clock reading that can no longer be compared across sources. (Meridian's view)

Sources

  1. [1]Date and Time on the Internet: Timestampsstandard
  2. [2]finetype/labels/definitions_datetime.yaml at main · meridian-online/finetype · GitHubFineType
  3. [3]PostgreSQL: Documentation: 18: 8.5. Date/Time Typesdocumentation
  4. [4]datetime — Basic date and time typesdocumentation
  5. [5]Date Format Functions – DuckDBdocumentation

Detection notes

RFC 3339 §5.6 allows both T and space as date/time separator. For classification purposes, space-separated = rfc_3339, T-separated = iso_8601_offset. This enables Tier 2 disambiguation via a simple character check. format_string_alt handles the Z (UTC) timezone indicator; primary format_string handles numeric offsets. DuckDB %z handles +HH:MM but not Z, hence the alt format. The transform casts instead, after upper-casing z and closing up a space before the offset or Z. The published format strings do not parse a fraction of a second, a lower-case z or a space before the offset or Z; those cast through the transform. Graded: a fraction and a lower-case z conform, as RFC 3339 allows them; noted are an offset without a colon (+0530) and a space before the offset or Z.

Use it

CLI · DuckDB
$ finetype infer -i "2024-01-15 10:30:00+00:00" --mode column
→ datetime.timestamp.rfc_3339

SELECT ft_infer('2024-01-15 10:30:00+00:00');
-- → 'datetime.timestamp.rfc_3339'
Cast expression
CAST(regexp_replace(upper({col}), ' ([+-]|Z)', '\1') AS TIMESTAMPTZ)
-- Format: %Y-%m-%d %H:%M:%S%z

Schema

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

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

Type Registry