What defines the output format of a Postgres Timestamp

Skeeve has asked for the wisdom of the Perl Monks concerning the following question:

I'm using Mojolicious and Mojo::Pg to create an API.

When retrieving a timestamp from the database, without converting the timestamp TO_CHAR, I get something like this:

{
  "created" => "2020-09-22 10:16:39.307719+02"
}
[download]

Now I'm wondering where the output format is defined.

As I would always require all timestamps to be in a RFC3339 format, I would have to change each and every select statement to have a TO_CHAR with the proper format.

There must be a more practical way of defining the format for all timestamps at a central place, but i have no clue where.

One of the answers on Stackoverflow suggests that

The display is completely controlled by the SQL client, not by the server.

The SQL client here should be DBD::Pg.

But maybe that's the wrong place to search and the conversion of a timestamp to a string representation is done at a later stage e.g. when doing the encode_json?

My current attempts in finding the spot where to change the format were fruitless :(

I hope someone here can give me more ideas where to search.

s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Comment on What defines the output format of a Postgres Timestamp Select or Download Code

Replies are listed 'Best First'.
Re: What defines the output format of a Postgres Timestamp by kikuchiyo (Hermit) on Jan 26, 2021 at 09:50 UTC
See https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT, especially the notes about ISO 8601. It appears that formatting happens on the server, and you can influence it with the `SET datestyle` command, but there aren't that many options. If you want some other fancy format, you have to use to_char. (I'd also like to point out that Postgresql's documentation is exemplary in general: it's well-structured, detailed, searchable, and best, versioned. It's usually the first and last place to look for if you have any questions about Postgresql.)	[reply]
Re^2: What defines the output format of a Postgres Timestamp by Skeeve (Parson) on Jan 26, 2021 at 12:04 UTC
Thanks. I already found that. Unfortunately ISO8601 and RFC3339 differ a bit. RFC3339 expects a minute part in the UTC offset while ISO8601 can live without it. `s$$([},&%#}/&/]+}%&{});#$&&s&&$^X.($'^"%]=\&(\|?{%` `+`.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e	[reply] [d/l] [select]
Re: What defines the output format of a Postgres Timestamp by haukex (Archbishop) on Jan 26, 2021 at 10:14 UTC
Now I'm wondering where the output format is defined. A quick look at the documentation: sections 8.5.2. Date/Time Output and 19.11.2. Locale and Formatting appear to discuss this. I would have to change each and every select statement to have a TO_CHAR with the proper format. This StackOverflow answer suggests defining a function to curry the arguments. In another piece of code I have lying around that uses Mojo::Pg, I use DateTime::Format::Strptime to parse the date strings coming from Postgres and DateTime's `strftime` to format them, though I suspect that's going to be less efficient than letting the database handle it.	[reply] [d/l]
Re^2: What defines the output format of a Postgres Timestamp by Skeeve (Parson) on Jan 26, 2021 at 12:25 UTC
This StackOverflow answer suggests defining a function to curry the arguments. This doesn't free me from adding a TO_CHAR to every timestamp column which is cumbersome if you have to replace every "select * …" with an explicit list of all columns. `s$$([},&%#}/&/]+}%&{});#$&&s&&$^X.($'^"%]=\&(\|?{%` `+`.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e	[reply] [d/l] [select]
Re^3: What defines the output format of a Postgres Timestamp by haukex (Archbishop) on Jan 26, 2021 at 12:33 UTC
This doesn't free me from adding a TO_CHAR to every timestamp column which is cumbersome if you have to replace every "select …" with an explicit list of all columns.* True, but at the moment I don't know enough about Pg to suggest any better solutions than the ones I already have. As for the Perl solution, note it's possible to detect date/time columns by inspecting `$sth->{pg_type}` (in my code I use a regex like `/^(?:datetime\|timestamp(?:tz)?)$/i`). For other database ~~types~~drivers it's `$sth->{TYPE}` (I don't think this is standardized). And a comment in terms of future-proofing the code, `SELECT *` only makes sense to me if you're using `selectrow_hashref` or one of its variants, and otherwise, using query builders is useful too; Mojo::Pg provdes easy access to SQL::Abstract.	[reply] [d/l] [select]
Re^3: What defines the output format of a Postgres Timestamp by jcb (Parson) on Jan 27, 2021 at 02:22 UTC
if you have to replace every "select …" with an explicit list of all columns* Unless you are introspecting the returned data to determine its structure or otherwise processing it very flexibly, you should be explicitly listing all of the columns you want to ensure that the data returned from the DB matches the order your program expects. "SELECT *" seems to be meant for interactive use and I suspect that the order of columns returned from that type of query is unspecified.	[reply]
Re^4: What defines the output format of a Postgres Timestamp by haukex (Archbishop) on Jan 27, 2021 at 14:08 UTC
Re^5: What defines the output format of a Postgres Timestamp by choroba (Cardinal) on Jan 27, 2021 at 14:13 UTC
Some notes below your chosen depth have not been shown here
Re^4: What defines the output format of a Postgres Timestamp by Skeeve (Parson) on Jan 27, 2021 at 07:53 UTC
Re^5: What defines the output format of a Postgres Timestamp by haukex (Archbishop) on Jan 27, 2021 at 14:10 UTC
Some notes below your chosen depth have not been shown here
Re: What defines the output format of a Postgres Timestamp by davies (Prior) on Jan 26, 2021 at 17:00 UTC
On a Debian 10 VM I have PG 11. In /etc/postgresql/11/main/postgresql.conf, I see the line: `datestyle = 'iso, dmy'` This seems to indicate that the information given in tables 8.14 & 8.15 of https://www.postgresql.org/docs/current/datatype-datetime.html is not case sensitive. Regards, John Davies	[reply] [d/l]
Re: What defines the output format of a Postgres Timestamp by Anonymous Monk on Jan 26, 2021 at 15:30 UTC
I believe that the server outputs the date/time in an ISO-standard character format because there is no single consistent or standard binary format for datetimes. Some programming-language API implementations will undertake to do a transparent-to-you conversion, but my understanding is that this takes place on the client side.	[reply]