Handy Little Items For Postgres

Time Zone Display

Postgres always stores a timestamp with no time zone. The data types TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE are both misnomers. A better name would be WITH REGARD TO TIME ZONE. 

The "without time zone" command ignores any time zone or offset information included with data input. The date-time value is stored without any adjustment and without any recording of the specified time zone or offset. This is rarely useful, as explained by Postgres expert David E Wheeler. 

The "with time zone" means that any time zone or offset information included with data input is used to adjust the stored date-time to UTC. Any such time zone or offset info is discarded after the adjustment, not stored as the name suggests.

When displaying timestamps, Postgres applies the client's default time zone when generating a string value. What if you want to display the date-time values in UTC? 

    SET TIME ZONE 'UTC';

What if you want to display in a specific time zone? Use a proper time zone name, usually a continent/city (or region). Avoid the 3 or 4 letter codes such as EST or IST as they are neither standardized nor unique.

    SET TIME ZONE 'America/Montreal';

This command affects the current session only. For example, type into a "Query" window in pgAdmin.

BYTEA Display

The BYTEA data type in Postgres is akin to a BLOB in other databases. I'm guessing the name is short for "byte array". This data type is a sort of anti-data-type. When specifying BYTEA, you are telling Postgres to not bother parsing or interpreting the data. You are saying "take these bytes as-is and save them to storage without looking at them".

Since Postgres has no idea what those bytes mean, it has no idea how to display them. In the old days, Postgres displayed them in a sequence of escapes. Nowadays you have the choice of displaying as hexadecimal. But how to specify which?

    SET bytea_output = "hex";
    SET bytea_output = "escape";

 This command affects the current session only.