Обсуждение: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Bryn Llewellyn
Дата:
Given these, where the timestamptz values span the start of US/Pacific DST:
t0 timestamptz := ...;
t1 timestamptz := ...;
i_by_subtraction interval second := t1 - t0;
then this expression tests FALSE:
t0 + i_by_subtraction i = t1
There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the results of adding each to the same timestamptz value are different.
PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.
(I searched the doc but found nothing.)
------------------------------------------------------------------------------------------
— Self-contained test case. Finishes silently in PG 13.2 showing that all assertions hold.
do $body$
declare
t0_text constant text := '2021-03-13 20:00:00';
t_dst constant text := '2021-03-14 02:00:00';
t1_text constant text := '2021-03-14 22:00:00';
t_surprise constant text := '2021-03-14 21:00:00';
-- i_expected because we spring forward to US DST between t0_text and t1_text.
i_expected constant interval second := '1 day 01:00:00';
i_25_hours constant interval second := '25 hours';
t0 timestamptz;
t1 timestamptz;
i_by_subtraction interval second;
begin
assert t0_text < t_dst and t_dst < t1_text, 'assert #1 failed';
assert t_surprise <> t1_text, 'assert #2 failed';
------------------------------------------------------------
-- Look!
assert i_expected = i_25_hours, 'assert #3 failed';
------------------------------------------------------------
set time zone 'US/Pacific';
t0 := t0_text::timestamptz;
t1 := t1_text::timestamptz;
assert t0::text = t0_text||'-08', 'assert #4 failed';
assert t1::text = t1_text||'-07', 'assert #5 failed'; --<< compare
i_by_subtraction := t1 - t0;
assert i_by_subtraction = i_expected, 'assert #6 failed';
------------------------------------------------------------
-- HERE IS THE PARADOX.
t1 := t0 + i_expected;
assert t1::text = t_surprise||'-07', 'assert #7 failed';
-- Meanwhile...
t1 := t0 + i_25_hours;
assert t1::text = t1_text||'-07', 'assert #8 failed';--<< compare (same)
------------------------------------------------------------
end;
$body$;
t0 timestamptz := ...;
t1 timestamptz := ...;
i_by_subtraction interval second := t1 - t0;
then this expression tests FALSE:
t0 + i_by_subtraction i = t1
There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the results of adding each to the same timestamptz value are different.
PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.
(I searched the doc but found nothing.)
------------------------------------------------------------------------------------------
— Self-contained test case. Finishes silently in PG 13.2 showing that all assertions hold.
do $body$
declare
t0_text constant text := '2021-03-13 20:00:00';
t_dst constant text := '2021-03-14 02:00:00';
t1_text constant text := '2021-03-14 22:00:00';
t_surprise constant text := '2021-03-14 21:00:00';
-- i_expected because we spring forward to US DST between t0_text and t1_text.
i_expected constant interval second := '1 day 01:00:00';
i_25_hours constant interval second := '25 hours';
t0 timestamptz;
t1 timestamptz;
i_by_subtraction interval second;
begin
assert t0_text < t_dst and t_dst < t1_text, 'assert #1 failed';
assert t_surprise <> t1_text, 'assert #2 failed';
------------------------------------------------------------
-- Look!
assert i_expected = i_25_hours, 'assert #3 failed';
------------------------------------------------------------
set time zone 'US/Pacific';
t0 := t0_text::timestamptz;
t1 := t1_text::timestamptz;
assert t0::text = t0_text||'-08', 'assert #4 failed';
assert t1::text = t1_text||'-07', 'assert #5 failed'; --<< compare
i_by_subtraction := t1 - t0;
assert i_by_subtraction = i_expected, 'assert #6 failed';
------------------------------------------------------------
-- HERE IS THE PARADOX.
t1 := t0 + i_expected;
assert t1::text = t_surprise||'-07', 'assert #7 failed';
-- Meanwhile...
t1 := t0 + i_25_hours;
assert t1::text = t1_text||'-07', 'assert #8 failed';--<< compare (same)
------------------------------------------------------------
end;
$body$;
Bryn Llewellyn <bryn@yugabyte.com> writes: > There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the resultsof adding each to the same timestamptz value are different. You're misunderstanding how it works. The whole point of these operations is that "+ '1 day'" should give the same civil time on the next day, even if a DST boundary intervened. Similarly, "+ '1 month'" tries to give the same day number in the next month, and "+ '1 year'" tries to give the same month/day in the next year, despite varying month and year lengths. (Obviously, there are edge cases where there is no such date, and you get some nearby date instead.) Smaller units are taken literally though, so you could add some large number of hours or seconds if you don't want these behaviors. > PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED. If you are looking for simple predictable rules, I am afraid there is absolutely nothing about either the Gregorian calendar or daylight-savings time that you will like. > (I searched the doc but found nothing.) There's a specific discussion of the DST-boundary issue on this page: https://www.postgresql.org/docs/current/functions-datetime.html (search for the first mention of America/Denver). Not sure about how well the months and years cases are documented. regards, tom lane
Re: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Francisco Olarte
Дата:
On Fri, Mar 26, 2021 at 1:21 PM Bryn Llewellyn <bryn@yugabyte.com> wrote: > Given these, where the timestamptz values span the start of US/Pacific DST: > t0 timestamptz := ...; > t1 timestamptz := ...; > i_by_subtraction interval second := t1 - t0; > then this expression tests FALSE: > t0 + i_by_subtraction i = t1 > There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the resultsof adding each to the same timestamptz value are different. This is many times by dessign. Intervals have, IIRC, three parts, seconds, days and months to make things like "3 month 2 days 1hour from now" work as expected in "normal" context ( i.e, in which you would expect to convert 2021-03-26 16:00:00 cia 2021-06-26 ( 3 month ) and 06-28 ( two days) to 2021-06-28 17:00, disregarding the fact that months and days are different duration ( in Spain ) while doing it ). It seems you want to use them as simple seconds count, like "I'm starting a batch job which will take 25 hours to be done, when will it end?". Like unix timestamps. If you want that, just avoid interval, use extract epoch to convert timestamptz to a simple number of seconds, do arithmetic there (where intervals are just plain numbers ) and convert back using to_timestamp(). It seems you expect a different behaviour from intervals to what they are dessigned for. Francisco Olarte.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Bryn Llewellyn
Дата:
Tom Lane wrote:
bryn@yugabyte.com writes:
You're misunderstanding how it works…
…there is absolutely nothing about either the Gregorian calendar or daylight-savings time that you will like.
There's a specific discussion of the DST-boundary issue on… https://www.postgresql.org/docs/11/functions-datetime.html.
There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the results of adding each to the same timestamptz value are different.
You're misunderstanding how it works…
PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.
…there is absolutely nothing about either the Gregorian calendar or daylight-savings time that you will like.
(I searched the doc but found nothing.)
There's a specific discussion of the DST-boundary issue on… https://www.postgresql.org/docs/11/functions-datetime.html.
Thanks for the prompt reply Tom.
and for the doc ref. (I used the Version 11 page ‘cos that’s my particular focus.)
« When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days, keeping the time of day the same. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours’.. »
I also noted this from folarte@peoplecall.com:
« It seems you want to use them as simple seconds count, like “I'm starting a batch job which will take 25 hours to be done, when will it end?". Like unix timestamps. If you want that, just avoid interval, use extract epoch to convert timestamptz to a simple number of seconds, do arithmetic there (where intervals are just plain numbers) and convert back using to_timestamp(). »
Thanks Francisco. That’s a useful tip.
I’d deduced the conceptual background that both Tom and Francisco referred to. And I’ve coined the terms “horological interval” and “cultural interval” to capture the distinction. I’d also noticed that it seems that, to first order, the “interval day to second” flavor maps to “horological” and the "interval year to month” flavor maps to “cultural”. However, as my testcase shows, “day” is an oddity because subtracting two timestamptz values treats “day” in the “horological” sense but adding an interval value to a timestamptz treats “day” in the cultural sense. This was the central point of my question. Neither of you referred to this.
I’m going to conclude just that it is what it is and it won’t change.
I noted this from https://www.postgresql.org/docs/11/datatype-datetime.html:
« Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results… »
(Francisco referred to this.) Consider this:
create table t(k int primary key, i interval day to second not null);
insert into t(k, i) values(1, '1 day 1 hour'), (2, '25 hours');
select k, i from t order by k;
insert into t(k, i) values(1, '1 day 1 hour'), (2, '25 hours');
select k, i from t order by k;
The “select” shows that the difference in spelling the two values is preserved in what’s persisted. This is consistent with months, days, and seconds being persisted separately. And it’s consistent with the different semantic effect that the two values can have on addition. I therefore find it confusing that wrong semantics are imposed on the represented value here:
create unique index on t(i);
It causes the “could not create unique index” error. This is the same effect that the “assert” for equality in my testcase shows. The two values can be defined to be equal only if “day” is given a strict horological meaning. But the present discussion shows that it’s taken culturally on interval addition.
This brings me to another strange observation. Do this:
set time zone 'US/Pacific';
select (
('2021-02-15 12:00:00'::timestamptz + (interval '9 months')::interval month)::timestamptz at time zone 'US/Pacific'
)::text as t;
select (
('2021-02-15 12:00:00'::timestamptz + (interval '9 months')::interval month)::timestamptz at time zone 'US/Pacific'
)::text as t;
This is the result:
2021-11-15 12:00:00
In plain English, 9 months after 15-Feb is 15-Nov—consistent with what has been said about “month” always being taken culturally. Now try this:
select (
('2021-11-15 12:00:00'::timestamptz - '2021-02-15 12:00:00'::timestamptz)::interval month
)::text as i;
('2021-11-15 12:00:00'::timestamptz - '2021-02-15 12:00:00'::timestamptz)::interval month
)::text as i;
It silently produces this result:
00:00:00
In plain English, you can’t produce a cultural interval value by subtraction; subtraction can only populate a “day to second” flavor interval. Moreover, the computed “hours” component never exceeds 24 and the computed “days” component is always the horological value.
I failed to find an explanation of this in the doc—but I dare say that my searching skills are too feeble.
Finally, I discovered that this is OK:
create table t(i interval);
But I can’t find a definition of the semantics of a bare interval. However, I did find a column headed “Mixed Interval” at https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE. But the example values in the column are consistent with this:
select ((interval '2 years, 3 months, 4 days, 5 hours, 6 minutes 7.8 seconds')::interval)::text as i;
This is the result:
2 years 3 mons 4 days 05:06:07.8
If you repeat the “select” using the typecast “::interval month” then the other components are silently thrown away. But if you repeat it using the typecast “::interval second” then all components are preserved just as with bare “interval”. This muddies my idea that there were three distinct interval flavors: horological, cultural, and hybrid. Is the behavior that I’ve just shown intended?
I can guess the rules for the outcome when such a hybrid is added to a timestamptz value. It’s possible to design edge case tests where you’d get different outcomes if: (a) the cultural component is added first and only then the horological component is added; or (b) the components are added in the other order. It seems to me that the outcome is governed by rule (a). Am I right?
B.t.w., I think that the specific complexities of the proleptic Gregorian calendar are cleanly separable from the basic idea that (considering only the requirements statement space) there is a real distinction to be drawn between “horological” and “cultural”—no matter what calendar rules might be used.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Francisco Olarte
Дата:
Bryn: ( 1st, sorry if I misquote something, but i use text-only for the list ) On Fri, Mar 26, 2021 at 10:16 PM Bryn Llewellyn <bryn@yugabyte.com> wrote: > I’d deduced the conceptual background that both Tom and Francisco referred to. And I’ve coined the terms “horological interval”and “cultural interval” to capture the distinction. I’d also noticed that it seems that, to first order, the “intervalday to second” flavor maps to “horological” and the "interval year to month” flavor maps to “cultural”. However,as my testcase shows, “day” is an oddity because subtracting two timestamptz values treats “day” in the “horological”sense but adding an interval value to a timestamptz treats “day” in the cultural sense. This was the centralpoint of my question. Neither of you referred to this. I, personally, did not refer to that on purpose, as I did not fully understand what you were trying to prove, and as your code defaulted the timezones I could not easily reproduce result. I saw intervals working as they are dessigned, thought you might be trying to use them for a purpose they are not dessigned and pointed that. > I’m going to conclude just that it is what it is and it won’t change. Intervals have a behaviour. Many people do not like it/consider it wrong. My advice is normally "do not use them". That is what I do, except for quick and dirty reports I rarely ever use them . ... > create table t(k int primary key, i interval day to second not null); > insert into t(k, i) values(1, '1 day 1 hour'), (2, '25 hours'); > select k, i from t order by k; > > The “select” shows that the difference in spelling the two values is preserved in what’s persisted. This is consistentwith months, days, and seconds being persisted separately. And it’s consistent with the different semantic effectthat the two values can have on addition. I therefore find it confusing that wrong semantics are imposed on the representedvalue here: > > create unique index on t(i); > > It causes the “could not create unique index” error. This is the same effect that the “assert” for equality in my testcaseshows. The two values can be defined to be equal only if “day” is given a strict horological meaning. But the presentdiscussion shows that it’s taken culturally on interval addition. Interval are a hairy datatype. I'm not sure they even have a proper order defined (i.e, is 30 days 6 hours more or less than 1 month ). They have some normalization conversions, but IMHO they are not good to use in a btree, which needs to order them. > This brings me to another strange observation. Do this: .. Skipping intermediates... > select ( > ('2021-11-15 12:00:00'::timestamptz - '2021-02-15 12:00:00'::timestamptz)::interval month > )::text as i; > It silently produces this result: > 00:00:00 You are right, the substraction produces a days interval (273 here), whcih when truncated to months just go to zero. It surprised me a bit, so I dug into the docs and found this: postgres=> select('2021-11-15'::timestamptz - '2021-02-15'::timestamptz); ?column? ---------- 273 days (1 row) postgres=> select age('2021-11-15'::timestamptz, '2021-02-15'::timestamptz); age -------- 9 mons (1 row) I just stored it in the "why you should always look at the manual before using interval" slots and went on. Note if you alter months to cross a single DST jump ( in here ) and repeat you would see more strange results. postgres=> set timezone TO 'Europe/Madrid'; SET postgres=> select age('2021-09-15'::timestamptz, '2021-02-15'::timestamptz), '2021-09-15'::timestamptz - '2021-02-15'::timestamptz; age | ?column? --------+------------------- 7 mons | 211 days 23:00:00 (1 row) It seems substraction is trying to preserve extract(epoch from (tza-tzb)) = extract(epoch from tza) - extract(epoch from tzb), but "beautifying" it a bit by using days. Docs should have it somewhere, but those chapters are a dense read. > In plain English, you can’t produce a cultural interval value by subtraction; subtraction can only populate a “day to second”flavor interval. Moreover, the computed “hours” component never exceeds 24 and the computed “days” component is alwaysthe horological value. Probably true. That seems to be the purpose of the age() function and is one of the reasons I normally avoid intervals. > I failed to find an explanation of this in the doc—but I dare say that my searching skills are too feeble. You could find some things like "SQL STD mandates it"·. It does mandate some really weird things. .... > I can guess the rules for the outcome when such a hybrid is added to a timestamptz value. It’s possible to design edgecase tests where you’d get different outcomes if: (a) the cultural component is added first and only then the horologicalcomponent is added; or (b) the components are added in the other order. It seems to me that the outcome is governedby rule (a). Am I right? I'm completely lost with the horological/cultural things. My understanding of the postgres intervals are: - 3 (internal) fields, months, days, seconds. Note they may have different signs! - months are sometimes printed as years, months because years have 12 months - seconds printed as hhmmss because minutes always have 60 seconds and hours 60 minutes ( disregarding leap seconds ) - When adding, IIRC, first add the months, then add the days, then add the seconds, rollig over the date as needed in each step. The very few cases where I've used it, mainly for calendaring, or for partitioning ( which is calendaring ), has worked well. Things like "meeting scheduled '3 months' from previous, confirmation mails sent '3 months, -14 days' from previous'. I've never tried to make some thing as complex as what you seem to be trying, I'll probably just roll my own datatype in that case justo to be sure the semantics are what I want. Regards. Francisco Olarte. > > B.t.w., I think that the specific complexities of the proleptic Gregorian calendar are cleanly separable from the basicidea that (considering only the requirements statement space) there is a real distinction to be drawn between “horological”and “cultural”—no matter what calendar rules might be used.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Adrian Klaver
Дата:
On 3/26/21 2:16 PM, Bryn Llewellyn wrote: > /Tom Lane wrote:/ > > Finally, I discovered that this is OK: > > *create table t(i interval);* > > But I can’t find a definition of the semantics of a bare interval. > However, I did find a column headed “Mixed Interval” at > https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE > <https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE>. > But the example values in the column are consistent with this: > > *select ((interval '2 years, 3 months, 4 days, 5 hours, 6 minutes 7.8 > seconds')::interval)::text as i;* > > This is the result: > > *2 years 3 mons 4 days 05:06:07.8* > > If you repeat the “select” using the typecast “*::interval month*” then > the other components are silently thrown away. But if you repeat it > using the typecast “*::interval second*” then all components are > preserved just as with bare “*interval*”. This muddies my idea that > there were three distinct interval flavors: horological, cultural, and > hybrid. Is the behavior that I’ve just shown intended? https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT "Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. For example, writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the seconds field, but not the day field." So you get: test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval month; interval ---------------- 2 years 3 mons (1 row) Equivalent to: test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval YEAR TO MONTH; interval ---------------- 2 years 3 mons test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval second; interval ---------------------------------- 2 years 3 mons 4 days 05:06:07.8 (1 row) Equivalent to: select '2 years 3 mons 4 days 05:06:07.8'::interval HOUR TO SECOND; interval ---------------------------------- 2 years 3 mons 4 days 05:06:07.8 (1 row) > > I can guess the rules for the outcome when such a hybrid is added to a > timestamptz value. It’s possible to design edge case tests where you’d > get different outcomes if: (a) the cultural component is added first and > only then the horological component is added; or (b) the components are > added in the other order. It seems to me that the outcome is governed by > rule (a). Am I right? > > B.t.w., I think that the specific complexities of the proleptic > Gregorian calendar are cleanly separable from the basic idea that > (considering only the requirements statement space) there is a real > distinction to be drawn between “horological” and “cultural”—no matter > what calendar rules might be used. Looking for logic in dates/times/calendars is a recipe for a continuous pounding headache. Not the least because horological = cultural. -- Adrian Klaver adrian.klaver@aklaver.com
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Bryn Llewellyn
Дата:
> On 27-Mar-2021, at 01:42, Francisco Olarte <folarte@peoplecall.com> wrote: > > Bryn, ( 1st, sorry if I misquote something, but i use text-only for the list )... Thanks again, Francisco. You said several things that clarify my understanding. Re your comment: > I've never tried to make some thing as complex as what you seem to be trying… What I’m trying to do is to understand interval semantics so that I can write a clear account of this topic. I do find thePostgreSQL doc on the topic difficult to use for this purpose because information is scattered and (as you said) it’sa dense read. This is why (though I don’t like the approach) I’m amassing empirical observations and trying to fit amental model to what I observe. Re your comment: > I'm completely lost with the horological/cultural things… My claim is that there’s a genuine distinction to be drawn in the conceptual domain—and that this can be made independentlyof any particular computer system. I might say that “1 day” is simply the same thing as “24 hours”, in thesame way that “1 foot” is the same as “12 inches”. But my discussion partner might argue with this saying that the lengthof one day is sometimes 23 hours and sometimes 25 hours due to the much-rehearsed arguments about DST. Here, I’d bethinking horologically and my discussion partner would be thinking culturally. Those two terms of art (or other equivalentones) are useful to stop a fist fight breaking out by allowing each discussion partner to understand, and label,the other’s mental model—both of which have merit. Notice that the same argument could be had about the equivalence of “1 minute” and “60 seconds” in the light of the “leapsecond” phenomenon. It just happens that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementationknows nothing of leap seconds. At least, this is how I interpret “because leap seconds are not handled” onthe https://www.postgresql.org/docs/11/functions-datetime.html page.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Bryn Llewellyn
Дата:
> On 27-Mar-2021, at 09:16, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > … Re Adrian’s quote of the doc thus: > Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. Yes, I do take this point. But there’s some murkiness here. All of my tests confirm that, for example, the declaration “intervalminute” to second” has the same effect as “interval day to second”. But you can’t even write “interval month tosecond” or “interval “year to second”. This suggests that there’s a hard boundary (using those words rather loosely) between“months” and “days”. This is consistent with this statement from the SQL Standard (1992 edition): « There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precisionthat includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-timeintervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. » Oracle Database honors this by allowing only two spellings of interval declaration “year to month” and “day to second”. Butthe fact that PostgreSQL allows a bare “interval” declaration that allows values of all six fields (years, months, days,hours, minutes, and seconds) is at odds with this. (In fact, it allows no fewer than _fourteen_ distinct ways to declarean interval—but these condense into only seven semantically distinct declarations. Red Adrian’s comment: > Looking for logic in dates/times/calendars is a recipe for a continuous pounding headache. Not the least because horological= cultural. I’m looking for a sufficient, and clear, way to describe the rules of what the PostgreSQL implementation actually does. AndI want to believe that at least some logical thinking informed the design. I rehearsed my argument that a genuine, anduseful, distinction can be made between the two terms of art in my reply to Francisco Olarte. I wrote: « My claim is that there’s a genuine distinction to be drawn in the conceptual domain—and that this can be made independentlyof any particular computer system. I might say that “1 day” is simply the same thing as “24 hours”, in thesame way that “1 foot” is the same as “12 inches”. But my discussion partner might argue with this saying that the lengthof one day is sometimes 23 hours and sometimes 25 hours due to the much-rehearsed arguments about DST. Here, I’d bethinking horologically and my discussion partner would be thinking culturally. Those two terms of art (or other equivalentones) are useful to stop a fist fight breaking out by allowing each discussion partner to understand, and label,the other’s mental model—both of which have merit. Notice that the same argument could be had about the equivalence of “1 minute” and “60 seconds” in the light of the “leapsecond” phenomenon. It just happens that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementationknows nothing of leap seconds. At least, this is how I interpret “because leap seconds are not handled” onthe https://www.postgresql.org/docs/11/functions-datetime.html page. » Here’s an example where (as I believe) I can use these terms to advantage: When you subtract two timeztamptz values which are greater apart than 24 hours, the “days”, “hours”, “minutes”, and “seconds”fields of the resulting interval are populated using horological semantics. But when you add an interval value toa timeztamptz value, the value of the “days” field is added using cultural semantics but the value of the “hours” fieldis added using horological semantics. This is possibly what lies behind the design choice that the “days” and “hours” values are explicitly separately represented.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Adrian Klaver
Дата:
On 3/29/21 3:32 PM, Bryn Llewellyn wrote: >> On 27-Mar-2021, at 09:16, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> … > > Re Adrian’s quote of the doc thus: > >> Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. > > Yes, I do take this point. But there’s some murkiness here. All of my tests confirm that, for example, the declaration“interval minute” to second” has the same effect as “interval day to second”. But you can’t even write “intervalmonth to second” or “interval “year to second”. This suggests that there’s a hard boundary (using those words ratherloosely) between “months” and > “days”. This is consistent with this statement from the SQL Standard (1992 edition): > > « There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precisionthat includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-timeintervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. » > > Oracle Database honors this by allowing only two spellings of interval declaration “year to month” and “day to second”.But the fact that PostgreSQL allows a bare “interval” declaration that allows values of all six fields (years, months,days, hours, minutes, and seconds) is at odds with this. (In fact, it allows no fewer than _fourteen_ distinct waysto declare an interval—but these condense into only seven semantically distinct declarations. https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-INTERVAL-INPUT "Also, a combination of years and months can be specified with a dash; for example '200-10' is read the same as '200 years 10 months'. (These shorter forms are in fact the only ones allowed by the SQL standard, and are used for output when IntervalStyle is set to sql_standard.)" My stock suggestion to anyway going over datatype-datetime.html and associated pages is to read them several times, take a break, then read them again several times. Then assume you still have not nailed down the if, and/or's, and buts and be prepared to go over them again. > > Red Adrian’s comment: > >> Looking for logic in dates/times/calendars is a recipe for a continuous pounding headache. Not the least because horological= cultural. > > I’m looking for a sufficient, and clear, way to describe the rules of what the PostgreSQL implementation actually does.And I want to believe that at least some logical thinking informed the design. I rehearsed my argument that a genuine,and useful, distinction can be made between the two terms of art in my reply to Francisco Olarte. I wrote: Again if you are looking for logic you are in the wrong place. If you really want to know what is going on then I suggest taking a look at the source, in particular: src/backend/utils/adt/datetime.c Bring aspirin and/or a stiff glass of something medicinal. > > « My claim is that there’s a genuine distinction to be drawn in the conceptual domain—and that this can be made independentlyof any particular computer system. I might say that “1 day” is simply the same thing as “24 hours”, in thesame way that “1 foot” is the same as “12 inches”. But my discussion partner might argue with this saying that the lengthof one day is sometimes 23 hours and sometimes 25 hours due to the much-rehearsed arguments about DST. Here, I’d bethinking horologically and my discussion partner would be thinking culturally. Those two terms of art (or other equivalentones) are useful to stop a fist fight breaking out by allowing each discussion partner to understand, and label,the other’s mental model—both of which have merit. > > Notice that the same argument could be had about the equivalence of “1 minute” and “60 seconds” in the light of the “leapsecond” phenomenon. It just happens that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementationknows nothing of leap seconds. At least, this is how I interpret “because leap seconds are not handled” onthe https://www.postgresql.org/docs/11/functions-datetime.html page. » > > Here’s an example where (as I believe) I can use these terms to advantage: > > When you subtract two timeztamptz values which are greater apart than 24 hours, the “days”, “hours”, “minutes”, and “seconds”fields of the resulting interval are populated using horological semantics. But when you add an interval value toa timeztamptz value, the value of the “days” field is added using cultural semantics but the value of the “hours” fieldis added using horological semantics. > > This is possibly what lies behind the design choice that the “days” and “hours” values are explicitly separately represented. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Adrian Klaver
Дата:
On 3/29/21 2:48 PM, Bryn Llewellyn wrote: >> On 27-Mar-2021, at 01:42, Francisco Olarte <folarte@peoplecall.com> wrote: >> >> Bryn, ( 1st, sorry if I misquote something, but i use text-only for the list )... > > Thanks again, Francisco. You said several things that clarify my understanding. Re your comment: > >> I've never tried to make some thing as complex as what you seem to be trying… > > What I’m trying to do is to understand interval semantics so that I can write a clear account of this topic. I do findthe PostgreSQL doc on the topic difficult to use for this purpose because information is scattered and (as you said)it’s a dense read. This is why (though I don’t like the approach) I’m amassing empirical observations and trying tofit a mental model to what I observe. > > Re your comment: > >> I'm completely lost with the horological/cultural things… > > My claim is that there’s a genuine distinction to be drawn in the conceptual domain—and that this can be made independentlyof any particular computer system. I might say that “1 day” is simply the same thing as “24 hours”, in thesame way that “1 foot” is the same as “12 inches”. But my discussion partner might argue with this saying that the lengthof one day is sometimes 23 hours and sometimes 25 hours due to the much-rehearsed arguments about DST. Here, I’d bethinking horologically and my discussion partner would be thinking culturally. Those two terms of art (or other equivalentones) are useful to stop a fist fight breaking out by allowing each discussion partner to understand, and label,the other’s mental model—both of which have merit. The point is horology is cultural, see non-Western calendars and alternate time keeping methods. Trying to maintain a distinction between the two concepts only furthers the confusion. The inconsistencies you see are the result of one(culture) intervening in the other(horology). > > Notice that the same argument could be had about the equivalence of “1 minute” and “60 seconds” in the light of the “leapsecond” phenomenon. It just happens that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementationknows nothing of leap seconds. At least, this is how I interpret “because leap seconds are not handled” onthe https://www.postgresql.org/docs/11/functions-datetime.html page. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Bryn Llewellyn
Дата:
> adrian.klaver@aklaver.com wrote: > > The point is horology is cultural, see non-Western calendars and alternate time keeping methods. Trying to maintain a distinctionbetween the two concepts only furthers the confusion. The inconsistencies you see are the result of one(culture)intervening in the other(horology). I intend the word “horology” to be taken in this sense: « The word "horology" means "the art of making clocks and watches". So the intended meaning of the phrase "horological interval"is "what you'd measure with a clock". The implication is "what you'd measure with the best clock that there is (inother words, a caesium clock) but expressed in seconds and multiples thereof (hours, and minutes, but not days).” » There’s nothing cultural about the size of the caesium unit. It simply emerges from the laws of physics. Maybe you don’tlike the word “horology”. I’m open to suggestions for a better term of art. But I hold fast to the idea that an atomic clock measures time and durations in one way and a calendar measures these ina different way. Seems to me that the whole business of calendars is nicely captured by the term “cultural”. Maybe I could use the terms “atomic clock time” and “calendar time”. The “to_timestamp()” built-in function maps from “atomic clock time” to “calendar time”. And the “extract… epoch…” constructmaps from “calendar time” to “atomic clock time”. Think of it like this: if you add the interval “24 hours” to a moment just before the US “spring forward” moment (using timestamptzand, say “US/Pacific" time zone), then you get one answer, But if you do the same exercise using the interval“1 day”, then you get a different answer. Tom Lane has said that this is intended. You need a vocabulary that yourinner voice can use when you decide, in the present application context, which of these is required. You can’t possiblyrehearse the whole discussion about atomic clocks and calendars every time this question comes up. Rather, you needterms of art to support your thinking. For example: « In the following, “interval arithmetic” denotes “t2 := t1 + i” (addition) and “i := t2 - t1” (subtraction). Interval arithmetic always uses cultural semantics for years and months. And it always uses horological semantics for hours,minutes and seconds*. Interval addition for days uses cultural semantics. But interval subtraction for days uses horologicalsemantics. * This is in the calendar regime where leap seconds are not accounted for. » You can’t write something like this without terms of art to support you. Thanks again for your helpful insights. I’ll stop now.
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
Adrian Klaver
Дата:
On 3/30/21 10:31 AM, Bryn Llewellyn wrote: >> adrian.klaver@aklaver.com wrote: >> >> The point is horology is cultural, see non-Western calendars and alternate time keeping methods. Trying to maintain adistinction between the two concepts only furthers the confusion. The inconsistencies you see are the result of one(culture)intervening in the other(horology). > > I intend the word “horology” to be taken in this sense: > > « The word "horology" means "the art of making clocks and watches". So the intended meaning of the phrase "horologicalinterval" is "what you'd measure with a clock". The implication is "what you'd measure with the best clock thatthere is (in other words, a caesium clock) but expressed in seconds and multiples thereof (hours, and minutes, but notdays).” » > > There’s nothing cultural about the size of the caesium unit. It simply emerges from the laws of physics. Maybe you don’tlike the word “horology”. I’m open to suggestions for a better term of art. > > But I hold fast to the idea that an atomic clock measures time and durations in one way and a calendar measures these ina different way. Seems to me that the whole business of calendars is nicely captured by the term “cultural”. > > Maybe I could use the terms “atomic clock time” and “calendar time”. Which are for practical purposes one and the same, otherwise we would not have leap seconds as a method of syncing the two. > > You can’t write something like this without terms of art to support you. > > Thanks again for your helpful insights. I’ll stop now. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value
От
"Peter J. Holzer"
Дата:
On 2021-03-30 12:36:16 -0700, Adrian Klaver wrote: > On 3/30/21 10:31 AM, Bryn Llewellyn wrote: > > > adrian.klaver@aklaver.com wrote: > > > > > > The point is horology is cultural, see non-Western calendars and > > > alternate time keeping methods. Trying to maintain a distinction > > > between the two concepts only furthers the confusion. The > > > inconsistencies you see are the result of one(culture) intervening > > > in the other(horology). > > > > I intend the word “horology” to be taken in this sense: > > > > « The word "horology" means "the art of making clocks and watches". > > So the intended meaning of the phrase "horological interval" is > > "what you'd measure with a clock". The implication is "what you'd > > measure with the best clock that there is (in other words, a caesium > > clock) but expressed in seconds and multiples thereof (hours, and > > minutes, but not days).” » > > > > There’s nothing cultural about the size of the caesium unit. It > > simply emerges from the laws of physics. Maybe you don’t like the > > word “horology”. I’m open to suggestions for a better term of art. Most clocks are not atomic clocks. Their job is not to count physical seconds (or periods of the radiation corresponding to the transition between the two hyperfine levels of the fundamental unperturbed ground-state of the caesium-133 atom), but to display "civil time", which is very much a cultural construct. When I went to bed last weekend, my clock showed something after 3:00 despite the fact that only a few minutes before there was a 1 before the colon. That's how a clock should behave (unfortunately, the clocks in my coffee maker and my stove don't do that - I have to set the time twice a year). (Operating systems often have a "wall-clock time clock" and a "monotonic clock". The wall-clock time clock is expected to mimic a clock on the wall, including all the cultural baggage like leap seconds, daylight saving times (although that's usually added in a second layer). The monotonic clock is supposed to just count seconds at a fixed rate, like a stop watch.) > > But I hold fast to the idea that an atomic clock measures time and > > durations in one way and a calendar measures these in a different > > way. Seems to me that the whole business of calendars is nicely > > captured by the term “cultural”. > > > > Maybe I could use the terms “atomic clock time” and “calendar time”. > > Which are for practical purposes one and the same, otherwise we would not > have leap seconds as a method of syncing the two. I disagree. We have leap seconds exactly because they are not the same. Atomic clock time just counts at at a constant rate - it doesn't care about the Earth's rotation. People however (well, some people, at least those who made the rules) do care about that so they add a second every now and then to keep days in sync with the Earth's rotation (currently TAI and UTC differ by 37 seconds). hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"