Re: ISO year. - Mailing list pgsql-patches

Karel Zak wrote:
> On Thu, Dec 18, 2003 at 08:18:08PM +0100, Kurt Roeckx wrote:
> >      DCH_IW,
> > +    DCH_IYYY,
> > +    DCH_IYY,
> > +    DCH_IY,
> > +    DCH_I,
>
>  It's better :-) This patch is OK for me.

Which patch is OK?  The one attached?  You looked like you were making
changes to this patch in your later emails.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.182
diff -u -r1.182 func.sgml
--- doc/src/sgml/func.sgml    16 Dec 2003 15:27:58 -0000    1.182
+++ doc/src/sgml/func.sgml    18 Dec 2003 18:57:56 -0000
@@ -3981,6 +3981,22 @@
     <entry>last digit of year</entry>
        </row>
        <row>
+    <entry><literal>IYYY</literal></entry>
+    <entry>ISO year (4 and more digits)</entry>
+       </row>
+       <row>
+    <entry><literal>IYY</literal></entry>
+    <entry>last 3 digits of ISO year</entry>
+       </row>
+       <row>
+    <entry><literal>IY</literal></entry>
+    <entry>last 2 digits of ISO year</entry>
+       </row>
+       <row>
+    <entry><literal>I</literal></entry>
+    <entry>last digits of ISO year</entry>
+       </row>
+       <row>
     <entry><literal>BC</literal> or <literal>B.C.</literal> or
     <literal>AD</literal> or <literal>A.D.</literal></entry>
     <entry>era indicator (upper case)</entry>
Index: src/include/utils/timestamp.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/timestamp.h,v
retrieving revision 1.32
diff -u -r1.32 timestamp.h
--- src/include/utils/timestamp.h    29 Nov 2003 22:41:16 -0000    1.32
+++ src/include/utils/timestamp.h    18 Dec 2003 18:57:57 -0000
@@ -248,5 +248,6 @@

 extern void isoweek2date(int woy, int *year, int *mon, int *mday);
 extern int    date2isoweek(int year, int mon, int mday);
+extern int    date2isoyear(int year, int mon, int mday);

 #endif   /* TIMESTAMP_H */
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.70
diff -u -r1.70 formatting.c
--- src/backend/utils/adt/formatting.c    29 Nov 2003 19:51:58 -0000    1.70
+++ src/backend/utils/adt/formatting.c    18 Dec 2003 18:57:58 -0000
@@ -525,6 +525,10 @@
     DCH_HH12,
     DCH_HH,
     DCH_IW,
+    DCH_IYYY,
+    DCH_IYY,
+    DCH_IY,
+    DCH_I,
     DCH_J,
     DCH_MI,
     DCH_MM,
@@ -565,6 +569,10 @@
     DCH_hh12,
     DCH_hh,
     DCH_iw,
+    DCH_iyyy,
+    DCH_iyy,
+    DCH_iy,
+    DCH_i,
     DCH_j,
     DCH_mi,
     DCH_mm,
@@ -659,6 +667,10 @@
     {"HH12", 4, dch_time, DCH_HH12, TRUE},
     {"HH", 2, dch_time, DCH_HH, TRUE},
     {"IW", 2, dch_date, DCH_IW, TRUE},    /* I */
+    {"IYYY", 4, dch_date, DCH_IYYY, TRUE},
+    {"IYY", 3, dch_date, DCH_IYY, TRUE},
+    {"IY", 2, dch_date, DCH_IY, TRUE},
+    {"I", 1, dch_date, DCH_I, TRUE},
     {"J", 1, dch_date, DCH_J, TRUE},    /* J */
     {"MI", 2, dch_time, DCH_MI, TRUE},
     {"MM", 2, dch_date, DCH_MM, TRUE},
@@ -699,6 +711,10 @@
     {"hh12", 4, dch_time, DCH_HH12, TRUE},
     {"hh", 2, dch_time, DCH_HH, TRUE},
     {"iw", 2, dch_date, DCH_IW, TRUE},    /* i */
+    {"iyyy", 4, dch_date, DCH_IYYY, TRUE},
+    {"iyy", 3, dch_date, DCH_IYY, TRUE},
+    {"iy", 2, dch_date, DCH_IY, TRUE},
+    {"i", 1, dch_date, DCH_I, TRUE},
     {"j", 1, dch_time, DCH_J, TRUE},    /* j */
     {"mi", 2, dch_time, DCH_MI, TRUE},    /* m */
     {"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2447,12 +2463,26 @@
             }
             break;
         case DCH_YYYY:
+        case DCH_IYYY:
             if (flag == TO_CHAR)
             {
                 if (tm->tm_year <= 9999 && tm->tm_year >= -9998)
-                    sprintf(inout, "%0*d", S_FM(suf) ? 0 : 4, YEAR_ABS(tm->tm_year));
-                else
-                    sprintf(inout, "%d", YEAR_ABS(tm->tm_year));
+                    sprintf(inout, "%0*d",
+                        S_FM(suf) ? 0 : 4,
+                        arg == DCH_YYYY ?
+                        YEAR_ABS(tm->tm_year) :
+                        YEAR_ABS(date2isoyear(
+                            tm->tm_year,
+                            tm->tm_mon,
+                            tm->tm_mday)));
+                else
+                    sprintf(inout, "%d",
+                        arg == DCH_YYYY ?
+                        YEAR_ABS(tm->tm_year) :
+                        YEAR_ABS(date2isoyear(
+                            tm->tm_year,
+                            tm->tm_mon,
+                            tm->tm_mday)));
                 if (S_THth(suf))
                     str_numth(p_inout, inout, S_TH_TYPE(suf));
                 return strlen(p_inout) - 1;
@@ -2472,9 +2502,14 @@
             }
             break;
         case DCH_YYY:
+        case DCH_IYY:
             if (flag == TO_CHAR)
             {
-                snprintf(buff, sizeof(buff), "%03d", YEAR_ABS(tm->tm_year));
+                snprintf(buff, sizeof(buff), "%03d",
+                    arg == DCH_YYY ?
+                    YEAR_ABS(tm->tm_year) :
+                    YEAR_ABS(date2isoyear(tm->tm_year,
+                        tm->tm_mon, tm->tm_mday)));
                 i = strlen(buff);
                 strcpy(inout, buff + (i - 3));
                 if (S_THth(suf))
@@ -2502,9 +2537,14 @@
             }
             break;
         case DCH_YY:
+        case DCH_IY:
             if (flag == TO_CHAR)
             {
-                snprintf(buff, sizeof(buff), "%02d", YEAR_ABS(tm->tm_year));
+                snprintf(buff, sizeof(buff), "%02d",
+                    arg == DCH_YY ?
+                    YEAR_ABS(tm->tm_year) :
+                    YEAR_ABS(date2isoyear(tm->tm_year,
+                        tm->tm_mon, tm->tm_mday)));
                 i = strlen(buff);
                 strcpy(inout, buff + (i - 2));
                 if (S_THth(suf))
@@ -2532,9 +2572,14 @@
             }
             break;
         case DCH_Y:
+        case DCH_I:
             if (flag == TO_CHAR)
             {
-                snprintf(buff, sizeof(buff), "%1d", YEAR_ABS(tm->tm_year));
+                snprintf(buff, sizeof(buff), "%1d",
+                    arg == DCH_Y ?
+                    YEAR_ABS(tm->tm_year) :
+                    YEAR_ABS(date2isoyear(tm->tm_year,
+                        tm->tm_mon, tm->tm_mday)));
                 i = strlen(buff);
                 strcpy(inout, buff + (i - 1));
                 if (S_THth(suf))
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.97
diff -u -r1.97 timestamp.c
--- src/backend/utils/adt/timestamp.c    29 Nov 2003 19:51:59 -0000    1.97
+++ src/backend/utils/adt/timestamp.c    18 Dec 2003 18:57:59 -0000
@@ -2840,7 +2840,7 @@

 /* isoweek2date()
  * Convert ISO week of year number to date.
- * The year field must be specified!
+ * The year field must be specified with the ISO year!
  * karel 2000/08/07
  */
 void
@@ -2917,6 +2917,64 @@
     }

     return (int) result;
+}
+
+
+/* date2isoyear()
+ *
+ *    Returns ISO 8601 year number.
+ */
+int
+date2isoyear(int year, int mon, int mday)
+{
+    float8    result;
+    int    day0,
+        day4,
+        dayn;
+
+    /* current day */
+    dayn = date2j(year, mon, mday);
+
+    /* fourth day of current year */
+    day4 = date2j(year, 1, 4);
+
+    /* day0 == offset to first day of week (Monday) */
+    day0 = j2day(day4 - 1);
+
+    /*
+     * We need the first week containing a Thursday, otherwise this day
+     * falls into the previous year for purposes of counting weeks
+     */
+    if (dayn < (day4 - day0))
+    {
+        day4 = date2j(year - 1, 1, 4);
+
+        /* day0 == offset to first day of week (Monday) */
+        day0 = j2day(day4 - 1);
+
+        year--;
+    }
+
+    result = (((dayn - (day4 - day0)) / 7) + 1);
+
+    /*
+     * Sometimes the last few days in a year will fall into the first week
+     * of the next year, so check for this.
+     */
+    if (result >= 53)
+    {
+        day4 = date2j(year + 1, 1, 4);
+
+        /* day0 == offset to first day of week (Monday) */
+        day0 = j2day(day4 - 1);
+
+        if (dayn >= (day4 - day0))
+        {
+            year++;
+        }
+    }
+
+    return year;
 }


Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.13
diff -u -r1.13 timestamptz.out
--- src/test/regress/expected/timestamptz.out    25 Sep 2003 06:58:06 -0000    1.13
+++ src/test/regress/expected/timestamptz.out    18 Dec 2003 18:57:59 -0000
@@ -1317,6 +1317,76 @@
            | 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
 (64 rows)

+SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
+   FROM TIMESTAMPTZ_TBL;
+ to_char_10 |     to_char
+------------+--------------------------
+            |
+            |
+            | 1969 53 1970 970 70 0 01
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 01 1997 997 97 7 01
+            | 1997 01 1997 997 97 7 01
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 23 1997 997 97 7 24
+            | 2001 38 2001 001 01 1 38
+            | 2000 11 2000 000 00 0 11
+            | 2000 11 2000 000 00 0 11
+            | 2000 11 2000 000 00 0 11
+            | 2000 11 2000 000 00 0 11
+            | 2000 11 2000 000 00 0 11
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 23 1997 997 97 7 24
+            | 1997 06 1997 997 97 7 07
+            | 1997 06 1997 997 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 0097 07 0097 097 97 7 07
+            | 0097 07 0097 097 97 7 07
+            | 0597 07 0597 597 97 7 07
+            | 1097 07 1097 097 97 7 07
+            | 1697 07 1697 697 97 7 07
+            | 1797 07 1797 797 97 7 07
+            | 1897 07 1897 897 97 7 07
+            | 1997 07 1997 997 97 7 07
+            | 2097 07 2097 097 97 7 07
+            | 1996 09 1996 996 96 6 09
+            | 1996 09 1996 996 96 6 09
+            | 1996 09 1996 996 96 6 09
+            | 1996 53 1997 997 97 7 01
+            | 1996 53 1997 997 97 7 01
+            | 1997 01 1997 997 97 7 01
+            | 1997 09 1997 997 97 7 09
+            | 1997 09 1997 997 97 7 09
+            | 1997 52 1998 998 98 8 01
+            | 1997 53 1998 998 98 8 01
+            | 1999 53 1999 999 99 9 52
+            | 2000 01 1999 999 99 9 52
+            | 2000 53 2000 000 00 0 52
+            | 2001 01 2001 001 01 1 01
+(64 rows)
+
 -- TO_TIMESTAMP()
 SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
  to_timestamp_1 |       to_timestamp
@@ -1400,6 +1470,18 @@
  to_timestamp_14 |         to_timestamp
 -----------------+------------------------------
                  | Thu Nov 16 00:00:00 1995 PST
+(1 row)
+
+SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
+ to_timestamp_15 |         to_timestamp
+-----------------+------------------------------
+                 | Mon Dec 29 00:00:00 2003 PST
+(1 row)
+
+SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
+ to_timestamp_16 |         to_timestamp
+-----------------+------------------------------
+                 | Thu Jan 01 00:00:00 2004 PST
 (1 row)

 SET DateStyle TO DEFAULT;
Index: src/test/regress/sql/timestamptz.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/timestamptz.sql,v
retrieving revision 1.4
diff -u -r1.4 timestamptz.sql
--- src/test/regress/sql/timestamptz.sql    29 Jul 2003 00:03:19 -0000    1.4
+++ src/test/regress/sql/timestamptz.sql    18 Dec 2003 18:58:00 -0000
@@ -189,6 +189,9 @@
 SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
    FROM TIMESTAMPTZ_TBL;

+SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
+   FROM TIMESTAMPTZ_TBL;
+
 -- TO_TIMESTAMP()
 SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');

@@ -219,5 +222,10 @@
 SELECT '' AS to_timestamp_13, to_timestamp('95-1116', 'YY-MMDD');

 SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
+
+SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
+
+SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
+

 SET DateStyle TO DEFAULT;