pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInterval subtracting
62+ messages / 13 participants
[nested] [flat]
* Interval subtracting
@ 2006-02-18 13:44 Milorad Poluga <milorad.poluga@cores.co.yu>
0 siblings, 1 reply; 62+ messages in thread
From: Milorad Poluga @ 2006-02-18 13:44 UTC (permalink / raw)
To: pgsql-sql
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
-------------------------------
milorad.poluga@cores.co.yu
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: Interval subtracting
@ 2006-02-18 14:24 Milen A. Radev <milen@radev.net>
parent: Milorad Poluga <milorad.poluga@cores.co.yu>
0 siblings, 1 reply; 62+ messages in thread
From: Milen A. Radev @ 2006-02-18 14:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: Interval subtracting
@ 2006-02-18 14:36 Milorad Poluga <milorad.poluga@cores.co.yu>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 62+ messages in thread
From: Milorad Poluga @ 2006-02-18 14:36 UTC (permalink / raw)
To: pgsql-sql; +Cc: Milen A. Radev <milen@radev.net>
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
---------------------------------------
milorad.poluga@cores.co.yu
---------------------------------------
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: Interval subtracting
@ 2006-02-18 14:45 Milen A. Radev <milen@radev.net>
parent: Milorad Poluga <milorad.poluga@cores.co.yu>
0 siblings, 1 reply; 62+ messages in thread
From: Milen A. Radev @ 2006-02-18 14:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-02-18 18:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 3 replies; 62+ messages in thread
From: Tom Lane @ 2006-02-18 18:30 UTC (permalink / raw)
To: Milen A. Radev <milen@radev.net>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
"Milen A. Radev" <milen@radev.net> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-02-18 19:03 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 62+ messages in thread
From: Stephan Szabo @ 2006-02-18 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
On Sat, 18 Feb 2006, Tom Lane wrote:
> "Milen A. Radev" <milen@radev.net> 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'?
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: Interval subtracting
@ 2006-02-20 08:39 Milorad Poluga <milorad.poluga@cores.co.yu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 62+ messages in thread
From: Milorad Poluga @ 2006-02-20 08:39 UTC (permalink / raw)
To: pgsql-sql
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
milorad.poluga@cores.co.yu
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 17:05 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 17:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> "Milen A. Radev" <milen@radev.net> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 17:59 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 1 reply; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 17:59 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
Mark Dilger wrote:
> Tom Lane wrote:
> > "Milen A. Radev" <milen@radev.net> 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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 18:06 Mark Dilger <pgsql@markdilger.com>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 2 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 18:06 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Mark Dilger wrote:
> Tom Lane wrote:
>
>> "Milen A. Radev" <milen@radev.net> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 18:24 Jim C. Nasby <jnasby@pervasive.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 62+ messages in thread
From: Jim C. Nasby @ 2006-03-01 18:24 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 18:39 Mark Dilger <pgsql@markdilger.com>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 0 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 18:39 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Mark Dilger wrote:
> Mark Dilger wrote:
>
>> Tom Lane wrote:
>>
>>> "Milen A. Radev" <milen@radev.net> 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
The patch is attached. Since the functionality is being intentionally changed,
not surprisingly the regression tests for timestamp, timestamptz and horology
failed. The regression.diffs are also attached.
I intended to update the docs for justify_days and justify_hours, but the docs
don't detail the behavior at a sufficient level for any change to be warranted.
mark
Attachments:
patch (text/plain, 1.5K) download
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 -0000 1.160
--- src/backend/utils/adt/timestamp.c 1 Mar 2006 18:35:36 -0000
***************
*** 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,2021 ----
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)
! {
! #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 ****
--- 2036,2050 ----
result->time = span->time;
wholemonth = result->day / DAYS_PER_MONTH;
result->day -= wholemonth * DAYS_PER_MONTH;
result->month += wholemonth;
+ if (result->day < 0)
+ {
+ result->day += DAYS_PER_MONTH;
+ result->month--;
+ }
PG_RETURN_INTERVAL_P(result);
}
/* timestamp_pl_interval()
regression.diffs (text/plain, 46.6K) download
*** ./expected/timestamp.out Sat Jun 25 20:04:18 2005
--- ./results/timestamp.out Wed Mar 1 10:21:00 2006
***************
*** 442,448 ****
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
54 | diff
! ----+----------------------------------------
| @ 9863 days ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
--- 442,448 ----
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
54 | diff
! ----+-------------------------------------------
| @ 9863 days ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
***************
*** 483,494 ****
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 308 days 6 hours 27 mins 59 secs ago
! | @ 307 days 6 hours 27 mins 59 secs ago
! | @ 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,494 ----
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 309 days -17 hours -32 mins -1 secs ago
! | @ 308 days -17 hours -32 mins -1 secs ago
! | @ 307 days -17 hours -32 mins -1 secs ago
! | @ 3 days -17 hours -32 mins -1 secs ago
! | @ 2 days -17 hours -32 mins -1 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
***************
*** 511,517 ****
WHERE d1 BETWEEN timestamp without time zone '1902-01-01'
AND timestamp without time zone '2038-01-01';
54 | diff
! ----+----------------------------------------
| @ 9863 days ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
--- 511,517 ----
WHERE d1 BETWEEN timestamp without time zone '1902-01-01'
AND timestamp without time zone '2038-01-01';
54 | diff
! ----+-------------------------------------------
| @ 9863 days ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
***************
*** 552,563 ****
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 308 days 6 hours 27 mins 59 secs ago
! | @ 307 days 6 hours 27 mins 59 secs ago
! | @ 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
--- 552,563 ----
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 309 days -17 hours -32 mins -1 secs ago
! | @ 308 days -17 hours -32 mins -1 secs ago
! | @ 307 days -17 hours -32 mins -1 secs ago
! | @ 3 days -17 hours -32 mins -1 secs ago
! | @ 2 days -17 hours -32 mins -1 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 10:21:00 2006
***************
*** 437,444 ****
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
54 | diff
! ----+----------------------------------------
! | @ 9863 days 8 hours ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 2 secs
--- 437,444 ----
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
54 | diff
! ----+-------------------------------------------
! | @ 9864 days -16 hours ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 2 secs
***************
*** 478,489 ****
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 308 days 6 hours 27 mins 59 secs ago
! | @ 307 days 6 hours 27 mins 59 secs ago
! | @ 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
--- 478,489 ----
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 309 days -17 hours -32 mins -1 secs ago
! | @ 308 days -17 hours -32 mins -1 secs ago
! | @ 307 days -17 hours -32 mins -1 secs ago
! | @ 3 days -17 hours -32 mins -1 secs ago
! | @ 2 days -17 hours -32 mins -1 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
***************
*** 505,512 ****
FROM TIMESTAMPTZ_TBL
WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
54 | diff
! ----+----------------------------------------
! | @ 9863 days 8 hours ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 2 secs
--- 505,512 ----
FROM TIMESTAMPTZ_TBL
WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
54 | diff
! ----+-------------------------------------------
! | @ 9864 days -16 hours ago
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 2 secs
***************
*** 546,557 ****
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 308 days 6 hours 27 mins 59 secs ago
! | @ 307 days 6 hours 27 mins 59 secs ago
! | @ 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
--- 546,557 ----
| @ 44 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
| @ 45 days 17 hours 32 mins 1 sec
! | @ 309 days -17 hours -32 mins -1 secs ago
! | @ 308 days -17 hours -32 mins -1 secs ago
! | @ 307 days -17 hours -32 mins -1 secs ago
! | @ 3 days -17 hours -32 mins -1 secs ago
! | @ 2 days -17 hours -32 mins -1 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/horology.out Thu Sep 8 09:49:04 2005
--- ./results/horology.out Wed Mar 1 10:21:02 2006
***************
*** 1955,1961 ****
ORDER BY difference;
16 | timestamp | gpstime_zero | difference
----+------------------------------+------------------------------+-------------------------------------
! | Thu Jan 01 00:00:00 1970 PST | Sat Jan 05 16:00:00 1980 PST | @ 3656 days 16 hours ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
--- 1955,1961 ----
ORDER BY difference;
16 | timestamp | gpstime_zero | difference
----+------------------------------+------------------------------+-------------------------------------
! | Thu Jan 01 00:00:00 1970 PST | Sat Jan 05 16:00:00 1980 PST | @ 3657 days -8 hours ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
***************
*** 1977,1999 ****
FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
ORDER BY timestamp1, timestamp2, difference;
226 | timestamp1 | timestamp2 | difference
! -----+------------------------------+------------------------------+-------------------------------------------
| Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 PST | @ 0
! | Thu Jan 01 00:00:00 1970 PST | Wed Feb 28 17:32:01 1996 PST | @ 9554 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Thu Feb 29 17:32:01 1996 PST | @ 9555 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Fri Mar 01 17:32:01 1996 PST | @ 9556 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Mon Dec 30 17:32:01 1996 PST | @ 9860 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Tue Dec 31 17:32:01 1996 PST | @ 9861 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Fri Dec 31 17:32:01 1999 PST | @ 10956 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Sat Jan 01 17:32:01 2000 PST | @ 10957 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 02:14:05 2000 PST | @ 11031 days 2 hours 14 mins 5 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 03:14:04 2000 PST | @ 11031 days 3 hours 14 mins 4 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 08:14:01 2000 PST | @ 11031 days 8 hours 14 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 12:14:03 2000 PST | @ 11031 days 12 hours 14 mins 3 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 13:14:02 2000 PST | @ 11031 days 13 hours 14 mins 2 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Sun Dec 31 17:32:01 2000 PST | @ 11322 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Mon Jan 01 17:32:01 2001 PST | @ 11323 days 17 hours 32 mins 1 sec ago
! | Thu Jan 01 00:00:00 1970 PST | Sat Sep 22 18:19:20 2001 PDT | @ 11587 days 17 hours 19 mins 20 secs ago
| Wed Feb 28 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9554 days 17 hours 32 mins 1 sec
| Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
| Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
--- 1977,1999 ----
FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
ORDER BY timestamp1, timestamp2, difference;
226 | timestamp1 | timestamp2 | difference
! -----+------------------------------+------------------------------+----------------------------------------------
| Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 PST | @ 0
! | Thu Jan 01 00:00:00 1970 PST | Wed Feb 28 17:32:01 1996 PST | @ 9555 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Thu Feb 29 17:32:01 1996 PST | @ 9556 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Fri Mar 01 17:32:01 1996 PST | @ 9557 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Mon Dec 30 17:32:01 1996 PST | @ 9861 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Tue Dec 31 17:32:01 1996 PST | @ 9862 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Fri Dec 31 17:32:01 1999 PST | @ 10957 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Sat Jan 01 17:32:01 2000 PST | @ 10958 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 02:14:05 2000 PST | @ 11032 days -21 hours -45 mins -55 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 03:14:04 2000 PST | @ 11032 days -20 hours -45 mins -56 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 08:14:01 2000 PST | @ 11032 days -15 hours -45 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 12:14:03 2000 PST | @ 11032 days -11 hours -45 mins -57 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 13:14:02 2000 PST | @ 11032 days -10 hours -45 mins -58 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Sun Dec 31 17:32:01 2000 PST | @ 11323 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Mon Jan 01 17:32:01 2001 PST | @ 11324 days -6 hours -27 mins -59 secs ago
! | Thu Jan 01 00:00:00 1970 PST | Sat Sep 22 18:19:20 2001 PDT | @ 11588 days -6 hours -40 mins -40 secs ago
| Wed Feb 28 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9554 days 17 hours 32 mins 1 sec
| Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
| Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
***************
*** 2002,2015 ****
| Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
| Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1476 days 8 hours 42 mins 4 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1476 days 9 hours 42 mins 3 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1476 days 14 hours 42 mins ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1476 days 18 hours 42 mins 2 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1476 days 19 hours 42 mins 1 sec ago
| Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
| Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
! | Wed Feb 28 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2032 days 23 hours 47 mins 19 secs ago
| Thu Feb 29 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9555 days 17 hours 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
| Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
--- 2002,2015 ----
| Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
| Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1477 days -15 hours -17 mins -56 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1477 days -14 hours -17 mins -57 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1477 days -9 hours -18 mins ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1477 days -5 hours -17 mins -58 secs ago
! | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1477 days -4 hours -17 mins -59 secs ago
| Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
| Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
! | Wed Feb 28 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2033 days -12 mins -41 secs ago
| Thu Feb 29 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9555 days 17 hours 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
| Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
***************
*** 2018,2031 ****
| Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
| Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1475 days 8 hours 42 mins 4 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1475 days 9 hours 42 mins 3 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1475 days 14 hours 42 mins ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1475 days 18 hours 42 mins 2 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1475 days 19 hours 42 mins 1 sec ago
| Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
| Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
! | Thu Feb 29 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2031 days 23 hours 47 mins 19 secs ago
| Fri Mar 01 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9556 days 17 hours 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
| Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
--- 2018,2031 ----
| Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
| Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1476 days -15 hours -17 mins -56 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1476 days -14 hours -17 mins -57 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1476 days -9 hours -18 mins ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1476 days -5 hours -17 mins -58 secs ago
! | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1476 days -4 hours -17 mins -59 secs ago
| Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
| Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
! | Thu Feb 29 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2032 days -12 mins -41 secs ago
| Fri Mar 01 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9556 days 17 hours 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
| Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
***************
*** 2034,2047 ****
| Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
| Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1474 days 8 hours 42 mins 4 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1474 days 9 hours 42 mins 3 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1474 days 14 hours 42 mins ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1474 days 18 hours 42 mins 2 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1474 days 19 hours 42 mins 1 sec ago
| Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
| Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
! | Fri Mar 01 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2030 days 23 hours 47 mins 19 secs ago
| Mon Dec 30 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9860 days 17 hours 32 mins 1 sec
| Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
| Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
--- 2034,2047 ----
| Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
| Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1475 days -15 hours -17 mins -56 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1475 days -14 hours -17 mins -57 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1475 days -9 hours -18 mins ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1475 days -5 hours -17 mins -58 secs ago
! | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1475 days -4 hours -17 mins -59 secs ago
| Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
| Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
! | Fri Mar 01 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2031 days -12 mins -41 secs ago
| Mon Dec 30 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9860 days 17 hours 32 mins 1 sec
| Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
| Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
***************
*** 2050,2063 ****
| Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
| Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
| Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1170 days 8 hours 42 mins 4 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1170 days 9 hours 42 mins 3 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1170 days 14 hours 42 mins ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1170 days 18 hours 42 mins 2 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1170 days 19 hours 42 mins 1 sec ago
| Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
| Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
! | Mon Dec 30 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1726 days 23 hours 47 mins 19 secs ago
| Tue Dec 31 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9861 days 17 hours 32 mins 1 sec
| Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
| Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
--- 2050,2063 ----
| Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
| Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
| Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1171 days -15 hours -17 mins -56 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1171 days -14 hours -17 mins -57 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1171 days -9 hours -18 mins ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1171 days -5 hours -17 mins -58 secs ago
! | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1171 days -4 hours -17 mins -59 secs ago
| Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
| Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
! | Mon Dec 30 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1727 days -12 mins -41 secs ago
| Tue Dec 31 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9861 days 17 hours 32 mins 1 sec
| Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
| Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
***************
*** 2066,2079 ****
| Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
| Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
| Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1169 days 8 hours 42 mins 4 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1169 days 9 hours 42 mins 3 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1169 days 14 hours 42 mins ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1169 days 18 hours 42 mins 2 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1169 days 19 hours 42 mins 1 sec ago
| Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
| Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
! | Tue Dec 31 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1725 days 23 hours 47 mins 19 secs ago
| Fri Dec 31 17:32:01 1999 PST | Thu Jan 01 00:00:00 1970 PST | @ 10956 days 17 hours 32 mins 1 sec
| Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
| Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
--- 2066,2079 ----
| Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
| Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
| Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1170 days -15 hours -17 mins -56 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1170 days -14 hours -17 mins -57 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1170 days -9 hours -18 mins ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1170 days -5 hours -17 mins -58 secs ago
! | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1170 days -4 hours -17 mins -59 secs ago
| Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
| Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
! | Tue Dec 31 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1726 days -12 mins -41 secs ago
| Fri Dec 31 17:32:01 1999 PST | Thu Jan 01 00:00:00 1970 PST | @ 10956 days 17 hours 32 mins 1 sec
| Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
| Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
***************
*** 2082,2095 ****
| Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
| Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
| Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 02:14:05 2000 PST | @ 74 days 8 hours 42 mins 4 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 03:14:04 2000 PST | @ 74 days 9 hours 42 mins 3 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 08:14:01 2000 PST | @ 74 days 14 hours 42 mins ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 12:14:03 2000 PST | @ 74 days 18 hours 42 mins 2 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 13:14:02 2000 PST | @ 74 days 19 hours 42 mins 1 sec ago
| Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
| Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
! | Fri Dec 31 17:32:01 1999 PST | Sat Sep 22 18:19:20 2001 PDT | @ 630 days 23 hours 47 mins 19 secs ago
| Sat Jan 01 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 10957 days 17 hours 32 mins 1 sec
| Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
| Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
--- 2082,2095 ----
| Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
| Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
| Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 02:14:05 2000 PST | @ 75 days -15 hours -17 mins -56 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 03:14:04 2000 PST | @ 75 days -14 hours -17 mins -57 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 08:14:01 2000 PST | @ 75 days -9 hours -18 mins ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 12:14:03 2000 PST | @ 75 days -5 hours -17 mins -58 secs ago
! | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 13:14:02 2000 PST | @ 75 days -4 hours -17 mins -59 secs ago
| Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
| Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
! | Fri Dec 31 17:32:01 1999 PST | Sat Sep 22 18:19:20 2001 PDT | @ 631 days -12 mins -41 secs ago
| Sat Jan 01 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 10957 days 17 hours 32 mins 1 sec
| Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
| Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
***************
*** 2098,2111 ****
| Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
| Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
| Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 73 days 8 hours 42 mins 4 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 73 days 9 hours 42 mins 3 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 73 days 14 hours 42 mins ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 73 days 18 hours 42 mins 2 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 73 days 19 hours 42 mins 1 sec ago
| Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
| Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
! | Sat Jan 01 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 629 days 23 hours 47 mins 19 secs ago
| Wed Mar 15 02:14:05 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 2 hours 14 mins 5 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 8 hours 42 mins 4 secs
--- 2098,2111 ----
| Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
| Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
| Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 74 days -15 hours -17 mins -56 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 74 days -14 hours -17 mins -57 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 74 days -9 hours -18 mins ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 74 days -5 hours -17 mins -58 secs ago
! | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 74 days -4 hours -17 mins -59 secs ago
| Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
| Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
! | Sat Jan 01 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 630 days -12 mins -41 secs ago
| Wed Mar 15 02:14:05 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 2 hours 14 mins 5 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 8 hours 42 mins 4 secs
***************
*** 2115,2127 ****
| Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
| Wed Mar 15 03:14:04 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 3 hours 14 mins 4 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 9 hours 42 mins 3 secs
--- 2115,2127 ----
| Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 1 day -23 hours -1 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -18 hours -4 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -14 hours -2 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -13 hours -3 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 292 days -8 hours -42 mins -4 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 293 days -8 hours -42 mins -4 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 557 days -8 hours -54 mins -45 secs ago
| Wed Mar 15 03:14:04 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 3 hours 14 mins 4 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 9 hours 42 mins 3 secs
***************
*** 2132,2143 ****
| Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
| Wed Mar 15 08:14:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 8 hours 14 mins 1 sec
| Wed Mar 15 08:14:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 14 hours 42 mins
| Wed Mar 15 08:14:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 14 hours 42 mins
--- 2132,2143 ----
| Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -19 hours -3 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -15 hours -1 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -14 hours -2 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 292 days -9 hours -42 mins -3 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 293 days -9 hours -42 mins -3 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 557 days -9 hours -54 mins -44 secs ago
| Wed Mar 15 08:14:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 8 hours 14 mins 1 sec
| Wed Mar 15 08:14:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 14 hours 42 mins
| Wed Mar 15 08:14:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 14 hours 42 mins
***************
*** 2149,2159 ****
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago
! | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
! | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
! | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
| Wed Mar 15 12:14:03 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 12 hours 14 mins 3 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 18 hours 42 mins 2 secs
| Wed Mar 15 12:14:03 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 18 hours 42 mins 2 secs
--- 2149,2159 ----
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -19 hours -59 mins -58 secs ago
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -18 hours -59 mins -59 secs ago
! | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 292 days -14 hours -42 mins ago
! | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 293 days -14 hours -42 mins ago
! | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 557 days -14 hours -54 mins -41 secs ago
| Wed Mar 15 12:14:03 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 12 hours 14 mins 3 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 18 hours 42 mins 2 secs
| Wed Mar 15 12:14:03 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 18 hours 42 mins 2 secs
***************
*** 2166,2175 ****
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago
| Wed Mar 15 13:14:02 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 13 hours 14 mins 2 secs
| Wed Mar 15 13:14:02 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 19 hours 42 mins 1 sec
| Wed Mar 15 13:14:02 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 19 hours 42 mins 1 sec
--- 2166,2175 ----
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -23 hours -1 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 292 days -18 hours -42 mins -2 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 293 days -18 hours -42 mins -2 secs ago
! | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 557 days -18 hours -54 mins -43 secs ago
| Wed Mar 15 13:14:02 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 13 hours 14 mins 2 secs
| Wed Mar 15 13:14:02 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 19 hours 42 mins 1 sec
| Wed Mar 15 13:14:02 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 19 hours 42 mins 1 sec
***************
*** 2183,2191 ****
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 1 sec
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 0
! | Wed Mar 15 13:14:02 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 4 hours 17 mins 59 secs ago
! | Wed Mar 15 13:14:02 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 4 hours 17 mins 59 secs ago
! | Wed Mar 15 13:14:02 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 4 hours 5 mins 18 secs ago
| Sun Dec 31 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11322 days 17 hours 32 mins 1 sec
| Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
| Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
--- 2183,2191 ----
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 1 sec
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 0
! | Wed Mar 15 13:14:02 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 292 days -19 hours -42 mins -1 secs ago
! | Wed Mar 15 13:14:02 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 293 days -19 hours -42 mins -1 secs ago
! | Wed Mar 15 13:14:02 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 557 days -19 hours -54 mins -42 secs ago
| Sun Dec 31 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11322 days 17 hours 32 mins 1 sec
| Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
| Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
***************
*** 2201,2207 ****
| Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 291 days 4 hours 17 mins 59 secs
| Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
| Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
! | Sun Dec 31 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 264 days 23 hours 47 mins 19 secs ago
| Mon Jan 01 17:32:01 2001 PST | Thu Jan 01 00:00:00 1970 PST | @ 11323 days 17 hours 32 mins 1 sec
| Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
| Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
--- 2201,2207 ----
| Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 291 days 4 hours 17 mins 59 secs
| Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
| Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
! | Sun Dec 31 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 265 days -12 mins -41 secs ago
| Mon Jan 01 17:32:01 2001 PST | Thu Jan 01 00:00:00 1970 PST | @ 11323 days 17 hours 32 mins 1 sec
| Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
| Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
***************
*** 2217,2223 ****
| Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 13:14:02 2000 PST | @ 292 days 4 hours 17 mins 59 secs
| Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
| Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
! | Mon Jan 01 17:32:01 2001 PST | Sat Sep 22 18:19:20 2001 PDT | @ 263 days 23 hours 47 mins 19 secs ago
| Sat Sep 22 18:19:20 2001 PDT | Thu Jan 01 00:00:00 1970 PST | @ 11587 days 17 hours 19 mins 20 secs
| Sat Sep 22 18:19:20 2001 PDT | Wed Feb 28 17:32:01 1996 PST | @ 2032 days 23 hours 47 mins 19 secs
| Sat Sep 22 18:19:20 2001 PDT | Thu Feb 29 17:32:01 1996 PST | @ 2031 days 23 hours 47 mins 19 secs
--- 2217,2223 ----
| Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 13:14:02 2000 PST | @ 292 days 4 hours 17 mins 59 secs
| Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
| Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
! | Mon Jan 01 17:32:01 2001 PST | Sat Sep 22 18:19:20 2001 PDT | @ 264 days -12 mins -41 secs ago
| Sat Sep 22 18:19:20 2001 PDT | Thu Jan 01 00:00:00 1970 PST | @ 11587 days 17 hours 19 mins 20 secs
| Sat Sep 22 18:19:20 2001 PDT | Wed Feb 28 17:32:01 1996 PST | @ 2032 days 23 hours 47 mins 19 secs
| Sat Sep 22 18:19:20 2001 PDT | Thu Feb 29 17:32:01 1996 PST | @ 2031 days 23 hours 47 mins 19 secs
======================================================================
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:13 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 1 reply; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 20:13 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: pgsql-hackers@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:18 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 20:18 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:21 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Scott Marlowe @ 2006-03-01 20:21 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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)
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:27 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 20:27 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-01 20:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:28 Hannu Krosing <hannu@skype.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 4 replies; 62+ messages in thread
From: Hannu Krosing @ 2006-03-01 20:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
Ü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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:32 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Hannu Krosing <hannu@skype.net>
3 siblings, 0 replies; 62+ messages in thread
From: Stephan Szabo @ 2006-03-01 20:32 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:33 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Hannu Krosing <hannu@skype.net>
3 siblings, 0 replies; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 20:33 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:33 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 20:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hannu Krosing <hannu@skype.net>
3 siblings, 0 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-01 20:35 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
Hannu Krosing <hannu@skype.net> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:36 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 62+ messages in thread
From: Scott Marlowe @ 2006-03-01 20:36 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:39 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 20:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:46 Mark Dilger <pgsql@markdilger.com>
parent: Hannu Krosing <hannu@skype.net>
3 siblings, 0 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 20:46 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 20:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-01 20:55 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 21:04 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 21:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 21:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-01 21:11 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 21:21 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 21:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 21:45 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 2 replies; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 21:45 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 21:52 Mark Dilger <pgsql@markdilger.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 21:52 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 22:30 Mark Dilger <pgsql@markdilger.com>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 22:30 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>
A new patch is attached. Please note the regression differences.
mark
Attachments:
patch2 (text/plain, 1.8K) download
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 -0000 1.160
--- src/backend/utils/adt/timestamp.c 1 Mar 2006 22:29:32 -0000
***************
*** 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()
regression.diffs2 (text/plain, 9.9K) download
*** ./expected/timestamp.out Sat 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
======================================================================
*** ./expected/horology.out Thu Sep 8 09:49:04 2005
--- ./results/horology.out Wed Mar 1 14:26:36 2006
***************
*** 2115,2124 ****
| Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago
| Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
| Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
| Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
--- 2115,2124 ----
| Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
| Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 1 day -23 hours -1 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -18 hours -4 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -14 hours -2 secs ago
! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -13 hours -3 secs ago
| Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
| Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
| Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
***************
*** 2132,2140 ****
| Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago
| Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
| Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
| Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
--- 2132,2140 ----
| Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
| Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -19 hours -3 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -15 hours -1 secs ago
! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -14 hours -2 secs ago
| Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
| Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
| Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
***************
*** 2149,2156 ****
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago
| Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
| Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
| Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
--- 2149,2156 ----
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
| Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -19 hours -59 mins -58 secs ago
! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -18 hours -59 mins -59 secs ago
| Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
| Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
| Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
***************
*** 2166,2172 ****
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago
| Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
| Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
| Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago
--- 2166,2172 ----
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
| Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -23 hours -1 secs ago
| Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
| Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
| Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago
======================================================================
* Re: [SQL] Interval subtracting
@ 2006-03-01 22:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-01 22:45 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 22:54 Mark Dilger <pgsql@markdilger.com>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 22:54 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:02 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 23:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:07 Mark Dilger <pgsql@markdilger.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 23:07 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-01 23:07 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:10 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 23:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:11 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 23:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-01 23:17 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Dilger <pgsql@markdilger.com>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 0 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-01 23:20 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-01 23:23 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: pgsql-hackers@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:26 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Bruce Momjian @ 2006-03-01 23:26 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:43 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 23:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:49 Mark Dilger <pgsql@markdilger.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-01 23:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-01 23:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-01 23:58 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 00:02 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-02 00:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 00:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-02 00:37 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 01:18 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 62+ messages in thread
From: Mark Dilger @ 2006-03-02 01:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> 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.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 06:45 Hannu Krosing <hannu@skype.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 2 replies; 62+ messages in thread
From: Hannu Krosing @ 2006-03-02 06:45 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-hackers@postgresql.org
Ü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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 07:05 Tino Wildenhain <tino@wildenhain.de>
parent: Hannu Krosing <hannu@skype.net>
1 sibling, 0 replies; 62+ messages in thread
From: Tino Wildenhain @ 2006-03-02 07:05 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
0 siblings, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-02 14:49 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: pgsql-hackers@postgreSQL.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 16:13 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Hannu Krosing <hannu@skype.net>
1 sibling, 1 reply; 62+ messages in thread
From: Scott Marlowe @ 2006-03-02 16:13 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: pgsql-hackers@postgresql.org
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.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 17:50 Hannu Krosing <hannu@skype.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 62+ messages in thread
From: Hannu Krosing @ 2006-03-02 17:50 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-hackers@postgresql.org
Ü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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 18:06 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-02 18:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
I've written the interval_justify() function but the parser does not know about
it yet. I changed these files:
backend/utils/adt/timestamp.c
include/catalog/pg_proc.h
include/utils/timestamp.h
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?
mark
Index: include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.399
diff -r1.399 pg_proc.h
1464a1465,1466
> DATA(insert OID = 2711 ( justify PGNSP PGUID 12 f f t f i 1
1186 "1186" _null_ _null_ _null_ interval_justify - _null_ ));
> DESCR("promote groups of 24 hours to numbers of days and promote groups of 30
days to numbers of months");
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 18:43 Martijn van Oosterhout <kleptog@svana.org>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 0 replies; 62+ messages in thread
From: Martijn van Oosterhout @ 2006-03-02 18:43 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
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.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-02 19:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dilger <pgsql@markdilger.com>
1 sibling, 1 reply; 62+ messages in thread
From: Tom Lane @ 2006-03-02 19:10 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: pgsql-hackers@postgreSQL.org
Mark Dilger <pgsql@markdilger.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-03 20:25 Mark Dilger <pgsql@markdilger.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 62+ messages in thread
From: Mark Dilger @ 2006-03-03 20:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <pgman@candle.pha.pa.us>
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
Attachments:
patch (text/plain, 5.1K) download
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 -0000 1.160
--- src/backend/utils/adt/timestamp.c 3 Mar 2006 20:23:26 -0000
***************
*** 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 += DAYS_PER_MONTH;
+ result->month--;
+ }
+
PG_RETURN_INTERVAL_P(result);
}
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.399
diff --context -r1.399 pg_proc.h
*** src/include/catalog/pg_proc.h 28 Feb 2006 22:37:26 -0000 1.399
--- src/include/catalog/pg_proc.h 3 Mar 2006 20:23:27 -0000
***************
*** 1462,1467 ****
--- 1462,1469 ----
DESCR("convert abstime to timestamp with time zone");
DATA(insert OID = 1174 ( timestamptz PGNSP PGUID 12 f f t f s 1 1184 "1082" _null_ _null_ _null_ date_timestamptz - _null_ ));
DESCR("convert date to timestamp with time zone");
+ DATA(insert OID = 2711 ( justify_interval PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_justify_interval - _null_ ));
+ DESCR("promote groups of 24 hours to numbers of days and promote groups of 30 days to numbers of months");
DATA(insert OID = 1175 ( justify_hours PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_justify_hours - _null_ ));
DESCR("promote groups of 24 hours to numbers of days");
DATA(insert OID = 1295 ( justify_days PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_justify_days - _null_ ));
Index: src/include/utils/timestamp.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/timestamp.h,v
retrieving revision 1.57
diff --context -r1.57 timestamp.h
*** src/include/utils/timestamp.h 15 Oct 2005 02:49:46 -0000 1.57
--- src/include/utils/timestamp.h 3 Mar 2006 20:23:27 -0000
***************
*** 234,239 ****
--- 234,240 ----
extern Datum interval_hash(PG_FUNCTION_ARGS);
extern Datum interval_smaller(PG_FUNCTION_ARGS);
extern Datum interval_larger(PG_FUNCTION_ARGS);
+ extern Datum interval_justify_interval(PG_FUNCTION_ARGS);
extern Datum interval_justify_hours(PG_FUNCTION_ARGS);
extern Datum interval_justify_days(PG_FUNCTION_ARGS);
* Re: [SQL] Interval subtracting
@ 2006-03-08 12:07 Markus Schaber <schabi@logix-tt.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 1 reply; 62+ messages in thread
From: Markus Schaber @ 2006-03-08 12:07 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-08 15:47 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 62+ messages in thread
From: Scott Marlowe @ 2006-03-08 15:47 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
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. :)
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [SQL] Interval subtracting
@ 2006-03-08 15:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 62+ messages in thread
From: Tom Lane @ 2006-03-08 15:54 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; Bruce Momjian <pgman@candle.pha.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Milen A. Radev <milen@radev.net>; pgsql-sql; pgsql-hackers@postgresql.org
Scott Marlowe <smarlowe@g2switchworks.com> 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
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: [HACKERS] Interval subtracting
@ 2006-03-08 21:23 PFC <lists@peufeu.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 62+ messages in thread
From: PFC @ 2006-03-08 21:23 UTC (permalink / raw)
To: pgsql-sql
> 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 ?
^ permalink raw reply [nested|flat] 62+ messages in thread
end of thread, other threads:[~2006-03-08 21:23 UTC | newest]
Thread overview: 62+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-18 13:44 Interval subtracting Milorad Poluga <milorad.poluga@cores.co.yu>
2006-02-18 14:24 ` Milen A. Radev <milen@radev.net>
2006-02-18 14:36 ` Milorad Poluga <milorad.poluga@cores.co.yu>
2006-02-18 14:45 ` Milen A. Radev <milen@radev.net>
2006-02-18 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-18 19:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-01 20:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 20:21 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-01 20:27 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 20:36 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-02 06:45 ` Hannu Krosing <hannu@skype.net>
2006-03-02 07:05 ` Tino Wildenhain <tino@wildenhain.de>
2006-03-02 16:13 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-02 17:50 ` Hannu Krosing <hannu@skype.net>
2006-03-08 12:07 ` Markus Schaber <schabi@logix-tt.com>
2006-03-08 15:47 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-08 15:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-08 21:23 ` PFC <lists@peufeu.com>
2006-03-01 20:28 ` Hannu Krosing <hannu@skype.net>
2006-03-01 20:32 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-01 20:33 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 20:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 20:46 ` Mark Dilger <pgsql@markdilger.com>
2006-02-20 08:39 ` Milorad Poluga <milorad.poluga@cores.co.yu>
2006-03-01 17:05 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 17:59 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 18:24 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-01 18:06 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 18:39 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 20:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 20:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 20:33 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 20:39 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 20:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 21:04 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 21:21 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 21:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 21:52 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 22:30 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 22:54 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 23:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 23:43 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 22:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 23:02 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 23:07 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 23:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 23:10 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 23:26 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 23:49 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 23:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-02 00:02 ` Mark Dilger <pgsql@markdilger.com>
2006-03-02 00:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-02 01:18 ` Mark Dilger <pgsql@markdilger.com>
2006-03-02 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-02 18:06 ` Mark Dilger <pgsql@markdilger.com>
2006-03-02 18:43 ` Martijn van Oosterhout <kleptog@svana.org>
2006-03-02 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-03 20:25 ` Mark Dilger <pgsql@markdilger.com>
2006-03-01 23:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 23:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox