# SQL Timestamp with Microseconds and Offset

Key: `datetime.timestamp.sql_microseconds_offset`

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

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

Format: `%Y-%m-%d %H:%M:%S.%f%z`

Samples: `2024-01-15 14:30:00.123456+00:00`, `2023-12-31 23:59:59.000000-05:00`, `2024-06-01 08:15:30.789012+05:30`

## Use it

Shell

```shell
finetype infer -i '2024-01-15 14:30:00.123456+00:00' --mode column
# → datetime.timestamp.sql_microseconds_offset
```

DuckDB

```sql
SELECT ft_infer('2024-01-15 14:30:00.123456+00:00');
-- → 'datetime.timestamp.sql_microseconds_offset'
```

Cast template: {col} is your column

```sql
strptime({col}, '%Y-%m-%d %H:%M:%S.%f%z')
-- Format: %Y-%m-%d %H:%M:%S.%f%z
```

## 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]](https://www.rfc-editor.org/rfc/rfc3339) 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]](https://www.rfc-editor.org/rfc/rfc3339) 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]](https://www.rfc-editor.org/rfc/rfc3339)

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

### 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]](https://www.postgresql.org/docs/current/datatype-datetime.html)

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

When used with strptime, Python's %f directive accepts from one to six digits and zero pads on the right.[[3]](https://docs.python.org/3/library/datetime.html) 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. [RFC 3339: Date and Time on the Internet: Timestamps](https://www.rfc-editor.org/rfc/rfc3339) — standard
2. [PostgreSQL: Documentation: 18: 8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html) — documentation
3. [datetime — Basic date and time types](https://docs.python.org/3/library/datetime.html) — documentation

## How it's detected

PostgreSQL TIMESTAMPTZ default output. Distinct from iso\_8601\_micros\_offset by space (not T) separator.