On Tue, May 04, 2004 at 22:59:34 +0200, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > Are you saying that when PostgreSQL returns "... 3 mons ..." > as a representation of an interval I can safely assume that > when it calculated the number of months it used 30 days > regardless of the actual length of the month ? I couldn't find It only does this when there is no month to know the length of. Offhand the only way I know of to get this is to extract the epoch part of a month which combines the month/year part of the interval with the week/day/hour/minute/second part without knowing which particular months are being referred to. > that number mentioned anywhere and had not browsed the source > yet. That would also be contrary to what I thought. I assumed > the following would happen: > > select age('1999-2-2', '1999-3-2'); > select age('1999-5-2', '1999-6-2'); > > would both return "1 mon" (despite the first one being 28 days > and the second one being 31 days). No it doesn't do that. In those examples it knows what particular months are involved and can use the correct length. > I am now looking for a way to say: > > select age('1999-2-2', '1999-3-2', without months); > select age('1999-5-2', '1999-6-2', without months); > > and get "28 days" in the first and "31 days" in the second > result. select '1999-3-2'::date - '1999-2-2'::date; select '1999-6-2'::date - '1999-5-2'::date; > > However, if you say that "1 mon" is always considered 30 days > in this context I would expect to receive: That isn't what I said and that isn't what happens. > > 1) "1 mon -2 days" (it would return 28 days of course, I know) > 2) "1 mon 1 day" > > Neither 7.1 nor 7.4 return that. > > > You can extract "epoch" from the interval to get the total number of > > seconds in the interval (converting months to the number of seconds > > in 30 days) and then divide that by the appropiate amount. > That only works if the above holds true, eg the month must be > fixed to 30 days by the calculation *generating* the interval > representation. Applying epoch *after* the fact is no good, > does it, because the epoch() code won't know whether "1 mons" > is to be 28 or 29 or 30 or 31 days. Not exactly. Months are converted to 30 days in the above situation, but not always. > Am I missing something here ? Note that intervals store two different values in them. One is a time in months and another is in some multiple (possibly 1) of seconds. Often one or the other of these is zero, but not always. ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)