David G. Johnston wrote: > On Wed, Apr 15, 2020 at 4:53 PM raf <raf@xxxxxxx> wrote: > > > I don't see much difference in storing a timestamptz in UTC or a > > timestamptz > > in CET. As long as the intended offset from UTC is recorded (which it is > > in a timestamptz) it should be fine. > > I only really skimmed the entire response but this framing of how > timestamptz stores results is wrong. Once you've stored a timestamptz in > PostgreSQL you no longer have any knowledge of the timezone. If you truly > need that you need to record that in a different field. What you do know > is that PostgreSQL has a known point-in-time in UTC and can give you back > the same value expressed in any other timezone according to the rules in > the timezone database. > > Or, as written verbatim in the documentation: > """ > For timestamp with time zone, the internally stored value is always in UTC > (Universal Coordinated Time, traditionally known as Greenwich Mean Time, > GMT). An input value that has an explicit time zone specified is converted > to UTC using the appropriate offset for that time zone. If no time zone is > stated in the input string, then it is assumed to be in the time zone > indicated by the system's TimeZone parameter, and is converted to UTC using > the offset for the timezone zone. > """ > > https://www.postgresql.org/docs/12/datatype-datetime.html > > David J. You are misinterpreting the documentation, or the documentation is incomplete/misleading at that location. It doesn't just convert and store the time in UTC. It stores the time in UTC and it also stores the offset from UTC as determined by the time zone specified on input (that section of the documentation might not mention that fact but it is true nonetheless). I store localtimes in the database and always see the offset when selecting it later. The timezone information doesn't just disappear as you (or that piece of documentation) might be suggesting. If you don't believe me, try this: create table example (t timestamptz not null); insert into example (t) values (timestamptz '2020-04-16 17:12:33.71768 Australia/Sydney'); select * from example; drop table example; When I run that, I see: CREATE TABLE INSERT 0 1 t ------------------------------ 2020-04-16 17:12:33.71768+10 (1 row) DROP TABLE So the timezone is stored (or the offset is stored if you prefer). Had it been daylight savings on that date in Sydney, the offset would have been "+11". It's all fine. cheers, raf