[mrtbld]

> Postgres has two types of timestamps. It has a generic timestamp and one with timezone embedded in it.

That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a displayable string, so timestamptz are always displayed with a timezone, but that info does not come from the stored value.

Timestamptz needs timezone information only for operations that would give different results in different timezones, e.g. display as string, extract the day part, add a 1-month interval (DST info needed), etc. Comparing two timestamptz however doesn't require any timezone info.

The difference between timestamp and timestamptz is not about what they store, but about how they behave.

Edit: In my experience, this is not always obvious because postgres uses the default timezone of the instance whenever it needs such info with timestamptz operations. Using an explicit timezone often requires convoluted code.

[mixmastamyk]

Thanks. If I store all datetimes from my app in UTC, with end users in more than one timezone, which type should I use?

[mrtbld]

Well I would use timestamptz, using user's timezone only to convert for display. Use cases for timestamp are very limited.

Just make sure you include a timezone info in string representations in your SQL queries. For example '2000-01-01T00:00:00Z' where Z stands for UTC. Otherwise that would insert a timestamp into a timestamptz column, in which case postgres uses local timezone setting for conversion, implicitly; this is not what you want.

See http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...

Also you should use an equivalent type in you app, i.e. python datetime with tzinfo or JS Date. And beware of UTC offsets: they can't handle DST. Python pytz and JS moment-timezone provide DST-aware timezone info (which is built-in in postgres).

Edit: if you can rely on your users system time for display that's even better because you wouldn't have to explicitly deal with those DST-aware timezone info.