Strategy about working with timezones in postgresql

Question

I need to work with multiple timezones in my application.

Basically my system generates data and customers from anywhere on the planet can get access to it.

Since my data have an associated timestamp I thought about the following strategy to deal with timezones.

In Postgres:

• Create my table with a ::timestamptz type for my timestamps. Timestamps without timezone will not be allowed as mixing ::timestamp and ::timestamptz looks like a time bomb in my case.

• Set my PG server system's time zone to `UTC.

• Set timezone='UTC' in postgresql.conf (might not be needed if the system's TZ is UTC but I am a bit paranoid, and it cost me nothing).

• Add the following check when creating my table :

  CONSTRAINT timestamp_must_be_utc CHECK (date_part('timezone'::text, "my_timestamp_field") = 0::double precision)

• Store all my timestamps in UTC

On the client side (python + pytz)

• Storing the customer's timezone in his profile, like 'America/Los Angeles'

• Sending the timezone information to postgres when querying data so I can get something like SELECT xxxx FROM yyyy WHERE my_ts >= '2012-11-27 19:13:00+01'::timestamptz and let Postgres do the conversion to UTC. Alternatively I could use pytz for the conversion but it seems that Postgres will do this job just fine.

• Converting timestamps according the customer's timezone so I can display data + timestamp correctly.

To summarize, I plan to use UTC everywhere for storing and querying data, and just a timezone conversion when displaying data.

I do not have much experience with Postgres and the way it handles timezones. I know how hard it is to deal with dates and times in different timezones (once you have to work with flight schedules you learn the hard way that using UTC is the only reliable way to do date and time calculus) this is why I am asking this question so that postgres users who are more experienced that me can confirm or correct my strategy.

Interesting links:

• https://stackoverflow.com/a/9576170/1121179
• https://stackoverflow.com/a/10462428/1121179

Answer 1

Make yourself a tea/coffee according to preference, set aside an hour or so and read the manual's details on timestamp handling. Play around a bit and it will all become clear.

There is no problem with dealing with different timezones if you are using "timestamp with time zone" (timestamptz). It should really have been named "absolute timestamp" and internally is UTC. The timezone part isn't stored, it's just used to get to an absolute time.

So - make sure all your timestamps include the relevant timezone on update, and then just set your client timezone appropriately. They will be returned to you in the client's timezone regardless of the timezone they were provided in.

Where it does get fiddly is dealing with the fact that a day isn't always 24 hours (and sometimes an hour isn't 3600 seconds) and that DST happens at different dates depending on country and history. That's not PostgreSQL though, that's just the real world.