# ISO 8601 with Timezone Offset

Key: `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.

Page: <https://meridian.online/docs/finetype/types/datetime/timestamp/iso_8601_offset>

Format: `%Y-%m-%dT%H:%M:%S%z`

Samples: `2024-01-15T10:30:00+05:00`, `2023-12-29T10:45:30-05:00`, `2024-06-15T12:00:00+00:00`

## Use it

Shell

```shell
finetype infer -i '2024-01-15T10:30:00+05:00' --mode column
# → datetime.timestamp.iso_8601_offset
```

DuckDB

```sql
SELECT ft_infer('2024-01-15T10:30:00+05:00');
-- → 'datetime.timestamp.iso_8601_offset'
```

Cast template: {col} is your column

```sql
CAST(regexp_replace(upper({col}), '([+-])([0-9]):([0-9]{2})$', '\10\2:\3') AS TIMESTAMPTZ)
-- Format: %Y-%m-%dT%H:%M:%S%z
```

## Background

### Origin

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

RFC 3339 defines a date and time format for Internet protocols that is a profile of the ISO 8601 standard.[[2]](https://www.rfc-editor.org/rfc/rfc3339) RFC 3339 calculates numeric offsets as local time minus UTC.[[2]](https://www.rfc-editor.org/rfc/rfc3339) Wikipedia notes that RFC 3339 deviates from ISO 8601 by allowing a zero offset to be written as -00:00.[[1]](https://en.wikipedia.org/wiki/ISO_8601)

### Use

RFC 3339 allows applications, for readability, to separate the date and time with a space instead of T.[[2]](https://www.rfc-editor.org/rfc/rfc3339) 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]](https://www.postgresql.org/docs/current/datatype-datetime.html) PostgreSQL silently ignores any time zone indication in a value it has determined to be timestamp without time zone.[[3]](https://www.postgresql.org/docs/current/datatype-datetime.html) DuckDB's TIMESTAMP WITH TIME ZONE does not store time zone information, despite its name.[[4]](https://duckdb.org/docs/current/sql/data_types/timestamp.html) DuckDB ignores offsets or time zone specifications in strings given to a WITHOUT TIME ZONE type.[[4]](https://duckdb.org/docs/current/sql/data_types/timestamp.html)

Python's strptime has accepted a colon between the hours and minutes of a %z offset since version 3.7.[[5]](https://docs.python.org/3/library/datetime.html) Python's strptime treats Z given to %z as identical to +00:00.[[5]](https://docs.python.org/3/library/datetime.html)

### 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]](https://docs.openaq.org/using-the-api/dates-datetimes)

## Sources

1. [ISO 8601 - Wikipedia](https://en.wikipedia.org/wiki/ISO_8601) — secondary source
2. [Date and Time on the Internet: Timestamps](https://www.rfc-editor.org/rfc/rfc3339) — standard
3. [8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html) — documentation
4. [Timestamp Types](https://duckdb.org/docs/current/sql/data_types/timestamp.html) — documentation
5. [datetime — Basic date and time types](https://docs.python.org/3/library/datetime.html) — documentation
6. [Dates, times and timezones](https://docs.openaq.org/using-the-api/dates-datetimes) — organisation

## 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).