Postgres timestamp

Question

We have a debate about how best to store the timestamp in postgres. Currently, all timestamps are stored as +00, and we have a time zone associated with each client. We look at the time zone and transform the time when something happened, which increases complexity, because we need to make more joins and a more complex query.

Another method is connecting to Postgres and setting the connection time zone, and it changes this time zone all the time.

My problem is that ANZ has 4-5 time zones. When we try to make our invoices, we need to know on what day certain transactions occurred, and within three time zones there is no perfect solution.

I was thinking of including the time zone in the timestamp to make it easier - TIMESTAMP '1999-01-15 8:00:00 -8: 00'

I got the impression that this is the best practice, but some people say that this is a bad idea. We will have clients through ANZ, what do we need to do accurate invoices, which is the best solution and the most elegant?

Cheers Scott

Answer 1

There are no bulletproof solutions here.

My first tip: never rely on the default time zone on the server.

My second tip: choose timestamp - timestamptz according to the (prevailing) data semantics.

In more detail: PostgresSQL has two options for timestamps, vaguely named TIMESTAMP WITHOUT TIMEZONE (timestamp) and TIMESTAMP WITH TIMEZONE (timestamptz) . In fact, neither stores the time zone, nor even the offset. Both types of data have the same width (4 bytes), and their difference is subtle - and, even worse, you can be bitten if you do not fully understand them, and your server changes the time zone. My set of health rules:

• Use TIMESTAMP WITH TIMEZONE (timestamptz) to store events that are mainly related to “physical” time , for which you are mostly interested in the question of whether event 1 was before event 2 (regardless of time zones) or calculated time intervals (in " physical units ", for example, seconds, and not in" civilian "units like days-months, etc.). A typical example is the creation / modification time of a record - which usually means the word "time stamp".

• Use TIMESTAMP WITHOUT TIMEZONE (timestamp) to store events for which the relevant information is “civil time” (that is, the {year-month-day hour-min-sec} fields in general) and queries include calendar calculations. In this case, you only save “local time”, that is, a date-date relative to some indefinite (irrelevant or implied or stored elsewhere) time zone.

The second option simplifies the request, say, "all events that occurred on day 2013-201-20" (in each respective region / country / time zone), but complicates the request for "all events that occurred (physically) before the control event" (if we do not know that they are in the same time zone). You choose.

If you need the complete thing, this is not enough, you need to either save the time zone or the offset in an additional field. Another option that takes a few bytes, but may be more efficient for queries, is to save both fields.

See also this answer .

Answer 2

Use timestamptz (or timestamp with time zone for standard SQL syntax) for your input fields, then you can set your own time offset for each insert using either the time zone or time offset according to your preference.

Example...

 CREATE TABLE "timetest" ( "timestamp" timestamptz ); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 PST'); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Madrid'); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Athens'); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT+11'); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT-11'); INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 UTC'); 

... and your time will be adjusted accordingly

 SELECT * FROM "timetest"; -- note this may default to your timezone ------------------------ [timestamp] ------------------------ 2013-01-01 16:45:00+00 2013-01-01 07:45:00+00 2013-01-01 06:45:00+00 2012-12-31 21:45:00+00 2013-01-01 19:45:00+00 2013-01-01 08:45:00+00 2013-01-01 08:45:00+00 

or better yet, try the following ...

 SELECT "timestamp" AT TIME ZONE 'Australia/Sydney' AS "Sydney", "timestamp" AT TIME ZONE 'Australia/Perth' AS "Perth" FROM "timetest"; -------------------------------------------- [Sydney]..............[Perth] -------------------------------------------- 2013-01-02 03:45:00 - 2013-01-02 00:45:00 2013-01-01 18:45:00 - 2013-01-01 15:45:00 2013-01-01 17:45:00 - 2013-01-01 14:45:00 2013-01-01 08:45:00 - 2013-01-01 05:45:00 2013-01-02 06:45:00 - 2013-01-02 03:45:00 2013-01-01 19:45:00 - 2013-01-01 16:45:00 

Finally, to get an idea of ​​the list of time zones available in your database, try:

 SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC;