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

On 05/13/2018 01:55 AM, Francisco Olarte wrote:
> 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.

Agreed, it was included just to show what my time zone my client was 
configured in for the examples shown below.

> 
>>
>> 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:

Not trying to trick anyone and no magic. The difference in the 
represented values between ts_tz and ts_naive is the heart of my 
argument. Timestamptz values are stored in manner that allows you to 
have the output with a time zone offset. Timestamps w/notz are not. This 
is a big difference and very important. The simple case above does not 
really illustrate that though. Where it becomes important is if you have 
multiple clients in multiple sessions over multiple time zones. If you 
store timestamps in a timestamp(notz) field and get input from PDT and 
EDT of the timestamp string '05/13/18 11:53' you are not dealing with 
the same time even though they show up that way:

set timezone = 'US/Eastern';
SET
insert into ts_comparison values (2, '05/13/18 11:53', '05/13/18 11:53');
set timezone = 'US/Pacific';
SET
insert into ts_comparison values (3, '05/13/18 11:53', '05/13/18 11:53');

select * from ts_comparison ;
  id |             ts_tz             |          ts_naive
----+-------------------------------+----------------------------
   2 | 2018-05-13 08:53:00-07        | 2018-05-13 11:53:00
   3 | 2018-05-13 11:53:00-07        | 2018-05-13 11:53:00

The above is my point. That is the ability to accurately distinguish 
points in time, because '2018-05-13 11:53:00' to me is just a timestamp 
string without the context of a tz offset to make it useful.


> 
>> 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.

Not sure what the above is supposed to indicate. It is no surprise that 
there are a lot of ways to output timestamps in human readable form.

> 
> 
> 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.

I don't know about you but I am living on the earths surface:). That 
means when I deal with timestamps they are with reference to a location. 
There is the general notion of time marching on with out reference to 
Earth or humans. Then there is the time that humans deal with on a daily 
basis and that is very much anchored to time zones. For that we 
timestamps with timezones:

https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT

timestamp [ (p) ] with time zone



> 
> 
>> 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.

I will agree that timestamptz is stored as number only. However that 
number in Postgres has an implied time zone of UTC:

https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT

"For timestamp with time zone, the internally stored value is always in 
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean 
Time, GMT)"

It is what allows timestamptz to deliver time zone offsets on output.

> 
> 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.

The point of time zones is that the above cannot be done. They all refer 
back to UTC via offsets so represent the same UTC time. Yet to the folks 
on the ground 07/16/1969 9:32:00-05 and 1969-07-16 06:32:00-08 are 
different times. It where the logical(universal time) and 
reality(perceived time) clash.

> 
> You can extract any timezone from any timestamp, but it is of no use
> alone. It's just an string formatting artifact.

It provides context to the timestamp so it more then an 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.

For time it does change its meaning. Per the Apollo example the same UTC 
time has different meanings in local time. On the US East coast it meant 
viewing at mid morning for the Pacific coast it meant viewing in the 
early morning. If you do not think that is different talk to someone who 
is not an early riser:)

> 
>> 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

But you have lost all context. Unless you are independently recording 
where the timestamp originated you have lost the actual point in time it 
represents.

> 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.

Retrieve a anchored timestamp without bringing in more info. As I said 
above:
'2018-05-13 11:53:00' has no meaning to me
'2018-05-12 11:05:00-07' does

> 
> 
> 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.

The stored value is value+00(implied).
The returned values sure look like they have tz-value:

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
   2 | 2018-05-13 08:53:00-07        | 2018-05-13 11:53:00
   3 | 2018-05-13 11:53:00-07        | 2018-05-13 11:53:00


> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com