Re: [BUGS] BUG #2977: dow doesn't conform to ISO-8601 - Mailing list pgsql-patches

I have implemented 'isodow' with the attached patch.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Am Mittwoch, 7. Februar 2007 11:24 schrieb Adriaan van Os:
> > Section 9.9.1 of the Postgres docs
> > <http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html>
> > mentions that the "week" subfield of the date_part function conforms to the
> > ISO-8601 standard.
>
> I see no such claim there.
>
> > You can not, in the same function, ignore ISO-8601 for one subfield and
> > follow it in another.
>
> I think we have pretty well shown that we can.
>
> > Besides, if in the same week Sunday comes before Monday, how can the result
> > of the "week" and "dow" fields conform to each other ?
>
> They don't.
>
> > If "dow" can not be changed for reasons of backward compatibility, I
> > suggest a new subfield "dayofweek" that does conform to the standard.
>
> That might be reasonable. (Or maybe "isodow".)
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.361
diff -c -c -r1.361 func.sgml
*** doc/src/sgml/func.sgml    16 Feb 2007 07:46:54 -0000    1.361
--- doc/src/sgml/func.sgml    18 Feb 2007 04:05:54 -0000
***************
*** 5806,5811 ****
--- 5806,5831 ----
       </varlistentry>

       <varlistentry>
+       <term><literal>isodow</literal></term>
+       <listitem>
+        <para>
+         The day of the week (1 - 7; Sunday is 7) (for
+         <type>timestamp</type> values only)
+        </para>
+
+ <screen>
+ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
+ <lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
+ </screen>
+        <para>
+         This is identical to <literal>dow</> except Sunday is <literal>7</>.
+         This matches the <acronym>ISO</> 8601 day of the week numbering.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><literal>isoyear</literal></term>
        <listitem>
         <para>
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.176
diff -c -c -r1.176 datetime.c
*** src/backend/utils/adt/datetime.c    16 Feb 2007 03:39:45 -0000    1.176
--- src/backend/utils/adt/datetime.c    18 Feb 2007 04:05:55 -0000
***************
*** 125,130 ****
--- 125,131 ----
      {"h", UNITS, DTK_HOUR},        /* "hour" */
      {LATE, RESERV, DTK_LATE},    /* "infinity" reserved for "late time" */
      {INVALID, RESERV, DTK_INVALID},        /* "invalid" reserved for bad time */
+     {"isodow", RESERV, DTK_ISODOW},        /* ISO day of week, Sunday == 7 */
      {"isoyear", UNITS, DTK_ISOYEAR},    /* year in terms of the ISO week date */
      {"j", UNITS, DTK_JULIAN},
      {"jan", MONTH, 1},
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.172
diff -c -c -r1.172 timestamp.c
*** src/backend/utils/adt/timestamp.c    16 Feb 2007 03:39:45 -0000    1.172
--- src/backend/utils/adt/timestamp.c    18 Feb 2007 04:05:57 -0000
***************
*** 4112,4122 ****
--- 4112,4125 ----
                      break;
                  }
              case DTK_DOW:
+             case DTK_ISODOW:
                  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
                      ereport(ERROR,
                              (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                               errmsg("timestamp out of range")));
                  result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
+                 if (val == DTK_ISODOW && result == 0)
+                     result = 7;
                  break;

              case DTK_DOY:
***************
*** 4322,4332 ****
--- 4325,4338 ----
                  break;

              case DTK_DOW:
+             case DTK_ISODOW:
                  if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
                      ereport(ERROR,
                              (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                               errmsg("timestamp out of range")));
                  result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
+                 if (val == DTK_ISODOW && result == 0)
+                     result = 7;
                  break;

              case DTK_DOY:
Index: src/include/utils/datetime.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.64
diff -c -c -r1.64 datetime.h
*** src/include/utils/datetime.h    16 Feb 2007 03:39:45 -0000    1.64
--- src/include/utils/datetime.h    18 Feb 2007 04:05:58 -0000
***************
*** 162,171 ****
  #define DTK_JULIAN        31

  #define DTK_DOW            32
! #define DTK_DOY            33
! #define DTK_TZ_HOUR        34
! #define DTK_TZ_MINUTE    35
! #define DTK_ISOYEAR        36


  /*
--- 162,172 ----
  #define DTK_JULIAN        31

  #define DTK_DOW            32
! #define DTK_ISODOW        33
! #define DTK_DOY            34
! #define DTK_TZ_HOUR        35
! #define DTK_TZ_MINUTE    36
! #define DTK_ISOYEAR        37


  /*
Index: src/interfaces/ecpg/pgtypeslib/dt.h
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/dt.h,v
retrieving revision 1.35
diff -c -c -r1.35 dt.h
*** src/interfaces/ecpg/pgtypeslib/dt.h    16 Feb 2007 03:39:45 -0000    1.35
--- src/interfaces/ecpg/pgtypeslib/dt.h    18 Feb 2007 04:05:58 -0000
***************
*** 154,163 ****
  #define DTK_JULIAN        31

  #define DTK_DOW            32
! #define DTK_DOY            33
! #define DTK_TZ_HOUR        34
! #define DTK_TZ_MINUTE    35
! #define DTK_ISOYEAR        36


  /*
--- 154,164 ----
  #define DTK_JULIAN        31

  #define DTK_DOW            32
! #define DTK_ISODOW        33
! #define DTK_DOY            34
! #define DTK_TZ_HOUR        35
! #define DTK_TZ_MINUTE    36
! #define DTK_ISOYEAR        37


  /*
Index: src/interfaces/ecpg/pgtypeslib/dt_common.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/dt_common.c,v
retrieving revision 1.36
diff -c -c -r1.36 dt_common.c
*** src/interfaces/ecpg/pgtypeslib/dt_common.c    26 Sep 2006 07:56:56 -0000    1.36
--- src/interfaces/ecpg/pgtypeslib/dt_common.c    18 Feb 2007 04:05:58 -0000
***************
*** 214,219 ****
--- 214,220 ----
      {"irkst", DTZ, POS(36)},    /* Irkutsk Summer Time */
      {"irkt", TZ, POS(32)},        /* Irkutsk Time */
      {"irt", TZ, POS(14)},        /* Iran Time */
+     {"isodow", RESERV, DTK_ISODOW},    /* ISO day of week, Sunday == 7 */
  #if 0
      isst
  #endif