On 05/12/2018 10:22 AM, Francisco Olarte wrote:
Adrian:
On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> wrote:
On 05/12/2018 04:04 AM, Francisco Olarte wrote:
...
Can you post an example ( correct, error inputs, preferrably done with
psql ) ?
At this point I do not know if it is working or if it seems to working
for you. I see some cases in which it would seem to work, but I would
like to know what you mean by "sending non utc timestamps to the
database".
In the post below Ben explains that he realizes it is not working the way he
thought:
Yep, saw it after posting this.
From what I can gather it comes down where UTC is being enforced. Ben was
hoping that the domain would force timestamps to be only submitted with a
UTC offset with the idea that this would force storage in the field as UTC
only. The realization is that if you have a field of type timestamp with
time zone the value is going to be stored as UTC regardless of the offset
that is presented(even in cases where there is no offset presented, when an
implicit one is assigned). That means there really is no need for the
domain.
I think this problem, and similar ones, come from the description in
the doc, and in a lot of similar places, as "the timestamp with time
zone value is stored as utc", or "timestamps with time zones are
normalized to utc". If you look at the docs you realize they are all
stored as numbers, and ocupy the same space as timestamp W/O TZ. The
thing is you do not need UTC for anything to represent timestamps.
Using the (simpler) unix timestamp as an example. If UTC ( and UAT and
GMT ) did not exist I could defne it as, say, "number of seconds
elapsed since eiight in the morning January the Second in
Madrid/Spain local clocks plus one hundred and eight thousands", no
UTC needed at all, they are just numbers, they do not have timezones.
The only difference is timestamp uses gmtime/timegm for text
conversion and timestamptz uses localtime/timelocal.
I would agree that timestamp and timestamptz are both stored as numbers.
I would not agree they do not have timezones:
show timezone;
TimeZone
------------
US/Pacific
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
11:05-07');
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
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. From there you can rotate it to whatever timezone you want and
know that it represents the original point in time. A timestamp(without
time zone) just records the date and time portions without reference to
a timezone. 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'.
In fact I've found the following in the sources:
* Timestamps, as well as the h/m/s fields of intervals, are stored as
* int64 values with units of microseconds. (Once upon a time they were
* double values with units of seconds.)
And from some reading it seems to be like the unix timestamp, but in
64 bits microseconds and referencing extended Julian ( or Gregorian )
calendar, not too sure about it. I've read the definition somewhere,
but thinking of them as "just a number" has avoided me a lot of
problems.
Francisco Olarte.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
