Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

What defines the output format of a Postgres Timestamp

by Skeeve (Parson)
on Jan 26, 2021 at 06:41 UTC ( #11127460=perlquestion: print w/replies, xml ) Need Help??

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" }

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

Replies are listed 'Best First'.
Re: What defines the output format of a Postgres Timestamp
by kikuchiyo (Friar) 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.)

      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
Re: What defines the output format of a Postgres Timestamp
by haukex (Bishop) 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.

      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
        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 typesdrivers 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.

        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.

Re: What defines the output format of a Postgres Timestamp
by davies (Prior) on Jan 26, 2021 at 17:00 UTC
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11127460]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (6)
As of 2021-03-02 08:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (41 votes). Check out past polls.

    Notices?