Hacker News new | ask | show | jobs
by shanipribadi 1776 days ago
Both postgres timestamptz and timestamp are the same, they both store timestamp as UTC epoch time. Also timestamptz does not store the timezone given by the client, the only thing it stores is the UTC epoch time, so you'll lose the original timezone information. The only difference between the two is that with timestamptz postgres does a conversion between the text representation and the stored value based on a configured timezone (session, or database configured).

The decision on which type to use IMO depends a lot more on the behaviour of the database driver of your programming language, and which one works better with the type.

If you're not writing an application that uses a db driver, but interacting with pg over psql manually, timestamptz is preferrable because a lot of pg functions works better with timestamptz. If you use timestamp, you would need to be using explicit timezone conversion and be consistent to avoid weird double timezone conversion issues, which makes it far more verbose.