Re: [SQL] Interval subtracting

Поиск
Список
Период
Сортировка
От Mark Dilger
Тема Re: [SQL] Interval subtracting
Дата
Msg-id 44062073.9010803@markdilger.com
обсуждение исходный текст
Ответ на Re: [SQL] Interval subtracting  (Mark Dilger <pgsql@markdilger.com>)
Ответы Re: [SQL] Interval subtracting  (Mark Dilger <pgsql@markdilger.com>)
Список pgsql-hackers
Дерево обсуждения 
A new patch is attached.  Please note the regression differences.

mark



Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context=5 -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c    22 Nov 2005 22:30:33 -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()
*** ./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

======================================================================

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Status of INS/UPD/DEL RETURNING?
Следующее
От: Mark Kirkwood
Дата:
Сообщение: Re: pg_config, pg_service.conf, postgresql.conf ....