MeridianMERIDIAN

ISO 8601 with Timezone Offset

datetime.timestamp.iso_8601_offset

ISO 8601 datetime with explicit timezone offset instead of Z. Preserves the original timezone information. The offset is a sign, an hour and optional minutes. The colon form (+05:30) or the hour alone (+05) is canonical; the form without a colon (+0530) and a one-digit hour (+5:30) are accepted with a note.

Operations on this type: Parse a date or time

Try a value

ISO 8601 with Timezone Offset's rule accepts it.

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

Use it

Shell
finetype infer -i '2024-01-15T10:30:00+05:00' --mode column
# → datetime.timestamp.iso_8601_offset
DuckDB
SELECT ft_infer('2024-01-15T10:30:00+05:00');
-- → 'datetime.timestamp.iso_8601_offset'
Cast template: {col} is your column
CAST(regexp_replace(upper({col}), '([+-])([0-9]):([0-9]{2})$', '\10\2:\3') AS TIMESTAMPTZ)
-- Format: %Y-%m-%dT%H:%M:%S%z

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs TIMESTAMPTZ
2024-01-15T10:30:00+05:002024-01-15 05:30:00+00
2023-12-29T10:45:30-05:002023-12-29 15:45:30+00
2024-06-15T12:00:00+00:002024-06-15 12: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
%z
UTC offset
Standard

Pattern

Regular expression
^\d{4}-\d{2}-\d{2}[Tt]\d{2}:\d{2}:\d{2}[+-](?:\d{2}(?::?\d{2})?|\d:\d{2})$
ValueLengthShape
2024-01-15T10:30:00+05:0025matches
2023-12-29T10:45:30-05:0025matches
2024-06-15T12:00:00+00:0025matches
2024-01-15T10:30:00+05:024rejected: one character short

Similar types

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

Background

Origin

The first edition of ISO 8601 was published as ISO 8601:1988 in June 1988.[1] ISO 8601:1988 unified and replaced a number of older ISO standards on date and time notation.[1] Under ISO 8601, a negative UTC offset describes a time zone west of the prime meridian, where civil time is behind UTC.[1] ISO 8601 does not permit a zero offset to be written with a negative sign, such as −00:00.[1]

RFC 3339 defines a date and time format for Internet protocols that is a profile of the ISO 8601 standard.[2] RFC 3339 calculates numeric offsets as local time minus UTC.[2] Wikipedia notes that RFC 3339 deviates from ISO 8601 by allowing a zero offset to be written as -00:00.[1]

Use

RFC 3339 allows applications, for readability, to separate the date and time with a space instead of T.[2] Expect the same instant to arrive as +00:00, Z or a space-separated variant from different systems, and accept all three before you call a column malformed. (Meridian's view)

PostgreSQL converts a timestamp with time zone input that carries an explicit time zone to UTC, and does not retain the stated time zone.[3] PostgreSQL silently ignores any time zone indication in a value it has determined to be timestamp without time zone.[3] DuckDB's TIMESTAMP WITH TIME ZONE does not store time zone information, despite its name.[4] DuckDB ignores offsets or time zone specifications in strings given to a WITHOUT TIME ZONE type.[4]

Python's strptime has accepted a colon between the hours and minutes of a %z offset since version 3.7.[5] Python's strptime treats Z given to %z as identical to +00:00.[5]

Where you'll meet it

The OpenAQ air-quality API returns each datetime with both a UTC value and a local value, the local one written with an offset such as 2019-07-11T14:00:00-06:00.[6]

Sources

  1. [1]ISO 8601 - Wikipediasecondary source
  2. [2]Date and Time on the Internet: Timestampsstandard
  3. [3]8.5. Date/Time Typesdocumentation
  4. [4]Timestamp Typesdocumentation
  5. [5]datetime — Basic date and time typesdocumentation
  6. [6]Dates, times and timezonesorganisation

How it's detected

The published format strings describe the upper-case-T, colon-offset form and do not parse lower-case t, an offset without a colon or a one-digit hour; those cast through the transform. Graded: lower-case t and the hour-only offset (+05) conform; noted are the offset without a colon (+0530) and a one-digit hour (+5:30).

Schema

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

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

Aliases

iso_8601_with_time_zone_name

Type Registry