Re: '1 year' = '360 days' ????

On Fri, Oct 22, 2004 at 13:37:19 +0200,
Ricardo Perez Lopez <ricpelo(at)hotmail(dot)com> wrote:
> Hello everyone:
>
> I'm a PostgreSQL newbie, working now with dates, times, timestamps and
> intervals.
>
> I have three questions about the above:
>
> FIRST:
> --------
>
> I have observed that, for PostgreSQL, one year is actually 360 days:

No it isn't. The interval is stored as months and seconds. When
adding intervals to timestamps, adding months and adding seconds are
handled differently. Under some circumstances the months part gets
converted to seconds, and in that event a month is taken to be as
long as 30 days.

> SELECT '1 year'::timestamp = '360 days'::timestamp;
>
> ?column?
> -------------
> t
>
>
> Glubs! I believed that 1 year is 365 days, or 366 if leap. Is it normal?
>
>
> SECOND:
> -----------
>
> When I want to check how many time is between two dates, I have two options
> (which shows two different results):
>
> SELECT '30-09-04'::timestamp - '30-09-03'::timestamp,
> age('30-09-04'::timestamp, '30-09-03'::timestamp);
>
> ?column? | age
> -------------------------------
> @ 366 days | @ 1 year
>
>
> The results are different. If we compare the two results:
>
> SELECT ('30-09-04'::timestamp - '30-09-03'::timestamp) =
> age('30-09-04'::timestamp, '30-09-03'::timestamp);
>
>
> ?column?
> --------------
> f
>
>
> Obviously, it returns False, because I told in the first question, 1 year
> is 360 days for PostgreSQL.

That isn't really why. When you use age you get an interval with a mix of month
and seconds parts. If you subtract two timestamps then you get an interval
with just a seconds part.

>
> The question is: is it normal? Which of the two methods is the correct? To
> substract timestamps? Or to use the age function?
>
>
> THIRD:
> --------
>
> As I told in the second question, when I do:
>
> SELECT '30-09-04'::timestamp - '30-09-03'::timestamp;
>
> the result is:
>
> ?column?
> --------------
> @ 366 days
>
> The question is: is there any way to "normalize" the result, such that the
> result was:
>
> @ 1 year 1 day

Use age. If you do that, that is what the interval will look like internally.
I don't think there is an easy way to output the value of an interval
so that it looks like that. But if you use it in operations it should
do what you want. (Though you need to consider whether you want the
day added before or after you add the 12 months.)

>
> ?
>
> I think it's better (and more correct) "@ 1 year 1 day" rather than "@ 366
> days". Is there any way to achieve that?
>
> Thanks to all.
>
> Ricardo.
>
> _________________________________________________________________
> Horóscopo, tarot, numerología... Escucha lo que te dicen los astros.
> http://astrocentro.msn.es/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org