Postgres Timestamp

Question

We are having a debate about the best way to store a timestamp in postgres. Currently all time stamps are stored as +00 and we have a timezone associated with each client. We look up the timezone and convert the time that something happened which increases complexity as we need to do more joins and a more complex query.

Another method is connecting to Postgres and setting the timezone of the connection and it changes all the times to be that timezone.

My problem with this is that in ANZ there are 4-5 timezones. When we try and do our invoices we need to know what day certain transactions happened, and across three timezones there is no perfect solution.

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

I was under the impression that this was best practice, but some people say this is a bad idea. We will have customers across ANZ that we need to do accurate invoices for, what is the best solution and most elegant?

Cheers Scott

Solution 1

There are no bulletproof solutions here.

My first advice: never rely on the default timezone of the server.

My second advice: choose between timestamp-timestamptz according to the (predominant) semantics of the data.

In more detail: PostgresSQL has two timestamp variants, confusingly named TIMESTAMP WITHOUT TIMEZONE (timestamp) and TIMESTAMP WITH TIMEZONE (timestamptz). Actually, neither stores a timezone, nor even an offset. Both datatypes occupy the same width (4 bytes), and their difference is subtle - and, worse, can bite you if you don't fully understand them and your server changes the timezone. My sanity ruleset is:

• Use TIMESTAMP WITH TIMEZONE (timestamptz) for storing events that are predominantly related to the "physical" time, for which you are mainly interested in querying whether event 1 was before event 2 (regardless of timezones), or computing time intervals (in "physical units", eg, seconds; not in "civil" units as days-months, etc). The typical example are record creation/modification time - what one usually means by the word "Timestamp".

• Use TIMESTAMP WITHOUT TIMEZONE (timestamp) for storing events for which the relevant information is the "civil time" (that is, the fields {year-month-day hour-min-sec} as a whole), and the queries involve calendar calculations. In this case, you would store here only the "local time", i.e., the date-time relative to some unspecified (irrelevant, or implied, or stored somewhere else) timezone.

The second option makes you easier to query for, say, "all events that happened on day '2013-01-20'" (in each corresponding region/country/timezone) - but makes it more difficult to query for "all events that ocurred (physically) before a reference event" (unless we know they are in the same timezone). You choose.

If you need the full thing, neither is enough, you need either to store the timezone or the offset in an additional field. Another option, which wastes a few bytes but can be more efficient for queries is to store both fields.

See also this answer.

Solution 2

Use timestamptz (or timestamp with time zone for standard SQL syntax) for your input fields, you can then set a custom time offset for every insert using either the timezone or time offset to whatever suits 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 times 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 still, 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 list of the timezones availalble to your database try:

SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC; 

Comments

[Dean Radcliffe]

Thanks for pointing out the confusion in the names of the fields, and that neither one stores the offset ! In fact, it's on The Postgres TODO wiki to actually store the timezone offset when saving a date to a timestamp with time zone column. wiki.postgresql.org/wiki/Todo#Dates_and_Times In the meantime thanks for pointing out the confusion !

[leonbloy]

@DeanRadcliffe That TODO is quite old (more than 5 years, I'd bet) so I wouldn't hold my breath... To be fair, I don't know if some relational SQL server has implemented that.

[Basil Bourque]

To clarify the difference: TIMESTAMP WITH TIME ZONE uses any passed offset-from-UTC or time zone info to adjust to UTC. In contrast, TIMESTAMP WITHOUT TIME ZONE ignores any offset or time zone whether included or omitted. Only use WITHOUT if you want the general idea of a date-time such as "Christmas starts at midnight December 25, 2015" which, of course, translates to a different moment in Auckland than in Sydney, Perth, Paris, or Seattle. See this expert’s post Always Use TIMESTAMP WITH TIME ZONE.