Adrian Klaver wrote: > On 4/16/20 1:23 AM, raf wrote: > > Steve Baldwin wrote: > > > > > 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 > > > > Thanks. You're right. > > > > 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; > > set timezone to 'utc'; > > select * from example; > > drop table example; > > > > Does this: > > > > CREATE TABLE > > INSERT 0 1 > > t > > ------------------------------ > > 2020-04-16 17:12:33.71768+10 > > (1 row) > > > > SET > > t > > ------------------------------ > > 2020-04-16 07:12:33.71768+00 > > (1 row) > > > > DROP TABLE > > > > So it doesn't store the offset, but I've used postgres > > for 12 years without knowing that and it hasn't been > > a problem. Yay, postgres! > > > > It doesn't store the offset but, by using timestamptz, > > it knows that the timezone is UTC. That's what matters. > > Well that is somewhat misleading. The value entered is stored as timestamp > with an implicit tz of 'UTC'. The issue that trips people up is the format > of the input value. If you enter an input with an offset or correct tz value > then you have given Postgres an explicit value to work off for converting it > to 'UTC'. When I said "it knows that the timezone is UTC", I was only referring to the values once stored as a timestamptz, not the input. Sorry that wasn't clear enough. I meant to say that once a value is stored in a timestamptz (as opposed to a timestamp), postgres knows that it is stored in UTC and will do the right things with it. > For what is correct see here: > > https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES > > If you input a value that does not have the above then Postgres uses the SET > TimeZone value to implicitly set the input value's tz(as pointed out by > David Johnson upstream). In other words Postgres does not assume an input > value is in 'UTC'. For the OP's case that could cause issues if the > timestamp in the CSV file does not have a proper offset/tz and the client is > using a tz other then 'UTC'(as pointed by others upstream). The bottom line > is that when dealing with timestamps explicit is better then implicit. I couldn't agree more. > > The fact that it knows the time zone is what makes everything > > work. Timestamp without time zone is best avoided I think. > > > > cheers, > > raf > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx >
