MeridianMERIDIAN

ISO with Space Separator and Z

datetime.timestamp.iso_space_zulu

RFC 3339 variant using space instead of T separator with UTC Z indicator. SQLite datetime('now') output and some cloud service log formats.

Operations on this type: Parse a date or time

Try a value

ISO with Space Separator and Z's rule accepts it.

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

Use it

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

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs TIMESTAMP
2024-01-15 14:30:00Z2024-01-15 14:30:00
2023-12-31 23:59:59Z2023-12-31 23:59:59
2024-06-01 00:00:00Z2024-06-01 00: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
Standard

Pattern

Regular expression
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}Z$
ValueLengthShape
2024-01-15 14:30:00Z20matches
2023-12-31 23:59:59Z20matches
2024-06-01 00:00:00Z20matches
2024-01-15 14:30:0019rejected: one character short

Similar types

Going the other way, this type's own rule accepts 1 of 4 samples of RFC 3339 (Space Separator).

These types are compared on one page: ISO 8601 vs RFC 3339 timestamps.

Background

Origin

RFC 3339, Date and Time on the Internet: Timestamps, was published in July 2002 by G. Klyne and C. Newman.[1] RFC 3339 defines a date and time format for Internet protocols as a profile of the ISO 8601 standard.[1] RFC 3339's grammar joins the full date and the full time with the letter T.[1] A note in RFC 3339 says applications may choose, for the sake of readability, to separate the full date and full time with a space character instead.[1] ISO 8601 itself does not allow a space between the date and time parts.[2]

RFC 3339 defines Z as a suffix that, applied to a time, denotes a UTC offset of 00:00.[1] The Z suffix in ISO 8601 times is sometimes called Zulu time.[2] RFC 3339 says a Z or +00:00 offset implies that UTC is the preferred reference point for the time, which differs from an offset of -00:00, meaning the UTC time is known but the local offset is not.[1] RFC 3339 permits a lower-case z but says applications that generate the format should use upper-case letters.[1]

Use

Elixir's DateTime.to_string renders a UTC datetime in this shape, with 2000-02-29 23:00:07Z as its documented example.[3] Elixir's DateTime.to_iso8601 writes the same UTC moment with a T separator, as 2000-02-29T23:00:07Z.[3] A file built from one Elixir application can therefore hold both spellings of the same instant, depending on which function wrote each column. (Meridian's view)

SQLite's date and time functions accept a time value followed by a timezone indicator of the form [+-]HH:MM or just Z.[4] SQLite's documentation says its date and time functions use UTC internally, so the Z suffix is a no-op.[4] Python's datetime.fromisoformat documentation says the T separator may be replaced by any single unicode character.[5] Python's datetime.fromisoformat documentation gives 2011-11-04T00:05:23Z as an example that parses to a datetime in UTC.[5]

The Z is the whole value of this format: it is the only thing that tells you the clock reading is UTC rather than some unstated local time. (Meridian's view) If your parser matches the Z as a literal and returns a timestamp without a time zone, record that the column is UTC, or convert it to a time-zone-aware type straight away, so the meaning is not lost at the next join. (Meridian's view)

Sources

  1. [1]RFC 3339: Date and Time on the Internet: Timestampsstandard
  2. [2]ISO 8601secondary source
  3. [3]DateTime (Elixir v1.20.4)documentation
  4. [4]Date And Time Functionsdocumentation
  5. [5]datetime — Basic date and time typesdocumentation

How it's detected

RFC 3339 permits space instead of T. SQLite datetime('now'), AWS CloudWatch, GCP logs.

Schema

https://meridian.online/schemas/datetime.timestamp.iso_space_zulu · Frictionless type datetime · %Y-%m-%d %H:%M:%SZ

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

Aliases

sqlite_utc

Type Registry