Обсуждение: [PATCH] Leading minus for negative time interval in ISO 8601

Hello!

I'd like to propose a simple patch to allow for negative ISO 8601
intervals with leading minus, e.g. -PT1H besides PT-1H. It seems that
standard isn't quite clear on negative duration. However, lots of
software use leading minus and expect/generate intervals in such forms
making those incompatible with current PostgreSQL decoding code.

All patch is doing is making a note of a leading minus and negates pg_tm
components along with fractional seconds. No other behavior change is
introduced.

--
Mikhail

Mikhail Titov <mlt@gmx.us> writes:
> I'd like to propose a simple patch to allow for negative ISO 8601
> intervals with leading minus, e.g. -PT1H besides PT-1H. It seems that
> standard isn't quite clear on negative duration.

"Isn't quite clear"?  ISTM that if the standard intended to allow that,
it'd be pretty clear.  I looked through the 8601 spec just now, and
I can't see any indication whatever that they intend to allow "-" before P.
It's hard to see why they'd bother with that introducer at all if
data can appear before it.

> However, lots of
> software use leading minus and expect/generate intervals in such forms
> making those incompatible with current PostgreSQL decoding code.

Which "lots of software" are you speaking of, exactly?  interval_in
has never had such a capability, and I don't recall previous complaints
about it.

The difference between a useful standard and a useless one is the
extent to which people obey the standard rather than adding random
extensions to it, so I'm not inclined to add such an extension
without a very well-grounded argument for it.

            regards, tom lane

On 06/03/20 22:46, Tom Lane wrote:
> "Isn't quite clear"?  ISTM that if the standard intended to allow that,
> it'd be pretty clear.  I looked through the 8601 spec just now, and
> I can't see any indication whatever that they intend to allow "-" before P.

Umm, did you see any indication that they intend to allow "-" /anywhere/
in a time interval (with the exception of between year and month, month
and day in the alternate form, as simple delimiters, not as minus?

(Maybe you did; I'm looking at a publicly-accessible 2016 draft.)

It looks like the whole idea of minusness has to be shoehorned into ISO 8601
by anyone who misses it, and that's been done different ways. I guess that's
the "isn't quite clear" part.

> Which "lots of software" are you speaking of, exactly?  interval_in
> has never had such a capability, and I don't recall previous complaints
> about it.

Java durations allow both the PostgreSQL-style minus on individual
components, and a leading minus that negates the whole thing. [1]
That explicitly says "The leading plus/minus sign, and negative values
for other units are not part of the ISO-8601 standard."

XML Schema (and therefore XML Query, which uses XML Schema data types)
allows only the leading minus. [2]

The XML Schema folks say their concept is "drawn from those of ISO 8601,
specifically durations without fixed endpoints." That's why they can get
away with just the single leading sign: they don't admit something like
P1M-1D which you don't know to call 27, 28, 29, or 30 days until you're
given an endpoint to hang it on.

I had to deal with that in [3].

Regards,
-Chap




[1]
https://docs.oracle.com/javase/8/docs/api/java/time/Duration.html#parse-java.lang.CharSequence-

[2] https://www.w3.org/TR/xmlschema11-2/#nt-durationRep

[3]

https://github.com/tada/pljava/blob/master/pljava-examples/src/main/java/org/postgresql/pljava/example/saxon/S9.java#L329

Chapman Flack <chap@anastigmatix.net> writes:
> On 06/03/20 22:46, Tom Lane wrote:
>> "Isn't quite clear"?  ISTM that if the standard intended to allow that,
>> it'd be pretty clear.  I looked through the 8601 spec just now, and
>> I can't see any indication whatever that they intend to allow "-" before P.

> Umm, did you see any indication that they intend to allow "-" /anywhere/
> in a time interval (with the exception of between year and month, month
> and day in the alternate form, as simple delimiters, not as minus?
> (Maybe you did; I'm looking at a publicly-accessible 2016 draft.)

I don't have an "official" copy either; I was looking at this draft:
https://www.loc.gov/standards/datetime/iso-tc154-wg5_n0038_iso_wd_8601-1_2016-02-16.pdf

I see this bit:

    [±] represents a plus sign [+] if in combination with the following
    element a positive value or zero needs to be represented (in this
    case, unless explicitly stated otherwise, the plus sign shall not be
    omitted), or a minus sign [−] if in combination with the following
    element a negative value needs to be represented.

but I agree that there's no clear application of that to intervals,
either overall or per-field.

> Java durations allow both the PostgreSQL-style minus on individual
> components, and a leading minus that negates the whole thing. [1]
> That explicitly says "The leading plus/minus sign, and negative values
> for other units are not part of the ISO-8601 standard."
> XML Schema (and therefore XML Query, which uses XML Schema data types)
> allows only the leading minus. [2]

Hm.  The slippery slope I *don't* want to be drawn down is somebody
arguing that we should change interval_out, because that would open
a whole Pandora's box of compatibility issues.  Maybe we should just
take the position that negative intervals aren't standardized, and
if you want to transport them using ISO format then you first need
to lobby ISO to fix that.

            regards, tom lane

On Wed, Jun  3, 2020 at  9:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ...
> ISTM that if the standard intended to allow that, it'd be pretty
> clear.  I looked through the 8601 spec just now, and I can't see any
> indication whatever that they intend to allow "-" before P.

To be fair, I do not have an access to 2019 edition that
seems to address negative duration, but what I can see from the wording
at
https://www.loc.gov/standards/datetime/iso-tc154-wg5_n0039_iso_wd_8601-2_2016-02-16.pdf
, it seems to be written without an idea of negative duration at all,
even PT-1D alikes supported by PostgreSQL. Also that PDF mentions comma
as a preferred sign for e.g. PT1,5D that PostgreSQL does not accept. I
understand though that PDF explicitly states it is not a standard.

> It's hard to see why they'd bother with that introducer at all if data
> can appear before it.

I'm not sure I follow. Do you mean to hard require for time/span to
start with P and nothing but that? If so, can we think of it as a
syntactic sugar? I.e. unary minus AND a normal, positive duration of
your liking that we just negate in-place.

>> However, lots of software use leading minus and expect/generate
>> intervals in such forms making those incompatible with current
>> PostgreSQL decoding code.
>
> Which "lots of software" are you speaking of, exactly?  interval_in
> has never had such a capability, and I don't recall previous complaints
> about it.

I was not talking about PG-centric software in particular. I had some
JavaScript libraries, Ruby on Rails, Java, Rust, Go in mind. Here is the
related issue for Rust https://github.com/rust-lang/rust/issues/18181
and some Go library
https://pkg.go.dev/github.com/rickb777/date/period?tab=doc#Parse (besides
the links I gave in the patch) to show examples of accepting minus prefix.

I presume no one complained much previously because offset can be (and
often is) stored as float in, e.g., seconds, and then offset * '@1
second'::interval. That looks a bit verbose and I'd prefer to keep
offset as interval and do no extra casting.

Take a look at w3c specs that refer to ISO 8601 as well. I understand,
that is not what PG is after, but here is an excerpt:

,----[ https://www.w3.org/TR/xmlschema-2/#duration ]
| One could also indicate a duration of minus 120 days as: -P120D.
| ...
| P-1347M is not allowed although -P1347M is allowed
`----

Note that the second example explicitly contradicts currently allowed PG
syntax. I presume if the standard was clear, there would be no such
ambiguity.

Not that I'm trying to introduce drastic changes, but to make PostgreSQL
to be somewhat more friendly to what it can accept directly without
dancing around.

--
Mikhail

> ...
>> Umm, did you see any indication that they intend to allow "-" /anywhere/
>> in a time interval (with the exception of between year and month, month
>> and day in the alternate form, as simple delimiters, not as minus?
>> (Maybe you did; I'm looking at a publicly-accessible 2016 draft.)
>
> I don't have an "official" copy either; I was looking at this draft:
> https://www.loc.gov/standards/datetime/iso-tc154-wg5_n0038_iso_wd_8601-1_2016-02-16.pdf

heh, no one has an up to date standard :-) Also that is the link I meant
to include in my first reply. From what I see at
https://www.iso.org/obp/ui/#iso:std:iso:8601:-2:ed-1:v1:en they (ISO) did
address negative values for components and also there is "3.1.1.7
negative duration" that would be nice to read somehow.

> I see this bit:
>
>     [±] represents a plus sign [+] if in combination with the following
>     element a positive value or zero needs to be represented (in this
>     case, unless explicitly stated otherwise, the plus sign shall not be
>     omitted), or a minus sign [−] if in combination with the following
>     element a negative value needs to be represented.

But nowhere near duration specification [±] is used whatsoever.

> Hm.  The slippery slope I *don't* want to be drawn down is somebody
> arguing that we should change interval_out, because that would open
> a whole Pandora's box of compatibility issues.  Maybe we should just
> take the position that negative intervals aren't standardized, and
> if you want to transport them using ISO format then you first need
> to lobby ISO to fix that.

I explicitly do NOT want to change anything on the way out. First, that
is how things are and we do not want to break anything. And, second, in
many cases client software can read either format. That is why I thought
it would be a trivial change. No output changes.

--
Mikhail

On Wed, Jun  3, 2020 at 11:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ...
> Maybe we should just take the position that negative intervals aren't
> standardized, and if you want to transport them using ISO format then
> you first need to lobby ISO to fix that.

Apparently ISO did "fix" this. I managed to get a copy of ISO
8601-2:2019(E) and I insist on reconsidering the patch. Here is an
excerpt from page 12 of the standard:

,----[ 4.4.1.9 Duration ]
| A duration in the reverse direction, called a "negative duration" in
| this document, can be expressed using the following representation based
| on the [duration] representation specified in ISO 8601-1:2019, 5.5.2. In
| this case, all time scale components within the duration representation
| shall be positive.
|
| duration(m) = [!]["-"][positiveDuration]
|
| where [positiveDuration] is the representation of a positive duration.
|
| EXAMPLE 1 '-P1000' in date represents the duration of 100 days in the
| reverse direction. The duration formula 'P3650 - PlOOO' results in
| 'P2650'.
|
| EXAMPLE 2 '-P1Y30' in date represents the duration of one year and three
| days in the reverse direction. The duration formula 'PSY60 - P1Y30'
| results in 'P4Y30'.
`----

Note (mine) exclamation sign [!] means the following is optional. Here
is the definition for positiveDuration:

,----[ positiveDuration ]
| representation of [duration] specified in ISO 8601-1:2019, 5.4.2 that
| contains only time scale components that have positive values
`----

However on page 41 the standard says:

,----[ 11.2 Durational units ]
| Individual duration units are allowed to have negative values. The
| following representation denoted as [durationalUnits(m)] accept negative values per component.
|
| durationUnits(m) = [yearE(m)][monthE(m)][weekE(m)][dayE(m))["T"][hourE(m)][minuteE(m)]
| (secondE(m)]
`----

And, finally, there is that

,----[ 11.3.2 Composite representation ]
| The composite representation of a duration is a more flexible and
| relaxed specification for duration than that of .ISO 8601-1:2019,
| 5.5.2. It accepts all expressions of the duration representation given
| in ISO 8601-1:2019, 5.5.2 and is given as follows.
|
| [!]["-"]["P"][ durationUnits(m)]
|
| where [durationUnits(m)] contains time scale components for expressing
| (positive or negative) duration (see 11.2).
|
| Expressions in the two examples below are valid in ISO 8601-1.
|
| EXAMPLE 1 'P3D', duration of three days.
| EXAMPLE 2 'P180Y800D', duration of one-hundred-and-eighty years and eight-hundred days.
|
| Expressions in the following four examples below are not valid in ISO
| 8601-1, but are valid as specified in this clause.
|
| EXAMPLE 3 'P3W2D', duration of three weeks and two days, which is 23 days (equivalent to the expression
| 'P23D'). In ISO 8601-1, ["W"] is not permitted to occur along with any other component.
| EXAMPLE 4 'PSYlOW', duration of five years and ten weeks.
| EXAMPLE 5 'P-3M-3DT1HSM', duration of three months and three days in the reverse direction, with one hour
| and five minutes in the original direction.
| EXAMPLE 6 'P-ZM-1D', duration in the reverse direction of two months and one day.
|
| When a minus sign is provided as prefix to the duration designator
| ["P"], the minus sign can be internalized into individual time scale
| components within the duration expression by applying to every time
| scale component within.
|
| EXAMPLE 7 '-P2M1D' is equivalent to 'P-2M-1D'.
| EXAMPLE 8 '-P5DT10H' is equivalent to 'P-5DT-10H'.
|
| When a minus sign is applied to a time scale component whose value is
| already negative (pointing to the reverse direction), it means that
| the direction of duration should be once again reversed and should be
| turned into a positive value.
|
| EXAMPLE 9 '-P8M-1D', duration in reverse, "eight months minus one day", is equivalent to 'P-8M1D', "eight
| months ago with a day ahead".
| EXAMPLE 10 '-P-5WT-18H30M', duration in reverse, "go back five weeks, eighteen hours but thirty minutes
| ahead", is equivalent to 'P5WT18H-30M', "go ahead five weeks, eighteen hours, but thirty minutes back".
|
| NOTE The exact duration for some time scale components can be known only when placed on the actual
| time scale, see D.2.
`----

On a side note, it also defines (4.4.2) exponential values, but I guess
we can pass on those for now.

--
Mikhail

On Tue, Jun  9, 2020 at 11:18:20PM -0500, Mikhail Titov wrote:
> On Wed, Jun  3, 2020 at 11:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > ...
> > Maybe we should just take the position that negative intervals aren't
> > standardized, and if you want to transport them using ISO format then
> > you first need to lobby ISO to fix that.
> 
> Apparently ISO did "fix" this. I managed to get a copy of ISO
> 8601-2:2019(E) and I insist on reconsidering the patch. Here is an
> excerpt from page 12 of the standard:

This shows the problem of trying to honor a standard which is not
publicly available.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee