Re: [HACKERS] [SQL] Interval subtracting
For same reasons, i.e. a need for precision, I find it hard to accept the idea of mixing positive and negative units in the same interval. The plus or minus sign should be outside of the interval. The interval data type is really useful. I see no reason to restrict its usefulness with an arbitrary constraint. Date arithmetic is treacherous and INTERVAL is a lifesaver. Forcing a global sign on the interval would break interval arithmetic. How would you compute '1 month'::interval - '1 week'::interval ? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Scott Marlowe <[EMAIL PROTECTED]> writes: > For same reasons, i.e. a need for precision, I find it hard to accept > the idea of mixing positive and negative units in the same interval. The semantics are perfectly well defined, so I don't buy this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-08 at 06:07, Markus Schaber wrote: > Hi, Scott, > > Scott Marlowe wrote: > > >>But it isn't '-2 months, -1 day'. I think what you are saying is what I > >>am saying, that we should make the signs consistent. > > Pretty much. It just seems wrong to have different signs in what is > > essentially a single unit. > > > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > > again, maybe some folks do. It just seems wrong to me. > > But we say "quarter to twelve", at least in some areas on this planet. > > The problem is that months have different lengths. '2 months - 1 day' > can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 > month 30 days', depending on the timestamp we apply the interval. I made this point before. In the military they say 1145 or 2345 instead of quarter to twelve, because 1: there are two "quarter to twelves" a day, and 2: It's easy to get it confused. For same reasons, i.e. a need for precision, I find it hard to accept the idea of mixing positive and negative units in the same interval. The plus or minus sign should be outside of the interval. Then, it's quite certain what you mean. If you say select '2006-06-12'::date - interval '1 month 2 days' there is no ambiguity. If you say: select '2006-06-12'::date + interval '-1 month -2 days' do you mean (1 month - 2 days) subtracted from the date, or do you mean to subtract 1 month, then 2 days from the date? Putting the + or - outside the interval seems to make the most sense to me. Allowing them inside makes no sense to me. And colloquialisms aren't really a good reason. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
Hi, Scott, Scott Marlowe wrote: >>But it isn't '-2 months, -1 day'. I think what you are saying is what I >>am saying, that we should make the signs consistent. > Pretty much. It just seems wrong to have different signs in what is > essentially a single unit. > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > again, maybe some folks do. It just seems wrong to me. But we say "quarter to twelve", at least in some areas on this planet. The problem is that months have different lengths. '2 months - 1 day' can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:27, Bruce Momjian wrote: > Scott Marlowe wrote: > > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > > > Stephan Szabo wrote: > > > > > justify_days doesn't currently do anything with this result --- it > > > > > thinks its charter is only to reduce day components that are >= 30 > > > > > days. > > > > > However, I think a good case could be made that it should normalize > > > > > negative days too; that is, the invariant on its result should be > > > > > 0 <= days < 30, not merely days < 30. > > > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > > > I think it should be the later. It is best to have a single sign, and I > > > think it is possible in all cases: > > > > > > '2 mons -1 days' > > > > > > could be adjusted to '1 mons 29 days'. > > > > There's a part of me that thinks the WHOLE THING should be positive or > > negative: > > > > -(2 months 1 day) > > But it isn't '-2 months, -1 day'. I think what you are saying is what I > am saying, that we should make the signs consistent. Pretty much. It just seems wrong to have different signs in what is essentially a single unit. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing <[EMAIL PROTECTED]> writes: > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Sure, but if you want to represent that then you don't pass the value through justify_days(). The entire premise of justify_days() is that 1 month is interchangeable with 30 days and we should try to make the value "look nice" given that assumption. I think everyone's independently arrived at the same thought that justify_days should not produce a result with different signs for month and day (except for the case with month = 0, per my last message). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing wrote: > ?hel kenal p?eval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Right, but you asked to justify the days by calling the function. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 1 Mar 2006, Hannu Krosing wrote: > Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Right, but would you call justify_days on such an interval? '2 months -1 days' <> '1 mon 29 days', but '1 mon 60 days' is also <> '3 mons' in general usage. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
Scott Marlowe wrote: > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > There's a part of me that thinks the WHOLE THING should be positive or > negative: > > -(2 months 1 day) But it isn't '-2 months, -1 day'. I think what you are saying is what I am saying, that we should make the signs consistent. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > Stephan Szabo wrote: > > > justify_days doesn't currently do anything with this result --- it > > > thinks its charter is only to reduce day components that are >= 30 days. > > > However, I think a good case could be made that it should normalize > > > negative days too; that is, the invariant on its result should be > > > 0 <= days < 30, not merely days < 30. > > > > What about cases like interval '1 month -99 days', should that turn into > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > I think it should be the later. It is best to have a single sign, and I > think it is possible in all cases: > > '2 mons -1 days' > > could be adjusted to '1 mons 29 days'. There's a part of me that thinks the WHOLE THING should be positive or negative: -(2 months 1 day) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Interval subtracting
Stephan Szabo wrote: > > justify_days doesn't currently do anything with this result --- it > > thinks its charter is only to reduce day components that are >= 30 days. > > However, I think a good case could be made that it should normalize > > negative days too; that is, the invariant on its result should be > > 0 <= days < 30, not merely days < 30. > > What about cases like interval '1 month -99 days', should that turn into > interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Interval subtracting
Thank you all for suggestions and links. Currently, I am working on PostgreSQL 8.0.4., so I cannot use justify_*() functions. Regards, Milorad Poluga [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Interval subtracting
On Sat, 18 Feb 2006, Tom Lane wrote: > "Milen A. Radev" <[EMAIL PROTECTED]> writes: > > Milorad Poluga : > >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 > >>> days'::interval > >>> ?column? > >>> --- > >>> 3 mons -14 days > >>> > >>> Why not '2 mons 16 days' ? > > > Please read the last paragraph in section 8.5.1.4 of the manual > > (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) > > . It mentions the functions named "justify_days" and "justify_hours" > > that could do what you need. > > justify_days doesn't currently do anything with this result --- it > thinks its charter is only to reduce day components that are >= 30 days. > However, I think a good case could be made that it should normalize > negative days too; that is, the invariant on its result should be > 0 <= days < 30, not merely days < 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Interval subtracting
"Milen A. Radev" <[EMAIL PROTECTED]> writes: > Milorad Poluga напиÑа: >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 >>> days'::interval >>> ?column? >>> --- >>> 3 mons -14 days >>> >>> Why not '2 mons 16 days' ? > Please read the last paragraph in section 8.5.1.4 of the manual > (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) > . It mentions the functions named "justify_days" and "justify_hours" > that could do what you need. justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are >= 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 <= days < 30, not merely days < 30. Similarly for justify_hours. Comments anyone? Patch anyone? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Interval subtracting
Milorad Poluga написа:
> On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
>> Milorad Poluga написа:
>>> Hi all,
>>>
>>> Is there something incorrect in the above query ?
>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
>>> days'::interval
>>>
>>> ?column?
>>> ---
>>> 3 mons -14 days
>>>
>>> Why not '2 mons 16 days' ?
>>>
>>> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc
>>> (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>>
>> How many days are there in a month?
>>
>
> I beleive that a month is calculated on the 30-days base.
Are you sure? Where?
>
> One way to solve this problem is to use a neutal date element and make
> timestamps :
>
> SELECT age(('1990-01-01'::date + '10 years 1 mons 1
> days'::interval)::timestamp ,
> ('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
>
> age
> --
> 2 mons 16 days
>
Please read the last paragraph in section 8.5.1.4 of the manual
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
. It mentions the functions named "justify_days" and "justify_hours"
that could do what you need.
--
Milen A. Radev
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Interval subtracting
On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
> Milorad Poluga написа:
> > Hi all,
> >
> > Is there something incorrect in the above query ?
> > SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
> > days'::interval
> >
> > ?column?
> > ---
> > 3 mons -14 days
> >
> > Why not '2 mons 16 days' ?
> >
> > /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc
> > (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>
>
> How many days are there in a month?
>
I beleive that a month is calculated on the 30-days base.
One way to solve this problem is to use a neutal date element and make
timestamps :
SELECT age(('1990-01-01'::date + '10 years 1 mons 1 days'::interval)::timestamp
,
('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
age
--
2 mons 16 days
Regards,
Milorad Poluga
---
[EMAIL PROTECTED]
---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Interval subtracting
Milorad Poluga написа: Hi all, Is there something incorrect in the above query ? SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / How many days are there in a month? -- Milen A. Radev ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Interval subtracting
Hi all, Is there something incorrect in the above query ? SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / Thanks a lot, Milorad Poluga --- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
