From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|

To: | pgsql-general(at)postgresql(dot)org |

Subject: | Re: Absolute value of intervals |

Date: | 2009-11-02 11:16:37 |

Message-ID: | hcmf2l$55l$1@reversiblemaps.ath.cx |

Views: | Raw Message | Whole Thread | Download mbox | Resend email |

Thread: | |

Lists: | pgsql-general |

On 2009-10-27, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> On Tue, Oct 27, 2009 at 10:55:31AM -0400, Tom Lane wrote:

>> Sam Mason <sam(at)samason(dot)me(dot)uk> writes:

>> > On Tue, Oct 27, 2009 at 11:27:17AM -0300, Joshua Berry wrote:

>> >> I couldn't find the operator '@' for intervals

>>

>> > A simple SQL implementation would look like:

>>

>> > CREATE FUNCTION absinterval(interval) RETURNS interval

>> > IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';

>> > CREATE OPERATOR @ ( PROCEDURE = absinterval, RIGHTARG = interval );

>>

>> I think this came up again recently and somebody pointed out that the

>> correct definition isn't as obvious as all that.

>

> Hum, I think it is! :)

>

>> The components of

>> an interval can have different signs, so should abs('-1 day 1 hour') be

>> '1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like

>> '1 day -25 hours'?

>

> Funny, I used exactly that example when playing---although I spelled it

> '-1 day 25:00:00'!

>

> It all comes down to how you define things. I'd say my quick hack does

> the "right" thing, but yes I should have pointed out that the interval

> type has subs-structure that makes it's behavior non-obvious. My

> intuition as to why it's correct worked along these lines:

>

> 1) '10' can be defined as '1 hundred -90 units'.

not all days are 24 hours long,

some differ by one second or one hour from that.

months are even worse.

> If the absolute value of an interval was defined to strip out all the

> negation signs you'd get the "wrong" answers out.

I think a definition that defines abs(interval)

such that

extract( date-part from abs(interval)) = abs(extract( date-part from interval))

is not totally without merit.

> The awkward thing

> with intervals is the the components are not all of the same units, but

> I think the argument stands.

the awkward thing is that the units are not all related by fixed ratios.

60 minutes per hour

7 days per week

12 months per year

thses are not:

days per month, (gregorian calendar)

days per year, (leap year)

hours per day, (daylight saving)

and seconds per minute (leap second)

- Re: Absolute value of intervals at 2009-10-27 15:25:02 from Sam Mason

From | Date | Subject | |
---|---|---|---|

Next Message | Jasen Betts | 2009-11-02 11:22:00 | Re: Absolute value of intervals |

Previous Message | Jasen Betts | 2009-11-02 10:52:40 | Re: Absolute value of intervals |