Re: Absolute value of intervals

On Mon, Nov 02, 2009 at 10:52:40AM +0000, Jasen Betts wrote:
> what's the absolute value of '1month -30 days'::interval
>
> if I add it to the first of march it goes forwards if I add it to the

In march a month has 31 days.

> first of february if goes backwards. if I add it to the first of april

29 or 28 days.

> it goes nowhere.

30 days so the math below is 0.

> select '2008-02-01'::date + '1month -30days'::interval;
> select '2008-03-01'::date + '1month -30days'::interval;
> select '2008-04-01'::date + '1month -30days'::interval;
>
> I'm guessing that '1 month 30 days' is the only mathematically
> consistent answer, not that it's likely to be useful.

A month is not fixed width. It depends on which month you're talking
about.

And then there is http://en.wikipedia.org/wiki/Month. Ow.

Having given it some more 1am thinking the above probably works out to:

select '2008-02-01'::date + '1month -30days'::interval;

2008-02-01 + 1 month = 2008-03-01
2008-03-01 - 30 days = 2008-01-31 (feb has 29 days)

select '2008-03-01'::date + '1month -30days'::interval;

2008-03-01 + 1 month = 2008-04-01
2008-04-01 - 30 days = 2008-03-02 (march has 31 days)

select '2008-04-01'::date + '1month -30days'::interval;

2008-04-01 + 1 month = 2008-05-01
2008-05-01 - 30 days = 2008-04-01 (april has 30 days)

I'm guessing (no postgres avail to me atm) and half asleep so my math
my be boogered.

In the end, though, it all makes sense. :)

--
"A search of his car uncovered pornography, a homemade sex aid, women's
stockings and a Jack Russell terrier."
- http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html