Re: Domain based on TIMEZONE WITH TIME ZONE - Mailing list pgsql-general

On Sat, May 12, 2018 at 8:19 PM, Adrian Klaver
<adrian.klaver@aklaver.com> 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.