On Sat, May 12, 2018 at 8:19 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > I would agree that timestamp and timestamptz are both stored as numbers. Well, after reading source that is a fact. I was trying to say they are like real numbers, a point in a line. > I would not agree they do not have timezones: > show timezone; > TimeZone > ------------ > US/Pacific This proves a session has a timezone. > > create table ts_comparison (id integer, ts_tz timestamptz, ts_naive > timestamp); > > insert into ts_comparison values (1, now(), now()); > insert into ts_comparison values (1, '05/12/18 11:05', '05/12/18 11:05'); > insert into ts_comparison values (1, '05/12/18 11:05-07', '05/12/18 > > select * from ts_comparison ; > id | ts_tz | ts_naive > ----+-------------------------------+---------------------------- > 1 | 2018-05-12 11:04:44.161849-07 | 2018-05-12 11:04:44.161849 > 1 | 2018-05-12 11:05:00-07 | 2018-05-12 11:05:00 > 1 | 2018-05-12 11:05:00-07 | 2018-05-12 11:05:00 This proves timestampTz values get printed as a string which includes a timezone. It's got a cute "ts_naive" name to trick someone. And relies on some default conventions for convertions to/from strings to make it seem there is magic appearing. IIRC what you are doing is, given now() returns a timestamp with time one and the postgres default conversims: > insert into ts_comparison values (1, now(), now()); insert now(), now() at session_timezone <- do not remember the parameter name. > insert into ts_comparison values (1, '05/12/18 11:05', '05/12/18 11:05'); > insert into ts_comparison values (1, '05/12/18 11:05-07', '05/12/18 11:05-07'); Use string conversion, fully knowing that tstz conversion use session timestamp by default and ts ignore them, so for the second column you can use "+00", "-00", "-07" or +UnicornFarts", it's just line noise to the parser, and relies on a lot of hidden info, like the fact that your locale uses M/D/Y ( results would differ in mine which uses D/M/Y ). Those are examples of very complex string conversions. But timestamps do not have timezone. They are points in the time line. Points in earth surface have timezones, countries have timezones, but nor timestamp. > A timestamp with time zone anchors the entered value at a point in time(UTC > timezone) and therefore allows you to recover that point in time. The entered value is a string. The string representation of a point in time. This string, if it is given in the usual YMDhms or similar thing it needs a timezone ( either explicit or implied by the session ) to b converted into a timestamp. Once it's a timestamp you do not need the timezone. "Launch time of Apollo XI" is another string representation of a timestamp, which does not need timezones. You may need a timezone to convert it back to "YMDhms" form, but the timestamp does not have a time zone. If timestamps have time zones, then try to produce this for me "Launch time of Apollo XI", two values for this with different time zones ( Different values, that mean they must compare different as tstz, not two different string values ). Two different strings are not good, I agree you can produce strings with have a part which we call timezone and extract from it said part. You can extract any timezone from any timestamp, but it is of no use alone. It's just an string formatting artifact. > From there > you can rotate it to whatever timezone you want and know that it represents > the original point in time. I can represent it as a string, but I do not call it rotate. Of course, reprensenting a value in different ways does not change its meaning, an integer value equal to eighteen is not going to represent nothing different because I print it as "18", "022", "0x12" or "2*9" at different times. > A timestamp(without time zone) just records the > date and time portions without reference to a timezone. Nope. A ts records an instant in time. It is converted to string by default withtout printing a timezone, but this is becuase they are used this way. Also "just records" means tstz records more stuff. So it has more info. So I should not be able to build an isomorphism between then? > This means you have > an un-anchored timestamp and a future of trying to reconstruct the original > point in time. This is a very important distinction and the reason why if > you care about accurate date time's you store as timestamp with time zone. > In other words timestamp with time zone is more then 'just a number'. Nope. If you care about "easy input output" you store as tstz. It has nothing to do with accuracy ( for a correct manipulation, the thing is it is easier to wrtie correct code for tstz than for ts, but they are equally acurate ). In fact, if I store "2010-01-01 12:23:45"::timestamp I know I'm getting "2010-01-01 12:23:45" in the future, independently of where I read it, so normally I use it when I want to preserver "string" values but have some special "numeric" semantics. When I store instants in time I need to make sure my string input values accurately designate them, and I normally use tstz because its default input conversions process those correct formats I allow ( and screen before sending them for conversion ). Tell me one thing you can do with a timestamp with timezone that you cannot do with a int8 number or a with a timestamp without time zone. Given there is an isomorphism between them ( and, in postgres, the text version is the same for the inverse, IIRC "one=the_other at timezone UTC" ) and they have the same representation, I doubt it. They are just convenient ways to manipulate text values and apply functions based on type, but they do not have a time zone. You can not get a time zone from a timestamp with time zone value. What leads to countless confussion is it seems that a tstz-value should be composed of a ts-value plus a tz-value, but they are not. Is just a problem with naming. Francisco Olarte.
