Why are the default formats different here? ( Two different implementations of ISO 8601 )

user_5785328 0
Viewed 48

When storing timestamps in jsonb I realized the format used when putting current_timestamp and now() into a jsonb was different than when I used them directly. What is going on here? Although both are ISO8601 formats, I would prefer that they both use the same formatting. Why is there two different defaults?

select version();
select now();
select current_timestamp;
select jsonb_build_object('timestamptz', current_timestamp);
select jsonb_build_object('timestamptz', now());

| version                                                                                                |
| :----------------------------------------------------------------------------------------------------- |
| PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |

| now                           |
| :---------------------------- |
| 2022-07-14 14:13:42.696612+01 |

| current_timestamp             |
| :---------------------------- |
| 2022-07-14 14:13:42.696612+01 |

| jsonb_build_object                                  |
| :-------------------------------------------------- |
| {"timestamptz": "2022-07-14T14:13:42.696612+01:00"} |

| jsonb_build_object                                  |
| :-------------------------------------------------- |
| {"timestamptz": "2022-07-14T14:13:42.696612+01:00"} |

db<>fiddle here

postgresql
2 Answers

PostgreSQL encodes dates and timestamps in a special way for JSON. See the comment at the start of JsonEncodeDateTime in src/backend/utils/adt/json.c:

/*
 * Encode 'value' of datetime type 'typid' into JSON string in ISO format using
 * optionally preallocated buffer 'buf'.  Optional 'tzp' determines time-zone
 * offset (in seconds) in which we want to show timestamptz.
 */

This seems to be justified by W3, but I don't know enough about JSON to know what the authoritative standard is and where to find it.

user_64643080

The format of a timestamp literal (and by extension, default conversion of a timestamp to string), is defined in ISO/IEC 9075-2:2016 (SQL:2016) section 5.3 <literal> to have a space as the separator between date and time:

<timestamp literal> ::=
  TIMESTAMP <timestamp string>

<timestamp string> ::=
  <quote> <unquoted timestamp string> <quote>

<unquoted timestamp string> ::=
  <unquoted date string> <space> <unquoted time string>

Both <unquoted date string> and <unquoted time string> follow the formats of ISO-8601.

So, for standards compliance, interoperability, and backwards compatibility, this format has to be used in SQL itself.

Likely, when PostgreSQL added JSON, this historical baggage of the SQL standard was left out, and instead ISO-8601 was followed, as this is also used by a lot of other JSON serialization solutions, which resulted in using T as the separator.

user_4668620
Related