This patch allows you to use IYYYY as format specifier to get the
ISO year, the year correspondeing to the ISO week number (IW).


Kurt

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 15:29:28 -0000
@@ -3981,6 +3981,10 @@
        <entry>last digit of year</entry>
        </row>
        <row>
+       <entry><literal>IYYYY</literal></entry>
+       <entry>ISO year (The first Thursday of the new year is in week 1.)</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 15:29:28 -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 15:29:30 -0000
@@ -544,6 +544,7 @@
        DCH_WW,
        DCH_W,
        DCH_Y_YYY,
+       DCH_IYYYY,
        DCH_YYYY,
        DCH_YYY,
        DCH_YY,
@@ -582,6 +583,7 @@
        DCH_ww,
        DCH_w,
        DCH_y_yyy,
+       DCH_iyyyy,
        DCH_yyyy,
        DCH_yyy,
        DCH_yy,
@@ -659,6 +661,7 @@
        {"HH12", 4, dch_time, DCH_HH12, TRUE},
        {"HH", 2, dch_time, DCH_HH, TRUE},
        {"IW", 2, dch_date, DCH_IW, TRUE},      /* I */
+       {"IYYYY", 5, dch_date, DCH_IYYYY, 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 +702,7 @@
        {"hh12", 4, dch_time, DCH_HH12, TRUE},
        {"hh", 2, dch_time, DCH_HH, TRUE},
        {"iw", 2, dch_date, DCH_IW, TRUE},      /* i */
+       {"iyyyy", 5, dch_date, DCH_IYYYY, TRUE},
        {"j", 1, dch_time, DCH_J, TRUE},        /* j */
        {"mi", 2, dch_time, DCH_MI, TRUE},      /* m */
        {"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2444,6 +2448,41 @@
                                tmfc->year += (cc * 1000);
 
                                return strdigits_len(inout) + 3 + SKIP_THth(suf);
+                       }
+                       break;
+               case DCH_IYYYY:
+                       if (flag == TO_CHAR)
+                       {
+                               if (tm->tm_year <= 9999 && tm->tm_year >= -9998)
+                                       sprintf(inout, "%0*d",
+                                               S_FM(suf) ? 0 : 4, 
+                                               YEAR_ABS(date2isoyear(
+                                                       tm->tm_year,
+                                                       tm->tm_mon,
+                                                       tm->tm_mday)));
+                               else
+                                       sprintf(inout, "%d",
+                                               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;
+                       }
+                       else if (flag == FROM_CHAR)
+                       {
+                               if (S_FM(suf) || is_next_separator(node))
+                               {
+                                       sscanf(inout, "%d", &tmfc->year);
+                                       return strdigits_len(inout) - 1 + 
SKIP_THth(suf);
+                               }
+                               else
+                               {
+                                       sscanf(inout, "%04d", &tmfc->year);
+                                       return 3 + SKIP_THth(suf);
+                               }
                        }
                        break;
                case DCH_YYYY:
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 15:29:31 -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 15:29:31 -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 IYYYY IW')
+   FROM TIMESTAMPTZ_TBL;
+ to_char_10 |     to_char
+------------+-----------------
+            | 
+            | 
+            | 1969 53 1970 01
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 01 1997 01
+            | 1997 01 1997 01
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 23 1997 24
+            | 2001 38 2001 38
+            | 2000 11 2000 11
+            | 2000 11 2000 11
+            | 2000 11 2000 11
+            | 2000 11 2000 11
+            | 2000 11 2000 11
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 23 1997 24
+            | 1997 06 1997 07
+            | 1997 06 1997 07
+            | 1997 07 1997 07
+            | 1997 07 1997 07
+            | 1997 07 1997 07
+            | 1997 07 1997 07
+            | 1997 07 1997 07
+            | 0097 07 0097 07
+            | 0097 07 0097 07
+            | 0597 07 0597 07
+            | 1097 07 1097 07
+            | 1697 07 1697 07
+            | 1797 07 1797 07
+            | 1897 07 1897 07
+            | 1997 07 1997 07
+            | 2097 07 2097 07
+            | 1996 09 1996 09
+            | 1996 09 1996 09
+            | 1996 09 1996 09
+            | 1996 53 1997 01
+            | 1996 53 1997 01
+            | 1997 01 1997 01
+            | 1997 09 1997 09
+            | 1997 09 1997 09
+            | 1997 52 1998 01
+            | 1997 53 1998 01
+            | 1999 53 1999 52
+            | 2000 01 1999 52
+            | 2000 53 2000 52
+            | 2001 01 2001 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', 'IYYYYIW');
+ 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 15:29:31 -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 IYYYY 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', 'IYYYYIW');
+
+SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
+
 
 SET DateStyle TO DEFAULT;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to