|From:||Jasen Betts <jasen(at)xnet(dot)co(dot)nz>|
|Subject:||Re: Absolute value of intervals|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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)
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)
|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|