Hello

This patch is implementation of some date functions which produce better 
compatibility with Oracle (On request Radim Kolar). Major changes are in 
/src/backend/utils/adt/oracle_compat.c. There are new functions: next_day, 
last_day, add_months and months_between. 

Original Oracle doc
http://www.techonthenet.com/oracle/functions/add_months.htm
http://www.techonthenet.com/oracle/functions/last_day.htm
http://www.techonthenet.com/oracle/functions/next_day.htm
http://www.techonthenet.com/oracle/functions/months_between.htm

Best regards
Pavel Stehule

diff -c -r --new-file pgsql.old/doc/src/sgml/func.sgml 
pgsql.new/doc/src/sgml/func.sgml
*** pgsql.old/doc/src/sgml/func.sgml    2005-05-23 03:50:01.000000000 +0200
--- pgsql.new/doc/src/sgml/func.sgml    2005-05-24 17:30:22.000000000 +0200
***************
*** 4989,4994 ****
--- 4989,5003 ----
  
        <tbody>
         <row>
+         <entry><literal><function>add_months</function>(<type>date</type>, 
<type>integer</type>)</literal></entry>
+         <entry><type>date</type></entry>
+         <entry>Returns date plus n months</entry>
+         <entry><literal>add_months(date '2005-05-31',1)</literal></entry>
+         <entry>2005-06-30<literal></literal></entry>
+        </row>
+ 
+ 
+        <row>
          <entry><literal><function>age</function>(<type>timestamp</type>, 
<type>timestamp</type>)</literal></entry>
          <entry><type>interval</type></entry>
          <entry>Subtract arguments, producing a <quote>symbolic</> result that
***************
*** 5099,5104 ****
--- 5108,5121 ----
         </row>
  
         <row>
+         
<entry><literal><function>last_day</function>(<type>date</type>)</literal></entry>
+         <entry><type>date</type></entry>
+         <entry>Returns last day of the month based on a date value</entry>
+         <entry><literal>last_day(date '2005-05-24')</literal></entry>
+         <entry>2005-05-31<literal></literal></entry>
+        </row>
+ 
+        <row>
          <entry><literal><function>localtime</function></literal></entry>
          <entry><type>time</type></entry>
          <entry>Time of day; see <xref linkend="functions-datetime-current">
***************
*** 5117,5122 ****
--- 5134,5147 ----
         </row>
  
         <row>
+         <entry><literal><function>next_day</function>(<type>date</type>, 
<type>text</type>)</literal></entry>
+         <entry><type>date</type></entry>
+         <entry>Returns the first weekday that is greather than a date 
value.</entry>
+         <entry><literal>next_day(date '2005-05-24', 
'monday')</literal></entry>
+         <entry>2005-05-30<literal></literal></entry>
+        </row>
+ 
+        <row>
          <entry><literal><function>now</function>()</literal></entry>
          <entry><type>timestamp with time zone</type></entry>
          <entry>Current date and time (equivalent to
***************
*** 5128,5133 ****
--- 5153,5169 ----
         </row>
  
         <row>
+         
<entry><literal><function>months_between</function>(<type>date</type>, 
<type>date</type>)</literal></entry>
+         <entry><type>float8</type></entry>
+         <entry>Returns the number of months between date1 and date2. If
+       a fractional month is calculated, the months_between  function
+       calculates the fraction based on a 31-day month.</entry>
+         <entry><literal>months_between(date '1995-02-02', date 
'1995-01-01')</literal></entry>
+         <entry>1.03225806451613<literal></literal></entry>
+        </row>
+ 
+ 
+        <row>
          <entry><literal><function>timeofday()</function></literal></entry>
          <entry><type>text</type></entry>
          <entry>Current date and time; see <xref
diff -c -r --new-file pgsql.old/src/backend/utils/adt/oracle_compat.c 
pgsql.new/src/backend/utils/adt/oracle_compat.c
*** pgsql.old/src/backend/utils/adt/oracle_compat.c     2005-05-07 
17:18:17.000000000 +0200
--- pgsql.new/src/backend/utils/adt/oracle_compat.c     2005-05-24 
15:31:39.000000000 +0200
***************
*** 30,35 ****
--- 30,37 ----
  
  #include "utils/builtins.h"
  #include "utils/pg_locale.h"
+ #include "utils/date.h"
+ #include "utils/nabstime.h"
  #include "mb/pg_wchar.h"
  
  
***************
*** 52,57 ****
--- 54,75 ----
           const char *set, int setlen,
           bool doltrim, bool dortrim);
  
+ /*
+  * External (defined in PgSQL datetime.c (timestamp utils))
+  */
+ 
+ extern char *days[];
+ 
+ #define CHECK_SEQ_SEARCH(_l, _s) \
+ do { \
+         if ((_l) < 0) {                                                       
 \
+                 ereport(ERROR,  \
+                                 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),    
  \
+                                  errmsg("invalid value for %s", (_s))));      
  \
+         }                                                               \
+ } while (0)
+ 
+ 
  
  #ifdef USE_WIDE_UPPER_LOWER
  
***************
*** 1157,1159 ****
--- 1175,1350 ----
  
        PG_RETURN_TEXT_P(result);
  }
+ 
+ /*
+  * Search const value in char array
+  *
+  */
+ 
+ static int 
+ seq_search(char *name, char **array, int max)
+ {
+   char *p, *n, **a;
+   int last, i;
+ 
+   if (!*name) 
+     return -1;
+ 
+   *name = pg_toupper((unsigned char) *name);
+   for (last = 0, a = array; *a != NULL; a++) 
+     {     
+     
+       if (*name != **a)       
+       continue;
+ 
+       for (i = 1, p = *a + 1, n = name + 1;; n++, p++, i++) 
+       {
+         if (i == max) 
+           return a - array; 
+         if (*p == '\0') 
+           break; 
+         if (i > last) 
+           {
+             *n = pg_tolower((unsigned char) *n);
+             last = i;
+           }
+       
+         if (*n != *p)
+           break;
+       }
+     }
+   
+   return -1;
+ }
+ 
+ /********************************************************************
+  *
+  * next_day
+  *
+  * Syntax:
+  *
+  *     date next_day(date value, text weekday)
+  *
+  * Purpose:
+  *
+  *     Returns the first weekday that is greater than a date value.
+  *
+  ********************************************************************/
+ 
+ 
+ Datum next_day (PG_FUNCTION_ARGS)
+ {
+ 
+   DateADT day = PG_GETARG_DATEADT(0);
+   text *day_txt = PG_GETARG_TEXT_P(1);
+   int off;
+   
+   int d = seq_search(VARDATA(day_txt), days, VARATT_SIZEP(day_txt) - 
VARHDRSZ);
+   CHECK_SEQ_SEARCH(d, "DAY/Day/day");
+ 
+   off = d - j2day(day+POSTGRES_EPOCH_JDATE);
+ 
+   PG_RETURN_DATEADT((off <= 0) ? day+off+7 : day + off);
+ }
+ 
+ /********************************************************************
+  *
+  * last_day
+  *
+  * Syntax:
+  *
+  *     date last_day(date value)
+  *
+  * Purpose:
+  *
+  *    Returns last day of the month based on a date value
+  *
+  ********************************************************************/
+ 
+ 
+ Datum last_day (PG_FUNCTION_ARGS)
+ {
+   DateADT day = PG_GETARG_DATEADT(0);
+   DateADT result;
+   int y, m, d;
+   j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+   result = date2j(y, m+1, 1) - POSTGRES_EPOCH_JDATE;
+   
+   PG_RETURN_DATEADT(result - 1);
+ }
+ 
+ /********************************************************************
+  *
+  * months_between
+  *
+  * Syntax:
+  *
+  *     float8 months_between(date date1, date date2)
+  *
+  * Purpose:
+  *
+  *    Returns the number of months between date1 and date2. If 
+  *      a fractional month is calculated, the months_between  function 
+  *      calculates the fraction based on a 31-day month.
+  *
+  ********************************************************************/
+ 
+ 
+ Datum months_between (PG_FUNCTION_ARGS)
+ {
+   DateADT date1 = PG_GETARG_DATEADT(0);
+   DateADT date2 = PG_GETARG_DATEADT(1);
+ 
+   int y1, m1, d1;
+   int y2, m2, d2;
+ 
+   float8 result;
+ 
+   j2date(date1 + POSTGRES_EPOCH_JDATE, &y1, &m1, &d1);
+   j2date(date2 + POSTGRES_EPOCH_JDATE, &y2, &m2, &d2);
+ 
+   result = (y1 - y2) * 12 + (m1 - m2) + (d1 - d2) / 31.0;
+ 
+   PG_RETURN_FLOAT8(result);
+ 
+ }
+ 
+ /********************************************************************
+  *
+  * add_months
+  *
+  * Syntax:
+  *
+  *     date add_months(date day, int val)
+  *
+  * Purpose:
+  *
+  *    Returns a date plus n months.
+  *
+  ********************************************************************/
+ 
+ 
+ Datum add_months (PG_FUNCTION_ARGS)
+ {
+   DateADT day = PG_GETARG_DATEADT(0);
+   int n = PG_GETARG_INT32(1);
+   int y, m, d;
+   DateADT last_day, result;
+ 
+   j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+   result = date2j(y, m+n, d) - POSTGRES_EPOCH_JDATE;  
+ 
+   if (d > 28) 
+     {
+       m += 2; 
+       if (m  > 12) 
+       {
+         ++y; m -= 12;
+       }
+       last_day = date2j(y, m, 1) - POSTGRES_EPOCH_JDATE - 1;
+       if (last_day < result) 
+       result = last_day;
+     }
+  
+   PG_RETURN_DATEADT (result);
+ }
diff -c -r --new-file pgsql.old/src/include/catalog/pg_proc.h 
pgsql.new/src/include/catalog/pg_proc.h
*** pgsql.old/src/include/catalog/pg_proc.h     2005-05-20 03:29:55.000000000 
+0200
--- pgsql.new/src/include/catalog/pg_proc.h     2005-05-24 13:55:16.000000000 
+0200
***************
*** 2160,2166 ****
  DATA(insert OID = 1689 (  flatfile_update_trigger  PGNSP PGUID 12 f f t f v 0 
2279  "" _null_ _null_ _null_   flatfile_update_trigger - _null_ ));
  DESCR("update flat-file copy of a shared catalog");
  
! /* Oracle Compatibility Related Functions - By Edmund Mergl <[EMAIL 
PROTECTED]> */
  DATA(insert OID =  868 (  strpos         PGNSP PGUID 12 f f t f i 2 23 "25 
25" _null_ _null_ _null_ textpos - _null_ ));
  DESCR("find position of substring");
  DATA(insert OID =  870 (  lower                  PGNSP PGUID 12 f f t f i 1 
25 "25" _null_ _null_ _null_  lower - _null_ ));
--- 2160,2166 ----
  DATA(insert OID = 1689 (  flatfile_update_trigger  PGNSP PGUID 12 f f t f v 0 
2279  "" _null_ _null_ _null_   flatfile_update_trigger - _null_ ));
  DESCR("update flat-file copy of a shared catalog");
  
! /* Oracle Compatibility Related Functions - By Edmund Mergl <[EMAIL 
PROTECTED]>, Pavel Stehule <[EMAIL PROTECTED]> */
  DATA(insert OID =  868 (  strpos         PGNSP PGUID 12 f f t f i 2 23 "25 
25" _null_ _null_ _null_ textpos - _null_ ));
  DESCR("find position of substring");
  DATA(insert OID =  870 (  lower                  PGNSP PGUID 12 f f t f i 1 
25 "25" _null_ _null_ _null_  lower - _null_ ));
***************
*** 2195,2200 ****
--- 2195,2208 ----
  DESCR("trim selected characters from both ends of string");
  DATA(insert OID =  885 (  btrim                  PGNSP PGUID 12 f f t f i 1 
25 "25" _null_ _null_ _null_  btrim1 - _null_ ));
  DESCR("trim spaces from both ends of string");
+ DATA(insert OID =  901 ( next_day        PGNSP PGUID 12 f f t f i 2 1082 
"1082 25" _null_ _null_ _null_  next_day - _null_ ));
+ DESCR("return the first weekday that is greater than a date value");
+ DATA(insert OID =  902 ( last_day        PGNSP PGUID 12 f f t f i 1 1082 
"1082" _null_ _null_ _null_  last_day - _null_ ));
+ DESCR("last day of the month");
+ DATA(insert OID =  903 ( months_between   PGNSP PGUID 12 f f t f i 2 701 
"1082 1082" _null_ _null_ _null_  months_between - _null_ ));
+ DESCR("returns the number of months between date1 and date2");
+ DATA(insert OID =  904 (  add_months     PGNSP PGUID 12 f f t f i 2 1082 
"1082 23" _null_ _null_ _null_  add_months - _null_ ));
+ DESCR("returns a date plus n months");
  
  DATA(insert OID =  936 (  substring    PGNSP PGUID 12 f f t f i 3 25 "25 23 
23" _null_ _null_ _null_  text_substr - _null_ ));
  DESCR("return portion of string");
diff -c -r --new-file pgsql.old/src/include/utils/builtins.h 
pgsql.new/src/include/utils/builtins.h
*** pgsql.old/src/include/utils/builtins.h      2005-05-20 03:29:55.000000000 
+0200
--- pgsql.new/src/include/utils/builtins.h      2005-05-24 13:16:51.000000000 
+0200
***************
*** 649,654 ****
--- 649,658 ----
  extern Datum chr (PG_FUNCTION_ARGS);
  extern Datum repeat(PG_FUNCTION_ARGS);
  extern Datum ascii(PG_FUNCTION_ARGS);
+ extern Datum next_day(PG_FUNCTION_ARGS);
+ extern Datum last_day(PG_FUNCTION_ARGS);
+ extern Datum months_between(PG_FUNCTION_ARGS);
+ extern Datum add_months(PG_FUNCTION_ARGS);
  
  /* inet_net_ntop.c */
  extern char *inet_net_ntop(int af, const void *src, int bits,
diff -c -r --new-file pgsql.old/src/test/regress/expected/oracle.out 
pgsql.new/src/test/regress/expected/oracle.out
*** pgsql.old/src/test/regress/expected/oracle.out      1970-01-01 
01:00:00.000000000 +0100
--- pgsql.new/src/test/regress/expected/oracle.out      2005-05-24 
17:04:22.000000000 +0200
***************
*** 0 ****
--- 1,87 ----
+ -- 
+ -- test built-in date type oracle compatibility functions
+ --
+ SELECT add_months ('2003-08-01', 3);
+  add_months 
+ ------------
+  2003-11-01
+ (1 row)
+ 
+ SELECT add_months ('2003-08-01', -3);
+  add_months 
+ ------------
+  2003-05-01
+ (1 row)
+ 
+ SELECT add_months ('2003-08-21', -3);
+  add_months 
+ ------------
+  2003-05-21
+ (1 row)
+ 
+ SELECT add_months ('2003-01-31', 1);
+  add_months 
+ ------------
+  2003-02-28
+ (1 row)
+ 
+ SELECT last_day (to_date('2003/03/15', 'yyyy/mm/dd'));
+   last_day  
+ ------------
+  2003-03-31
+ (1 row)
+ 
+ SELECT last_day (to_date('2003/02/03', 'yyyy/mm/dd'));
+   last_day  
+ ------------
+  2003-02-28
+ (1 row)
+ 
+ SELECT last_day (to_date('2004/02/03', 'yyyy/mm/dd'));
+   last_day  
+ ------------
+  2004-02-29
+ (1 row)
+ 
+ SELECT next_day ('2003-08-01', 'TUESDAY');
+   next_day  
+ ------------
+  2003-08-05
+ (1 row)
+ 
+ SELECT next_day ('2003-08-06', 'WEDNESDAY');
+   next_day  
+ ------------
+  2003-08-13
+ (1 row)
+ 
+ SELECT next_day ('2003-08-06', 'SUNDAY');
+   next_day  
+ ------------
+  2003-08-10
+ (1 row)
+ 
+ SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date 
('2003/03/14', 'yyyy/mm/dd'));
+   months_between   
+ -------------------
+  -2.41935483870968
+ (1 row)
+ 
+ SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date 
('2003/03/14', 'yyyy/mm/dd'));
+   months_between  
+ ------------------
+  3.58064516129032
+ (1 row)
+ 
+ SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date 
('2003/07/02', 'yyyy/mm/dd'));
+  months_between 
+ ----------------
+               0
+ (1 row)
+ 
+ SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date 
('2003/06/02', 'yyyy/mm/dd'));
+  months_between 
+ ----------------
+               2
+ (1 row)
+ 
diff -c -r --new-file pgsql.old/src/test/regress/sql/oracle.sql 
pgsql.new/src/test/regress/sql/oracle.sql
*** pgsql.old/src/test/regress/sql/oracle.sql   1970-01-01 01:00:00.000000000 
+0100
--- pgsql.new/src/test/regress/sql/oracle.sql   2005-05-24 16:41:33.000000000 
+0200
***************
*** 0 ****
--- 1,21 ----
+ -- 
+ -- test built-in date type oracle compatibility functions
+ --
+ 
+ SELECT add_months ('2003-08-01', 3);
+ SELECT add_months ('2003-08-01', -3);
+ SELECT add_months ('2003-08-21', -3);
+ SELECT add_months ('2003-01-31', 1);
+ 
+ SELECT last_day (to_date('2003/03/15', 'yyyy/mm/dd'));
+ SELECT last_day (to_date('2003/02/03', 'yyyy/mm/dd'));
+ SELECT last_day (to_date('2004/02/03', 'yyyy/mm/dd'));
+ 
+ SELECT next_day ('2003-08-01', 'TUESDAY');
+ SELECT next_day ('2003-08-06', 'WEDNESDAY');
+ SELECT next_day ('2003-08-06', 'SUNDAY');
+ 
+ SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date 
('2003/03/14', 'yyyy/mm/dd'));
+ SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date 
('2003/03/14', 'yyyy/mm/dd'));
+ SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date 
('2003/07/02', 'yyyy/mm/dd'));
+ SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date 
('2003/06/02', 'yyyy/mm/dd'));
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to