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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 6: explain analyze is your friend
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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Attached is the new patch. To summarize:
- new function justify_interval(interval)
- modified function justify_hours(interval)
- modified function justify_days(interval)
These functions are defined to meet the requirements as discussed in this
thread. Specifically:
- justify_hours makes certain the sign bit on the hours
matches the sign bit on the days. It only checks the
sign bit on the days, and not the months, when
determining if the hours should be positive or negative.
After the call, -24 hours 24.
- justify_days makes certain the sign bit on the days
matches the sign bit on the months. It's behavior does
not depend on the hours, nor does it modify the hours.
After the call, -30 days 30.
- justify_interval makes sure the sign bits on all three
fields months, days, and hours are all the same. After
the call, -24 hours 24 AND -30 days 30.
'make check' passes all tests. There are no tests for justify_interval, as it
is new. But the existing tests for justify_hours and justify_days appear to
still work, even though the behavior has changed. Apparently, their test cases
are not sensitive to the particular changes that have occurred.
I would include new tests in the patch but do not know on which reference
machine/platform the patches are supposed to be generated.
mark
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c 22 Nov 2005 22:30:33 - 1.160
--- src/backend/utils/adt/timestamp.c 3 Mar 2006 20:23:26 -
***
*** 1975,1980
--- 1975,2054
}
/*
+ * interval_justify_interval()
+ *
+ * Adjust interval so 'month', 'day', and 'time' portions are within
+ * customary bounds. Specifically:
+ *
+ *0 = abs(time) 24 hours
+ *0 = abs(day) 30 days
+ *
+ * Also, the sign bit on all three fields is made equal, so either
+ * all three fields are negative or all are positive.
+ */
+ Datum
+ interval_justify_interval(PG_FUNCTION_ARGS)
+ {
+ Interval *span = PG_GETARG_INTERVAL_P(0);
+ Interval *result;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+ int64 wholeday;
+ #else
+ double wholeday;
+ #endif
+ int32 wholemonth;
+
+ result = (Interval *) palloc(sizeof(Interval));
+ result-month = span-month;
+ result-day = span-day;
+ result-time = span-time;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+ TMODULO(result-time, wholeday, USECS_PER_DAY);
+ #else
+ TMODULO(result-time, wholeday, (double) SECS_PER_DAY);
+ #endif
+ result-day += wholeday;/* could overflow... */
+
+ wholemonth = result-day / DAYS_PER_MONTH;
+ result-day -= wholemonth * DAYS_PER_MONTH;
+ result-month += wholemonth;
+
+ if (result-month 0 result-day 0)
+ {
+ result-day -= DAYS_PER_MONTH;
+ result-month++;
+ }
+ else if (result-month 0 result-day 0)
+ {
+ result-day += DAYS_PER_MONTH;
+ result-month--;
+ }
+
+ if (result-time 0 result-day 0)
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ result-time += USECS_PER_DAY;
+ #else
+ result-time += (double) SECS_PER_DAY;
+ #endif
+ result-day--;
+ }
+ else if (result-time 0 result-day 0)
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ result-time -= USECS_PER_DAY;
+ #else
+ result-time -= (double) SECS_PER_DAY;
+ #endif
+ result-day++;
+ }
+
+ PG_RETURN_INTERVAL_P(result);
+ }
+
+ /*
*interval_justify_hours()
*
*Adjust interval so 'time' contains less than a whole day, adding
***
*** 2006,2011
--- 2080,2104
#endif
result-day += wholeday;/* could overflow... */
+ if (result-time 0 result-day 0)
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ result-time += USECS_PER_DAY;
+ #else
+ result-time += (double) SECS_PER_DAY;
+ #endif
+ result-day--;
+ }
+ else if (result-time 0 result-day 0)
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ result-time -= USECS_PER_DAY;
+ #else
+ result-time -= (double) SECS_PER_DAY;
+ #endif
+ result-day++;
+ }
+
PG_RETURN_INTERVAL_P(result);
}
***
*** 2031,2036
--- 2124,2140
result-day -= wholemonth * DAYS_PER_MONTH;
result-month += wholemonth;
+ if (result-month 0 result-day 0)
+ {
+ result-day -= DAYS_PER_MONTH;
+ result-month++;
+ }
+ else if (result-month 0 result-day 0)
+ {
+ result-day +=
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: The problem is that you can't determine what answer justify_days would give without using the assumption 1 month == 30 days, which is an assumption that justify_hours must not depend on. Ahhh. So the fact that justify_days already makes the 1 month == 30 days assumption is ok in that function but can't be propagated to justify_hours. Right. I don't want us to define things so that none of this functionality is available in situations where the 30-day assumption is untenable. justify_hours can still do something useful (ie, trim oversize hours fields) without that. justify_interval will probably be the new normal way to do things when you are prepared to make both assumptions. I'm not entirely sure about the use-case for justify_days, but seems we ought to keep it for reasons of backwards compatibility. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote: Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: 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 do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. But the military says 1515 or 1545 or 0315 or 0345, because if they get the time wrong they shell the wrong place and kill their own soldiers. I.e. getting it right is important to them. So they use exact language. I prefer the more exact way. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Ühel kenal päeval, N, 2006-03-02 kell 10:13, kirjutas Scott Marlowe: On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote: Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: .. 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 do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. But the military says 1515 or 1545 or 0315 or 0345, because if they get the time wrong they shell the wrong place and kill their own soldiers. do they also speak so of intervals ? hannu=# select now(), now() - '-1 hour ago'::interval; now | ?column? ---+--- 2006-03-02 19:47:38.042408+02 | 2006-03-02 18:47:38.042408+02 (1 row) You see what I mean - perfectly precise and unconfusable :) I.e. getting it right is important to them. So they use exact language. I prefer the more exact way. And in the current global era they must also speak in GMT all the time, to avoid any confusion :P -- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
On Thu, Mar 02, 2006 at 10:06:26AM -0800, Mark Dilger wrote: I used grep -R to find all locations where interval_justify_time is mentioned, and for each one added an analogous entry for my new function interval_justify. But I get lost where OID=1175 is associated with interval_justify_time. I can't really just grep for 1175 and add analogous entries of my new OID, because I don't know what all the tables mean. (BTW, I grabbed the next slot in the table and used OID=2711, but I don't know if that is the proper strategy for adding new OIDs.) Is there an automated way of handling this task, such as a developer tool that I didn't find? In the include/catalog directory there are two script, unused_oids and duplicate_oids. It tells you whats available. Have a ncie day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: I've written the interval_justify() function but the parser does not know about it yet. The pg_proc change is the only source change you need for that, but afterwards you need to update the postgres.bki file (handled by make and make install in src/backend) and then initdb. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
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. Similarly for justify_hours. Comments anyone? Patch anyone? Sure, if nobody objects to this change I can write the patch. mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger wrote:
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. Similarly for justify_hours.
Comments anyone? Patch anyone?
Sure, if nobody objects to this change I can write the patch.
Good question. Should we restrict days to 0 - 30 or -30 - 30? The
current system does the later:
test= select justify_days('-45 days');
justify_days
--
-1 mons -15 days
(1 row)
test= select justify_days('1 month -45 days');
justify_days
--
-15 days
(1 row)
test= select justify_days('1 month -15 days');
justify_days
1 mon -15 days
(1 row)
Should we be adjusting the last one? I am unsure. Comments?
--
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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger wrote:
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. Similarly for justify_hours.
Comments anyone? Patch anyone?
Sure, if nobody objects to this change I can write the patch.
mark
I've modified the code and it now behaves as follows:
select justify_days('3 months -12 days'::interval);
justify_days
2 mons 18 days
select justify_days('3 months -33 days'::interval);
justify_days
---
1 mon 27 days
select justify_hours('3 months -33 days -12 hours'::interval);
justify_hours
---
3 mons -34 days +12:00:00
select justify_days(justify_hours('3 months -33 days -12 hours'::interval));
justify_days
1 mon 26 days 12:00:00
select justify_hours('-73 hours'::interval);
justify_hours
---
-4 days +23:00:00
select justify_days('-62 days'::interval);
justify_days
--
-3 mons +28 days
I find the last two results somewhat peculiar, as the new functionality pushes
the negative values upwards (from hours to days, days to months). Changing '-73
hours' to '-3 days -1 hour' might be more intuitive? The '-4 days +23 hours' is
however consistent with the behavior in the other cases.
Thoughts? I will package this up into a patch fairly soon.
mark
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
On Wed, Mar 01, 2006 at 12:59:29PM -0500, Bruce Momjian wrote:
Good question. Should we restrict days to 0 - 30 or -30 - 30? The
current system does the later:
test= select justify_days('-45 days');
justify_days
--
-1 mons -15 days
(1 row)
test= select justify_days('1 month -45 days');
justify_days
--
-15 days
(1 row)
test= select justify_days('1 month -15 days');
justify_days
1 mon -15 days
(1 row)
Should we be adjusting the last one? I am unsure. Comments?
ISTM it should be looking at the sign of the overall interval, and
sticking with that consistently. So while '1 mon 5 days' and '-3 mon -8
days' both make sense, '1 mon -2 days' doesn't make nearly as much sense
in the general case. Of course this is complicated by the fact that '1
mon 20 days' doesn't necessarily equate to '2 mon -10 days'... :(
One of these days we should just create a new calendar. ;)
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(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
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger wrote:
select justify_hours('-73 hours'::interval);
justify_hours
---
-4 days +23:00:00
select justify_days('-62 days'::interval);
justify_days
--
-3 mons +28 days
I find the last two results somewhat peculiar, as the new functionality
pushes the negative values upwards (from hours to days, days to months).
Changing '-73 hours' to '-3 days -1 hour' might be more intuitive?
The '-4 days +23 hours' is however consistent with the behavior in the
other cases.
I don't think we can accept a change that takes a negative and turns it
into a positive and negative. I think the answer to the last one should
be '-2 mons -2 days', which is what it does now:
test= select justify_days('-62 days'::interval);
justify_days
-
-2 mons -2 days
(1 row)
The open question is whether we should convert a positive and negative
to a positive, or a negative, based on the sign of the highest value,
e.g. convert '1 mons -10 days' to '20 days', and '-1 mons 10 days' to
'-20 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: [HACKERS] [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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [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 5: don't forget to increase your free space map settings
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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Bruce Momjian pgman@candle.pha.pa.us writes: I don't think we can accept a change that takes a negative and turns it into a positive and negative. Yeah, I find the patch's changes to the regression results pretty disturbing. Perhaps the correct definition ought to be like if month part = 0 then the reduced day part should be between 0 and 30, otherwise the reduced day part should be between 0 and -30. However there are still corner cases to worry about. If the original month and day parts are of different sign, you might not be able to do such a reduction without changing the sign of the month part, consider 1 month -95 days. Not clear what to do with this. I guess I would expect a good result to satisfy one of these three cases: * month 0 and 0 = day 30 * month 0 and -30 day = 0 * month = 0 and -30 day 30 If you believe that then 1 month -95 days should justify to -2 months -5 days. regards, tom lane ---(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
Re: [HACKERS] [SQL] Interval subtracting
Ü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'. -- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I don't think we can accept a change that takes a negative and turns it into a positive and negative. Yeah, I find the patch's changes to the regression results pretty disturbing. Perhaps the correct definition ought to be like if month part = 0 then the reduced day part should be between 0 and 30, otherwise the reduced day part should be between 0 and -30. However there are still corner cases to worry about. If the original month and day parts are of different sign, you might not be able to do such a reduction without changing the sign of the month part, consider 1 month -95 days. Not clear what to do with this. I guess I would expect a good result to satisfy one of these three cases: * month 0 and 0 = day 30 * month 0 and -30 day = 0 * month = 0 and -30 day 30 If you believe that then 1 month -95 days should justify to -2 months -5 days. I believe it. :-) -- 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 2: Don't 'kill -9' the postmaster
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 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
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I don't think we can accept a change that takes a negative and turns it into a positive and negative. Yeah, I find the patch's changes to the regression results pretty disturbing. Perhaps the correct definition ought to be like if month part = 0 then the reduced day part should be between 0 and 30, otherwise the reduced day part should be between 0 and -30. However there are still corner cases to worry about. If the original month and day parts are of different sign, you might not be able to do such a reduction without changing the sign of the month part, consider 1 month -95 days. Not clear what to do with this. I guess I would expect a good result to satisfy one of these three cases: * month 0 and 0 = day 30 * month 0 and -30 day = 0 * month = 0 and -30 day 30 If you believe that then 1 month -95 days should justify to -2 months -5 days. regards, tom lane How would you expect justify_hours to behave? I extrapolate from your rules above that: * month 0 and 0 = day 30 and 0 = hours 24 * month 0 and -30 day = 0 and -24 hours = 0 * month = 0 and -30 day = 0 and -24 hours = 0 * month = 0 and 0 = day 30 and 0 = hours 24 Which would mean that '1 month -95 days -12 hours' should justify to -2 months -5 days -12 hours rather than -2 months -6 days 12 hours, but that '1 month -15 days -12 hours would justify to '14 days 12 hours' rather than '15 days -12 hours'. Is this correct? mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing wrote: 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'. Correct me if I am wrong, but I thought that justify_days would only be called if the user wanted it. I get the following behavior in psql even after the patch is applied: select '2 mons -1 days'::interval; interval 2 mons -1 days So there does not seem to be any justification going on without the user's permission. Consequently, if you need '2 mons -1 days', don't call justify_days. Am I missing something? mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: I guess I would expect a good result to satisfy one of these three cases: * month 0 and 0 = day 30 * month 0 and -30 day = 0 * month = 0 and -30 day 30 If you believe that then 1 month -95 days should justify to -2 months -5 days. How would you expect justify_hours to behave? I extrapolate from your rules above that: * month 0 and 0 = day 30 and 0 = hours 24 * month 0 and -30 day = 0 and -24 hours = 0 * month = 0 and -30 day = 0 and -24 hours = 0 * month = 0 and 0 = day 30 and 0 = hours 24 Hmmm ... I think it would be better if the two functions were independent, if possible. Your spec above implies that justify_hours implicitly does justify_days as well, which seems a bit restrictive. Furthermore, justify_hours should only assume that 1 day == 24 hours, which while broken by DST is still a lot solider assumption than justify_days' 1 month == 30 days. I can well believe that a lot of people only want to make the first assumption. So I'm inclined to think that justify_hours is responsible for reducing the seconds part to less-than-24-hours and pushing any overflow into the days part (but not touching months), while justify_days is responsible for reducing the days part to less-than-30-days and pushing any overflow into the months part (but not touching seconds). If you want both you apply both functions, probably in that order. (I wonder if there are any cases where applying justify_days before justify_hours would be useful. Offhand I can't see one ...) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 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
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote:
Mark Dilger [EMAIL PROTECTED] writes:
Tom Lane wrote:
I guess I would expect a good result to satisfy one of these three
cases:
* month 0 and 0 = day 30
* month 0 and -30 day = 0
* month = 0 and -30 day 30
If you believe that then 1 month -95 days should justify to
-2 months -5 days.
How would you expect justify_hours to behave? I extrapolate from your rules
above that:
* month 0 and 0 = day 30 and 0 = hours 24
* month 0 and -30 day = 0 and -24 hours = 0
* month = 0 and -30 day = 0 and -24 hours = 0
* month = 0 and 0 = day 30 and 0 = hours 24
Hmmm ... I think it would be better if the two functions were
independent, if possible. Your spec above implies that justify_hours
implicitly does justify_days as well, which seems a bit restrictive.
Furthermore, justify_hours should only assume that 1 day == 24 hours,
which while broken by DST is still a lot solider assumption than
justify_days' 1 month == 30 days. I can well believe that a lot of
people only want to make the first assumption.
So I'm inclined to think that justify_hours is responsible for reducing
the seconds part to less-than-24-hours and pushing any overflow into the
days part (but not touching months), while justify_days is responsible
for reducing the days part to less-than-30-days and pushing any overflow
into the months part (but not touching seconds). If you want both you
apply both functions, probably in that order. (I wonder if there are
any cases where applying justify_days before justify_hours would be
useful. Offhand I can't see one ...)
regards, tom lane
I did not mean to imply that the two functions would be calling each other.
Rather, I thought that a user should get sensible results if they called them
both together. The current code (without the patch) behaves as follows:
select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
-
4 mons 4 days -12:00:00
which seems inconsistent with the intent of the patch. Shouldn't the patched
version return '4 mons 3 days 12:00:00' instead?
mark
---(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
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes:
The current code (without the patch) behaves as follows:
select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
-
4 mons 4 days -12:00:00
So? If we liked the current behavior we wouldn't be discussing a patch...
My thought is that justify_hours should reduce that input to
'1 month 93 days 12:00:00' and then justify_days would produce
'4 months 3 days 12:00:00'.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote:
Mark Dilger [EMAIL PROTECTED] writes:
The current code (without the patch) behaves as follows:
select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
-
4 mons 4 days -12:00:00
So? If we liked the current behavior we wouldn't be discussing a patch...
My thought is that justify_hours should reduce that input to
'1 month 93 days 12:00:00' and then justify_days would produce
'4 months 3 days 12:00:00'.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Your proposal is that justify_hours borrows 24 hours from the days column in
order to bring the -12 hours up to a positive 12 hours. Should it only do that
if the days column is a positive number? What if it is negative?
I think we all agree on the following but nobody is explicitly saying so:
select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
-
1 day 12:00:00
select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
-
-2 days -12:00:00
Am I correct that the second case should still have negative hours? If so, then
justify_hours(...) needs to examine the sign of the days and months portion of
the interval while performing its work.
mark
---(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
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger wrote:
Your proposal is that justify_hours borrows 24 hours from the days column in
order to bring the -12 hours up to a positive 12 hours. Should it only do
that
if the days column is a positive number? What if it is negative?
I think we all agree on the following but nobody is explicitly saying so:
select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
-
1 day 12:00:00
Right.
select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
-
-2 days -12:00:00
Right, unchanged.
Am I correct that the second case should still have negative hours? If so,
then
justify_hours(...) needs to examine the sign of the days and months portion
of
the interval while performing its work.
Yes, it would need to look at both, and this opens a new problem.
Imagine this:
'1 mons -2 days -12:00:00'
Which sign do we head to for this? For justify_hours, if we don't look
at the months it remains unchange, but calling justify_days we get:
'28 days -12:00:00'
which is wrong (negative and positive). Now if we knew justify_days was
going to be called we would have had justify_hours return '-3 days
12:00:00' so the final result after calling justify_days would be '27
days 12:00:00'.
My head hurts.
--
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: [HACKERS] [SQL] Interval subtracting
Bruce Momjian wrote:
Mark Dilger wrote:
Your proposal is that justify_hours borrows 24 hours from the days column in
order to bring the -12 hours up to a positive 12 hours. Should it only do that
if the days column is a positive number? What if it is negative?
I think we all agree on the following but nobody is explicitly saying so:
select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
-
1 day 12:00:00
Right.
select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
-
-2 days -12:00:00
Right, unchanged.
Am I correct that the second case should still have negative hours? If so, then
justify_hours(...) needs to examine the sign of the days and months portion of
the interval while performing its work.
Yes, it would need to look at both, and this opens a new problem.
Imagine this:
'1 mons -2 days -12:00:00'
Which sign do we head to for this? For justify_hours, if we don't look
at the months it remains unchange, but calling justify_days we get:
'28 days -12:00:00'
which is wrong (negative and positive). Now if we knew justify_days was
going to be called we would have had justify_hours return '-3 days
12:00:00' so the final result after calling justify_days would be '27
days 12:00:00'.
My head hurts.
I am just now testing a patch which handles all of this. justify_hours *makes
no change to months or days*, but it examines them both to determine if the
total amount of time represented there is positive or negative. It then makes
sure that the hours have the same sign.
Of course, if you never get around to calling justify_days, you'll have mixed
signs in your results. But if days and months have different signs to begin
with, then that isn't the fault of justify_hours, so we really haven't done any
harm.
I'll be posting the patch shortly.
mark
---(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
Re: [HACKERS] [SQL] Interval subtracting
A new patch is attached. Please note the regression differences.
mark
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context=5 -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c 22 Nov 2005 22:30:33 - 1.160
--- src/backend/utils/adt/timestamp.c 1 Mar 2006 22:29:32 -
***
*** 2003,2013
TMODULO(result-time, wholeday, USECS_PER_DAY);
#else
TMODULO(result-time, wholeday, (double) SECS_PER_DAY);
#endif
result-day += wholeday;/* could overflow... */
!
PG_RETURN_INTERVAL_P(result);
}
/*
*interval_justify_days()
--- 2003,2024
TMODULO(result-time, wholeday, USECS_PER_DAY);
#else
TMODULO(result-time, wholeday, (double) SECS_PER_DAY);
#endif
result-day += wholeday;/* could overflow... */
! if ((result-time 0)
!((result-month = 0 result-day = 0) ||
! (result-month 0 result-day 0 (-1.0 *
(double)result-day)/((double)result-month) ((double)DAYS_PER_MONTH)) ||
! (result-month 0 result-day 0
((double)result-day)/(-1.0 * (double)result-month)
((double)DAYS_PER_MONTH
! {
! #ifdef HAVE_INT64_TIMESTAMP
! result-time += USECS_PER_DAY;
! #else
! result-time += (double) SECS_PER_DAY;
! #endif
! result-day--;
! }
PG_RETURN_INTERVAL_P(result);
}
/*
*interval_justify_days()
***
*** 2028,2037
--- 2039,2053
result-time = span-time;
wholemonth = result-day / DAYS_PER_MONTH;
result-day -= wholemonth * DAYS_PER_MONTH;
result-month += wholemonth;
+ if (result-day 0 result-month 0)
+ {
+ result-day += DAYS_PER_MONTH;
+ result-month--;
+ }
PG_RETURN_INTERVAL_P(result);
}
/* timestamp_pl_interval()
*** ./expected/timestamp.outSat Jun 25 20:04:18 2005
--- ./results/timestamp.out Wed Mar 1 14:26:33 2006
***
*** 488,494
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
--- 488,494
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
***
*** 557,563
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
--- 557,563
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
==
*** ./expected/timestamptz.out Sat Jun 25 20:04:18 2005
--- ./results/timestamptz.out Wed Mar 1 14:26:34 2006
***
*** 483,489
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
--- 483,489
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
***
*** 551,557
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
--- 551,557
| @ 306 days 6 hours 27 mins 59 secs ago
| @ 2 days 6 hours 27 mins 59 secs ago
| @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
| @ 57 days 17 hours 32 mins 1 sec
| @ 58 days 17 hours 32 mins 1 sec
| @ 362 days 17 hours 32 mins 1 sec
Re: [HACKERS] [SQL] Interval subtracting
Bruce Momjian pgman@candle.pha.pa.us writes: Imagine this: '1 mons -2 days -12:00:00' Which sign do we head to for this? For justify_hours, if we don't look at the months it remains unchange, but calling justify_days we get: '28 days -12:00:00' which is wrong (negative and positive). Ugh, that's not good. Based on that, I guess I have to change my vote: justify_hours should still not look at the month (because it shouldn't use the month=30days assumption), but justify_days should be changed to be effectively a combination of both functions --- that is, it should fix all three fields using both the 30days and the 24hours assumptions. Then it could guarantee that all come out with the same sign. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Mark Dilger [EMAIL PROTECTED] writes: Am I correct that the second case should still have negative hours? Yes... If so, then justify_hours(...) needs to examine the sign of the days and months portion of the interval while performing its work. No, it should ignore the months part completely, IMHO. You are just confusing matters by using both functions in your examples, as then it's not clear which does what. regards, tom lane I like the idea that a person has some justify-path by which they can get all the signs to match. With the patch that I just posted, this is accomplished as follows: justify_days(justify_hours(...)) Regardless of the particular weirdness of the signs in the original interval. But the patch also leaves open the possibility that you don't want the hours touched, perhaps because you're dealing with a daylight savings time period and can't accept the concept of a 24-hour day. In that case: justify_days(...) will get the sign on the months and days to match each other, though perhaps not match the hours. In the event that you want to justify the hours, but can't accept having the days justified (because you have a non-30 day month), then you can call: justify_hours(...) and get the sign on the hours portion to match the overall intent of the interval (positive or negative) without being forced to actually change the way the days and months are being represented. This overall design seems more flexible than Tom's recent post in which he stated that justify_days should call justify_hours internally. I tend not to agree. However, it wouldn't hurt to have a justify_interval(...) function which does justify both in one shot. mark ---(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: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Imagine this: '1 mons -2 days -12:00:00' Which sign do we head to for this? For justify_hours, if we don't look at the months it remains unchange, but calling justify_days we get: '28 days -12:00:00' which is wrong (negative and positive). Ugh, that's not good. Based on that, I guess I have to change my vote: justify_hours should still not look at the month (because it shouldn't use the month=30days assumption), but justify_days should be changed to be effectively a combination of both functions --- that is, it should fix all three fields using both the 30days and the 24hours assumptions. Then it could guarantee that all come out with the same sign. If we do that, we should just call it justify_interval(). I am thinking this is the direction to go, and for people who want more control they use the justify_hours and justify_days, and those are left unchanged. Should justify_days() look at hours only if the day and hours signs differ? And perhaps only if the hours is between -24 and 0. -- 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Bruce Momjian wrote: If we do that, we should just call it justify_interval(). I am thinking this is the direction to go, and for people who want more control they use the justify_hours and justify_days, and those are left unchanged. I agree. Let's leave the existing functions alone. I can roll-up the changes made so far into a new function as Bruce suggests. mark ---(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: [HACKERS] [SQL] Interval subtracting
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Based on that, I guess I have to change my vote: justify_hours should still not look at the month (because it shouldn't use the month=30days assumption), but justify_days should be changed to be effectively a combination of both functions --- that is, it should fix all three fields using both the 30days and the 24hours assumptions. Then it could guarantee that all come out with the same sign. If we do that, we should just call it justify_interval(). I am thinking this is the direction to go, and for people who want more control they use the justify_hours and justify_days, and those are left unchanged. Well, the question is whether justify_days has a sane definition that is different from this. Based on your example, I'm not seeing one. regards, tom lane ---(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: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Well, the question is whether justify_days has a sane definition that is different from this. Based on your example, I'm not seeing one. Backwards compatibility is probably more important than sanity. Let's just deprecate the existing functions and recommend that people use justify_interval(...). By not changing the existing functions we can avoid a certain amount of hell. mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Based on that, I guess I have to change my vote: justify_hours should still not look at the month (because it shouldn't use the month=30days assumption), but justify_days should be changed to be effectively a combination of both functions --- that is, it should fix all three fields using both the 30days and the 24hours assumptions. Then it could guarantee that all come out with the same sign. If we do that, we should just call it justify_interval(). I am thinking this is the direction to go, and for people who want more control they use the justify_hours and justify_days, and those are left unchanged. Well, the question is whether justify_days has a sane definition that is different from this. Based on your example, I'm not seeing one. Uh, justify days only deals with days -- months conversions. There is no processing for hours. I don't understand your comment. -- 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Bruce Momjian pgman@candle.pha.pa.us writes: Well, the question is whether justify_days has a sane definition that is different from this. Based on your example, I'm not seeing one. Uh, justify days only deals with days -- months conversions. There is no processing for hours. I don't understand your comment. So it won't guarantee that hours has a consistent sign. If you're OK with that, then that's fine, let's make justify_days work that way and then provide a justify_interval that processes all three fields. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: Bruce Momjian wrote: If we do that, we should just call it justify_interval(). I am thinking this is the direction to go, and for people who want more control they use the justify_hours and justify_days, and those are left unchanged. I agree. Let's leave the existing functions alone. No, we still need to fix them to not leave a large negative value in place for seconds or days (respectively). The current coding is unquestionably inadequate. regards, tom lane ---(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
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: This overall design seems more flexible than Tom's recent post in which he stated that justify_days should call justify_hours internally. AFAIR I said the exact opposite. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger wrote: Tom Lane wrote: Well, the question is whether justify_days has a sane definition that is different from this. Based on your example, I'm not seeing one. Backwards compatibility is probably more important than sanity. Let's just deprecate the existing functions and recommend that people use justify_interval(...). By not changing the existing functions we can avoid a certain amount of hell. Those functions are new in 8.1 so I do think we can improve them in 8.2 if we agree. Tom's idea of: * month 0 and 0 = day 30 * month 0 and -30 day = 0 * month = 0 and -30 day 30 seems a good change for 8.2, and the same for justify_hours(). The question is whether justify_days should also adjust hours I think is the issue, and the reason for a justify_interval() function. Even if we had people do: justify_hours(justify_days(justify_hours())) I don't think that would do what we want in all cases. Consider '1 mon -1 hour'. That should be '29 days 23 hours' but neither existing function, even if modified, will allow us to return that. Only something like justify_interval() could do it. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Mark Dilger [EMAIL PROTECTED] writes: This overall design seems more flexible than Tom's recent post in which he stated that justify_days should call justify_hours internally. AFAIR I said the exact opposite. regards, tom lane Tom Lane also wrote: assumption), but justify_days should be changed to be effectively a combination of both functions --- that is, it should fix all three fields using both the 30days and the 24hours assumptions. Then it could guarantee that all come out with the same sign. How is changing justify days so that it touches the hours field different from having justify_days call justify_hours? mark ---(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
Re: [HACKERS] [SQL] Interval subtracting
Bruce Momjian wrote: Even if we had people do: justify_hours(justify_days(justify_hours())) I don't think that would do what we want in all cases. Consider '1 mon -1 hour'. That should be '29 days 23 hours' but neither existing function, even if modified, will allow us to return that. Only something like justify_interval() could do it. justify_days(justify_hours(...)) fixes *everything* in the most recently submitted patch, regardless of the convoluted case you invent. There is no data for which it won't work. There is no need for justify_interval(...), except as syntactic sugar. Since the backward compatibility argument didn't convince you, then we should go with the existing patch as-is. Whether we introduce the new function justify_interval(...) could be treated as a separate question, though I don't mind putting that in the patch and resubmitting. mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: justify_days(justify_hours(...)) fixes *everything* in the most recently submitted patch, regardless of the convoluted case you invent. There is no data for which it won't work. If so, one function or the other is cheating. Per discussion, justify_hours must never touch months, and I don't believe that justify_days should touch seconds either. The proposed justify_interval function should have a result different from successive application of the two existing functions, because it will ensure that all three fields have similar signs whereas separate use of the two functions can't promise that in corner cases. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Mark Dilger [EMAIL PROTECTED] writes: justify_days(justify_hours(...)) fixes *everything* in the most recently submitted patch, regardless of the convoluted case you invent. There is no data for which it won't work. If so, one function or the other is cheating. Per discussion, justify_hours must never touch months, and I don't believe that justify_days should touch seconds either. The proposed justify_interval function should have a result different from successive application of the two existing functions, because it will ensure that all three fields have similar signs whereas separate use of the two functions can't promise that in corner cases. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster That depends what you mean by cheating. The justify_hours function looks to see what answer justify_days would give, but does not actually change the data. I described this all earlier and I still don't see why there is anything wrong with it. mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: If so, one function or the other is cheating. That depends what you mean by cheating. The justify_hours function looks to see what answer justify_days would give, but does not actually change the data. I described this all earlier and I still don't see why there is anything wrong with it. The problem is that you can't determine what answer justify_days would give without using the assumption 1 month == 30 days, which is an assumption that justify_hours must not depend on. regards, tom lane ---(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
Re: [HACKERS] [SQL] Interval subtracting
Tom Lane wrote: Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: If so, one function or the other is cheating. That depends what you mean by cheating. The justify_hours function looks to see what answer justify_days would give, but does not actually change the data. I described this all earlier and I still don't see why there is anything wrong with it. The problem is that you can't determine what answer justify_days would give without using the assumption 1 month == 30 days, which is an assumption that justify_hours must not depend on. Ahhh. So the fact that justify_days already makes the 1 month == 30 days assumption is ok in that function but can't be propagated to justify_hours. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: 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 do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. --- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing schrieb: Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: ... But we do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. but luckily we dont write it ;) Some people say (like ) this: quarter past 3, half past 3, three quartes past 3, 4. Which seems more logical. :-) But saying would be a job for to_char, not for internal storage, which should _always_ be canonical. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [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 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
Re: [HACKERS] [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 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
