Thread: '1 year' = '360 days' ????
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:
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.
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
?
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/
On Fri, Oct 22, 2004 at 13:37:19 +0200,
Ricardo Perez Lopez <ricpelo@hotmail.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@postgresql.org
"Ricardo Perez Lopez" <ricpelo@hotmail.com> writes:
> I have observed that, for PostgreSQL, one year is actually 360 days:
> SELECT '1 year'::timestamp = '360 days'::timestamp;
> ?column?
> -------------
> t
Nonsense.
regression=# SELECT '1 year'::timestamp = '360 days'::timestamp;
ERROR: invalid input syntax for type timestamp: "1 year"
How about telling us what you *really* did, instead of posting faked
examples?
There are some contexts in which an interval (not a timestamp) of 1
month will be taken as equivalent to 30 days, for lack of any better
idea, but it's not the case that Postgres doesn't know the difference.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> "Ricardo Perez Lopez" <ricpelo@hotmail.com> writes:
>> I have observed that, for PostgreSQL, one year is actually 360 days:
>
>> SELECT '1 year'::timestamp = '360 days'::timestamp;
>
>> ?column?
>> -------------
>> t
>
> Nonsense.
>
> regression=# SELECT '1 year'::timestamp = '360 days'::timestamp;
> ERROR: invalid input syntax for type timestamp: "1 year"
>
> How about telling us what you *really* did, instead of posting faked
> examples?
FWIW:
template1=# select '1 year'::interval = '360 days'::interval;
?column?
----------
t
(1 row)
template1=# select '1 year'::interval = '365 days'::interval;
?column?
----------
f
(1 row)
template1=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
-Doug
Doug McNaught <doug@mcnaught.org> writes:
> template1=# select '1 year'::interval = '360 days'::interval;
> ?column?
> ----------
> t
> (1 row)
Yeah, if you look at interval_cmp_internal() it's fairly obvious why.
I think that this definition is probably bogus, and that only intervals
that match exactly (equal months parts *and* equal seconds parts) should
be considered "equal". However the most obvious way to redefine it
(compare the months, and only if equal compare the seconds) would lead
to rather nonintuitive behaviors such as "'1 year' > '1000 days'".
Anybody have any thoughts about a better way to map the multicomponent
reality into a one-dimensional sorting order?
(Note also that as Bruno was just mentioning, we really ought to have
months/days/seconds components, not just months/seconds; which makes the
comparison issue even more interesting.)
regards, tom lane
On Sat, Oct 23, 2004 at 21:38:15 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Doug McNaught <doug@mcnaught.org> writes: > > template1=# select '1 year'::interval = '360 days'::interval; > > ?column? > > ---------- > > t > > (1 row) > > Yeah, if you look at interval_cmp_internal() it's fairly obvious why. > I think that this definition is probably bogus, and that only intervals > that match exactly (equal months parts *and* equal seconds parts) should > be considered "equal". However the most obvious way to redefine it > (compare the months, and only if equal compare the seconds) would lead > to rather nonintuitive behaviors such as "'1 year' > '1000 days'". > Anybody have any thoughts about a better way to map the multicomponent > reality into a one-dimensional sorting order? You could return NULL for cases where the number of months in the first interval is less than the second, but the number of seconds in the second interval is greater than the first. You could even tighten things down more by using that months have to be at least 28 days, but not more than 31 days (neglecting daylight savings time). If you want to be able to use a btree index, you need a total ordering, so in that case I think you have to have things work pretty much the way they do now, including the way the equality operator works.
Bruno Wolff III <bruno@wolff.to> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Anybody have any thoughts about a better way to map the multicomponent
>> reality into a one-dimensional sorting order?
> You could return NULL for cases where the number of months in the
> first interval is less than the second, but the number of seconds in
> the second interval is greater than the first.
No, you can't, at least not if you want to have btree indexes on
interval columns. The comparison operators can never return NULL
for nonnull inputs.
> If you want to be able to use a btree index, you need a total ordering, so
> in that case I think you have to have things work pretty much the way they do
> now, including the way the equality operator works.
We don't have to have this particular sorting decision, we just have
to have *some* unique sorting order. In particular, if we want to say
that two interval values are not equal, we have to be able to say which
one is less. For instance, "compare the months first and only if equal
compare the seconds" would work fine from the point of view of btree.
It's just that that leads to a sort order that users will probably not
like very much.
regards, tom lane
On Sat, Oct 23, 2004 at 23:36:05 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > We don't have to have this particular sorting decision, we just have > to have *some* unique sorting order. In particular, if we want to say > that two interval values are not equal, we have to be able to say which > one is less. For instance, "compare the months first and only if equal > compare the seconds" would work fine from the point of view of btree. > It's just that that leads to a sort order that users will probably not > like very much. One way to do comparisons is to use a mapping f(m,s) => R and compare (m1,s1) and (m2,s2) by comparing f(m1,s1) and f(m2,s2) and break ties by comparing say m1 and m2. This will work as long as f(m,s1) = f(m,s2) implies s1 = s2. It will probably be desirable to use a subset of these mappings where f(m,s) = g(m) + h(s). In fact the current system uses this with g(m) = 30*24*60*60*m and h(s) = s (but without the tiebreak that compares m values). Because of the way intervals work, I think you want to use an ordering generated like that you want to use something of the form f(m,s) = C1*m + C2*s. I also think that treating a month as 30 days and having round numbers is better than using something like 1/12 a solar year in seconds. So I think the best plan is to do things as they are now, except for adding a tie breaker just using months or seconds for when both intervals give the same number of seconds when treating months as 30 days, but have a different number of months.
On Sat, Oct 23, 2004 at 23:15:57 -0500, Bruno Wolff III <bruno@wolff.to> wrote: > by comparing say m1 and m2. This will work as long as f(m,s1) = f(m,s2) > implies s1 = s2. It will probably be desirable to use a subset of these > mappings where f(m,s) = g(m) + h(s). In fact the current system uses > this with g(m) = 30*24*60*60*m and h(s) = s (but without the tiebreak > that compares m values). Because of the way intervals work, I think > you want to use an ordering generated like that you want to use > something of the form f(m,s) = C1*m + C2*s. I also think that treating > a month as 30 days and having round numbers is better than using > something like 1/12 a solar year in seconds. So I think the best plan > is to do things as they are now, except for adding a tie breaker just > using months or seconds for when both intervals give the same number of > seconds when treating months as 30 days, but have a different number of > months. Some more comments on this. I was thinking about it a bit more and using 1/12 of the number of seconds in a solar year doesn't seem that bad for comparisons. That way 366 days > 1 year > 365 days. However, if you go that route, I think you would also want to change EXTRACT so that when you extract the EPOCH you use the same function as for comparison. One value I found for a solar year is 365 days, 5 hours, 48 minutes, 45.51 seconds.
On Sat, Oct 23, 2004 at 23:51:20 -0500, Bruno Wolff III <bruno@wolff.to> wrote: > One value I found for a solar year is 365 days, 5 hours, 48 minutes, 45.51 > seconds. Wikipedia gives 365.242189670 days (86400 seconds) as the length of the mean solar year in 2000. To give you some idea of how constant that values is, Wikipedia claims that 2000 years ago the mean solar year was about 10 seconds longer. Using the above value I get there is an average of 2629743 seconds in a month. And yet another option is to note that in the Gregorian calendar there are 400*365+97 days or 400*12 months in 400 years, which gives 2629746 seconds per month on average.
problem is that '1 months':: interval does not have the same value if you
add it to a date or another :
=> SELECT '2004-02-01'::timestamp+'1 month'::interval,
'2004-03-01'::timestamp+'1 month'::interval;
?column? | ?column?
---------------------+---------------------
2004-03-01 00:00:00 | 2004-04-01 00:00:00
SELECT '2004-03-01'::timestamp-'2004-02-01'::timestamp,
'2004-04-01'::timestamp-'2004-03-01'::timestamp;
?column? | ?column?
----------+----------
29 days | 31 days
That's because a month is an undefined number of days (also some years
are 366 days). In that case '1 months':: interval is either 29 or 31 days
but it could be 28 in february 2003 or 30 in april !
Thus if we have a date d and two intervals i1 and i2 :
The comparison (d+i1) < (d+i2) depends on the value of d (and the
timezone).
For instance if i1 is '1 month' and i2 is '30 days', we have :
SELECT '2004-02-01'::timestamp+'1 month'::interval,
'2004-02-01'::timestamp+'30 days'::interval;
?column? | ?column?
---------------------+---------------------
2004-03-01 00:00:00 | 2004-03-02 00:00:00
Thus (d+i1) < (d+i2)
SELECT '2004-04-01'::timestamp+'1 month'::interval,
'2004-04-01'::timestamp+'30 days'::interval;
?column? | ?column?
---------------------+---------------------
2004-05-01 00:00:00 | 2004-05-01 00:00:00
Thus (d+i1) = (d+i2)
SELECT '2004-03-01'::timestamp+'1 month'::interval,
'2004-03-01'::timestamp+'30 days'::interval;
?column? | ?column?
---------------------+---------------------
2004-04-01 00:00:00 | 2004-03-31 00:00:00
Thus (d+i1) > (d+i2)
And that's normal ! Intervals having months are extremely useful to
express the idea of 'same day, next month' that you can't do with just an
interval expressed in seconds. However, beware :
SELECT '2004-01-31'::timestamp+'1 month'::interval;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-30'::timestamp+'1 month'::interval;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-29'::timestamp+'1 month'::interval;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-28'::timestamp+'1 month'::interval;
?column?
---------------------
2004-02-28 00:00:00
31 january + 1 month = 29 february (it clips at the end of the month,
which is IMHO GOOD).
How can we sort intervals meaningfully in these conditions ? Can we ? In
fact the value of an interval depends on the application, and intervals
with months are in another 'world' than intervals with only seconds...
same thing for years.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
On Oct 24, 2004, at 4:13 PM, Pierre-Frédéric Caillaud wrote: > How can we sort intervals meaningfully in these conditions ? Can we ? > In fact the value of an interval depends on the application, and > intervals with months are in another 'world' than intervals with only > seconds... same thing for years. Added to this, I've been wondering whether '1 day'::interval is also problematic wrt daylight savings time or changing time zones. The whole thing seems pretty hairy to me. Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> Added to this, I've been wondering whether '1 day'::interval is also
> problematic wrt daylight savings time or changing time zones.
This is exactly the point I alluded to earlier: intervals need to have
three components (months, days, seconds) not just two. That's been on
the to-do list for quite awhile. All the other units we support for
intervals bear a fixed relationship to one or another of these, so
three is sufficient.
Question to think about: should we allow fractional months or days in
the stored representation? There are some places where the existing
restriction that the months field is an integer requires awkward
compromises. On the other hand, it's not real clear what a fractional
month actually means, and similarly a fractional day is hard to assign
meaning to without positing that 1 day == 24 hours.
regards, tom lane
On Sun, Oct 24, 2004 at 11:29:13 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Question to think about: should we allow fractional months or days in > the stored representation? There are some places where the existing > restriction that the months field is an integer requires awkward > compromises. On the other hand, it's not real clear what a fractional > month actually means, and similarly a fractional day is hard to assign > meaning to without positing that 1 day == 24 hours. There are reasonable addition and subtraction operation definitions on two intervals. There might be some application where you want to keep track of fractional months or days. What I am not sure of is would you really have a reason to add fractional months or days to a timestamp. There are a couple reasonable definitions you might make for this definition, but I don't really see a good reason to want this. ne thing to note, when adding intervals you can add the fractions normally. When adding to a date you can get the actual length of the day or month the fractional part adds to, if you wanted to use that information. (Though the resulting day or month may not be the one you added the fractional month to.) I think starting with a type where months and days were integers would be OK, since you could generalize it to handle fractional months and days later and not break applications. Another thing to think about when designing this type, is that when adding timestamps and intervals it makes a difference in which order you add the months, days and seconds.
Bruno Wolff III <bruno@wolff.to> writes:
> Wikipedia gives 365.242189670 days (86400 seconds) as the length of
> the mean solar year in 2000. To give you some idea of how constant
> that values is, Wikipedia claims that 2000 years ago the mean solar
> year was about 10 seconds longer. Using the above value I get there
> is an average of 2629743 seconds in a month.
> And yet another option is to note that in the Gregorian calendar there are
> 400*365+97 days or 400*12 months in 400 years, which gives 2629746 seconds
> per month on average.
I like the latter approach, mainly because it gives a defensible
rationale for using a particular exact value. With the solar-year
approach there's no strong reason why you should use 2000 (or any other
particular year) as the reference; and any value you did use would be
subject to both roundoff and observational error. With the Gregorian
calendar as reference, 2629746 seconds is the *exact* answer, and it's
correct because the Pope says so ;-).
(Or, for the Protestants among us, it's correct because the SQL standard
specifies use of the Gregorian calendar.)
regards, tom lane
>From: Tom Lane <tgl@sss.pgh.pa.us> >To: "Ricardo Perez Lopez" <ricpelo@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] '1 year' = '360 days' ???? Date: Sat, 23 Oct 2004 >19:52:50 -0400 > >"Ricardo Perez Lopez" <ricpelo@hotmail.com> writes: > > I have observed that, for PostgreSQL, one year is actually 360 days: > > > SELECT '1 year'::timestamp = '360 days'::timestamp; > > > ?column? > > ------------- > > t > >Nonsense. > >regression=# SELECT '1 year'::timestamp = '360 days'::timestamp; >ERROR: invalid input syntax for type timestamp: "1 year" > >How about telling us what you *really* did, instead of posting faked >examples? Sorry: it's an errata. The query is, actually: SELECT '1 year'::interval = '360 days'::interval; Sorry about the inconvenience. >There are some contexts in which an interval (not a timestamp) of 1 >month will be taken as equivalent to 30 days, for lack of any better >idea, but it's not the case that Postgres doesn't know the difference. > > regards, tom lane Thanks. _________________________________________________________________ Acepta el reto MSN Premium: Protección para tus hijos en internet. Descárgalo y pruébalo 2 meses gratis. http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_proteccioninfantil
Tom Lane wrote: >Doug McNaught <doug@mcnaught.org> writes: > > >>template1=# select '1 year'::interval = '360 days'::interval; >> ?column? >>---------- >> t >>(1 row) >> >> > >Yeah, if you look at interval_cmp_internal() it's fairly obvious why. >I think that this definition is probably bogus, and that only intervals >that match exactly (equal months parts *and* equal seconds parts) should >be considered "equal". However the most obvious way to redefine it >(compare the months, and only if equal compare the seconds) would lead >to rather nonintuitive behaviors such as "'1 year' > '1000 days'". >Anybody have any thoughts about a better way to map the multicomponent >reality into a one-dimensional sorting order? > >(Note also that as Bruno was just mentioning, we really ought to have >months/days/seconds components, not just months/seconds; which makes the >comparison issue even more interesting.) > > regards, tom lane > > As any of us who have ever researched how to calculate time know; 1) The amount of time in 1 year depends on the year due to leap years. 2) The amount of time in 1 month depends on the month and year because a month is an arbitrary number of days. 3) A week is a theological creation always equal to 7 days. Using the Gregorian Calendar there are 10 missing days between Oct. 4, 1582 and Oct. 15, 1582 . Leap Years are (((every 4 years) except when modulo 100) except when modulo 400). It is therefore not possible to define a Month or Year in Seconds, without knowing which Day, Month and Year you calculating. Time constants : 1 Solar Day = 23 hours 56 minutes 4.091 seconds 1 Lunar Month = 27.32158 days 1 Tropical Year = 365.24215 Solar Days 1 Year in Gregorian time is : 365 Days 5 Hours 49 Minutes 12 Seconds As it is now obvious there is not any simple way to convert months to seconds since a month is an abstract number of days used to split four (13 week) seasons three ways plus one day every non leap year and two days every leap year. When calculating any usage based on time, it is a good idea to store usage in days:hours:minutes:seconds because they are static and stable, if you discount the deceleration of the earth and corrections in leap seconds for atomic clocks [see http://tycho.usno.navy.mil/leapsec.html ]. Trivia: In approximately 620 million years a day will be twice as long as it is today. -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.
Tom Lane wrote: >Bruno Wolff III <bruno@wolff.to> writes: > > >>Wikipedia gives 365.242189670 days (86400 seconds) as the length of >>the mean solar year in 2000. To give you some idea of how constant >>that values is, Wikipedia claims that 2000 years ago the mean solar >>year was about 10 seconds longer. Using the above value I get there >>is an average of 2629743 seconds in a month. >> >> >>And yet another option is to note that in the Gregorian calendar there are >>400*365+97 days or 400*12 months in 400 years, which gives 2629746 seconds >>per month on average. >> >> > >I like the latter approach, mainly because it gives a defensible >rationale for using a particular exact value. With the solar-year >approach there's no strong reason why you should use 2000 (or any other >particular year) as the reference; and any value you did use would be >subject to both roundoff and observational error. With the Gregorian >calendar as reference, 2629746 seconds is the *exact* answer, and it's >correct because the Pope says so ;-). > >(Or, for the Protestants among us, it's correct because the SQL standard >specifies use of the Gregorian calendar.) > > regards, tom lane > > Give or take one day every 4000 years. ;-) -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.
Guy Fraser wrote: > Trivia: In approximately 620 million years a day will be twice as long > as it is today. Do you think then that Postgres628M.0 will fix it ? :-) Regards Gaetano Mendola
Gaetano Mendola wrote: > Guy Fraser wrote: > >> Trivia: In approximately 620 million years a day will be twice as >> long as it is today. > > Do you think then that Postgres628M.0 will fix it ? :-) > > Regards > Gaetano Mendola I just hope, I don't have to work an equivalent fraction of the day for the same pay, but with any luck I'll have all my bills paid and be retired by then. ;-) -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.
On Wed, Oct 27, 2004 at 16:26:13 -0600, Guy Fraser <guy@incentre.net> wrote: > > When calculating any usage based on time, it is a good idea to > store usage in days:hours:minutes:seconds because they are static > and stable, if you discount the deceleration of the earth and > corrections in leap seconds for atomic clocks [see > http://tycho.usno.navy.mil/leapsec.html ]. The length of calendar days isn't constant. In many timezones, one day a year is 23 hours long and another is 25 hours long. Having month and year intervals is useful for events that repeat monthly or yearly in spite of there not being a constant number of seconds between events.