Postgres: global setting to always return TIMESTAMPTZ in exactly the same format? i.e.: 6 digits of sub-seconds + 4 digits of timezone offset

Question

Postgres will return TIMESTAMPTZ values using inconsistent formats, depending on how many zeros are after the .

See these examples:

2020-10-24 13:21:05+10
2020-10-24 13:21:05.1+10
2020-10-24 13:21:05.12+10
2020-10-24 13:21:05.123+10
2020-10-24 13:21:05.1234+10
2020-10-24 13:21:05.12345+10
2020-10-24 13:21:05.123456+10

It also does the same with the offset, e.g.

2020-10-24 13:21:05.123456+10
2020-10-24 13:21:05.123456+1030

Is there any way to have postgres just return the exact same format at all times? i.e. With 6 digits after the . for sub-seconds, and 4 digits for the timezone offset?

...but without actually needing to edit every query to manually format the output. I'm after a database/connection/session/global setting that does it for everything.

I came across the DateStyle setting - it doesn't seem to let me control this.

score 0 · Answer 1 · answered Oct 24 '20 at 05:00

For this case you need to use explicit formatting:

postgres=# select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.USTZHTZM');
┌─────────────────────────────────┐
│             to_char             │
╞═════════════════════════════════╡
│ 2020-10-24 06:59:18.272696+0200 │
└─────────────────────────────────┘
(1 row)

Postgres: global setting to always return TIMESTAMPTZ in exactly the same format? i.e.: 6 digits of sub-seconds + 4 digits of timezone offset

1 Answers1