I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' time zone in a timestamptz column.
Try doing this before re-running your test: set timezone to 'utc'; What you are seeing in your test is an artifact of that timezone setting. Steve On Thu, Apr 16, 2020 at 5:19 PM raf <r...@raf.org> wrote: > David G. Johnston wrote: > > > On Wed, Apr 15, 2020 at 4:53 PM raf <r...@raf.org> 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 > > >
