CODEWITHSUNDEEP
postgresqltimestampiso8601
ffxsam
ffxsam

Reputation: 27763

Is there any way to get Postgres to use proper ISO 8601 timestamps globally and automatically?

As far as I can tell, Postgres 10.12 is not using proper ISO 8601 timestamps for my timestamptz columns: 2021-03-21 00:23:18.587565

Per ISO 8601:

A single point in time can be represented by concatenating a complete date expression, the letter "T" as a delimiter, and a valid time expression. For example, "2007-04-05T14:30". In ISO 8601:2004 it was permitted to omit the "T" character by mutual agreement as in "200704051430", but this provision was removed in ISO 8601-1:2019. Separating date and time parts with other characters such as space is not allowed in ISO 8601, but allowed in its profile RFC 3339. 1

Is there any way I can configure this globally, so I get timestamps in the YYYY-MM-DDThh:mm:ss.xxxZ format? My current workaround is querying like this every time I want a timestamp:

SELECT to_json(created_at) #>> '{}' AS created_at FROM items

This is quite tedious, and a global option would be much preferred.

Upvotes: 2

Views: 1999

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

There is no way in PostgreSQL to configure the string representation of timestamps beyond the datestyle parameter, which offers only a few fixed alternatives.

The best advice I can give you to use to_char with an appropriate format string.

Upvotes: 2

Related Questions