Re: [GENERAL] Date conversion using day of week - Mailing list pgsql-hackers

Patch applied.

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

On Sat, Sep  1, 2012 at 05:14:39PM -0400, Bruce Momjian wrote:
> [Properly posted to hackers list]
>
> On Fri, Apr  1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
> > On 1 April 2011 02:00, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> > >> If we wanted to make it "work", then I think the thing to do would be
> > >> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
> > >> idea of interpreting DY and co. differently depending on whether the
> > >> other tokens happen to be ISO week or Gregorian.
> > >
> > > Just to play Devils advocate here, but why not? The day name is the same either
> > > way, it is the index that changes. I am not sure why that could not be context
> > > specific?
> > >
> >
> > To be perfectly honest, it's mostly because I was hoping not to spend
> > very much more of my time in formatting.c.  Every time I go in there I
> > come out a little bit less sane.  I'm concerned that if I do anything
>   -------------------------------
>
> Agreed!
>
> > further to it, I might inadvertently summon Chattur'gha or something.
> > But since you went to the trouble of calling me on my laziness, let's
> > take a look at the problem.
> >
> > At the time when the day-of-week token gets converted into a numeric
> > value and put into the TmFromChar.d field, the code has no knowledge
> > of whether the overall pattern is Gregorian or ISO (the DY field could
> > well be at the front of the pattern, for example).
> >
> > Later on, in do_to_timestamp, the code expects the 'd' value to make
> > sense given the mode (it should be zero-based on Sunday for Gregorian,
> > or one-based on Monday for ISO).  That's all well and good *except* in
> > the totally bizarre case raised by the OP.
> >
> > To resolve it, we could make TmFromChar.d always stored using the ISO
> > convention (because zero then has the useful property of meaning "not
> > set") and converted to the Gregorian convention as necessary in
> > do_to_timestamp.
>
> I did quite a bit if study on this and have a fix in the attached patch.
> Brendan above is correct about the cause of the problems.  Basically,
> 'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
> other times 0-6 with Sunday as start.  Plus, zero was used to designate
> "not supplied" in ISO tests.  Obviously the number and the start value
> both caused problems.
>
> The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
> throughout, allowing any mix of Gregorian and ISO week designations.  It
> is converted to ISO (or Unix format 0-6, Sunday=0) as needed.
>
> Sample output:
>
>         test=> select to_date('2011-13-MON', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-03-28
>         (1 row)
>
>         test=> select to_date('2011-13-SUN', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
>
>         test=> select to_date('2011-13-SAT', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-04-02
>         (1 row)
>
>         test=> select to_date('2011-13-1', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-03-28
>         (1 row)
>
>         test=> select to_date('2011-13-7', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
>
>         test=> select to_date('2011-13-0', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
>
> --
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
>   + It's impossible for everything to be true. +

> diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
> new file mode 100644
> index 25af8a2..2aa6df1
> *** a/src/backend/utils/adt/formatting.c
> --- b/src/backend/utils/adt/formatting.c
> *************** typedef struct
> *** 412,418 ****
>                   mi,
>                   ss,
>                   ssss,
> !                 d,
>                   dd,
>                   ddd,
>                   mm,
> --- 412,418 ----
>                   mi,
>                   ss,
>                   ssss,
> !                 d,                /* stored as 1-7, Sunday = 1, 0 means missing */
>                   dd,
>                   ddd,
>                   mm,
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2897,2902 ****
> --- 2897,2903 ----
>                   from_char_seq_search(&value, &s, days, ONE_UPPER,
>                                        MAX_DAY_LEN, n);
>                   from_char_set_int(&out->d, value, n);
> +                 out->d++;
>                   break;
>               case DCH_DY:
>               case DCH_Dy:
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2904,2909 ****
> --- 2905,2911 ----
>                   from_char_seq_search(&value, &s, days, ONE_UPPER,
>                                        MAX_DY_LEN, n);
>                   from_char_set_int(&out->d, value, n);
> +                 out->d++;
>                   break;
>               case DCH_DDD:
>                   from_char_parse_int(&out->ddd, &s, n);
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2919,2929 ****
>                   break;
>               case DCH_D:
>                   from_char_parse_int(&out->d, &s, n);
> -                 out->d--;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_ID:
>                   from_char_parse_int_len(&out->d, &s, 1, n);
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_WW:
> --- 2921,2933 ----
>                   break;
>               case DCH_D:
>                   from_char_parse_int(&out->d, &s, n);
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_ID:
>                   from_char_parse_int_len(&out->d, &s, 1, n);
> +                 /* Shift numbering to match Gregorian where Sunday = 1 */
> +                 if (++out->d > 7)
> +                     out->d = 1;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_WW:
> *************** do_to_timestamp(text *date_txt, text *fm
> *** 3534,3540 ****
>       if (tmfc.w)
>           tmfc.dd = (tmfc.w - 1) * 7 + 1;
>       if (tmfc.d)
> !         tm->tm_wday = tmfc.d;
>       if (tmfc.dd)
>           tm->tm_mday = tmfc.dd;
>       if (tmfc.ddd)
> --- 3538,3544 ----
>       if (tmfc.w)
>           tmfc.dd = (tmfc.w - 1) * 7 + 1;
>       if (tmfc.d)
> !         tm->tm_wday = tmfc.d - 1;    /* convert to native numbering */
>       if (tmfc.dd)
>           tm->tm_mday = tmfc.dd;
>       if (tmfc.ddd)
> diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
> new file mode 100644
> index 2adc178..50ef897
> *** a/src/backend/utils/adt/timestamp.c
> --- b/src/backend/utils/adt/timestamp.c
> *************** isoweek2date(int woy, int *year, int *mo
> *** 3775,3792 ****
>
>   /* isoweekdate2date()
>    *
> !  *    Convert an ISO 8601 week date (ISO year, ISO week and day of week) into a Gregorian date.
>    *    Populates year, mon, and mday with the correct Gregorian values.
>    *    year must be passed in as the ISO year.
>    */
>   void
> ! isoweekdate2date(int isoweek, int isowday, int *year, int *mon, int *mday)
>   {
>       int            jday;
>
>       jday = isoweek2j(*year, isoweek);
> !     jday += isowday - 1;
> !
>       j2date(jday, year, mon, mday);
>   }
>
> --- 3775,3796 ----
>
>   /* isoweekdate2date()
>    *
> !  *    Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
> !  *    Gregorian day of week sent so weekday strings can be supplied.
>    *    Populates year, mon, and mday with the correct Gregorian values.
>    *    year must be passed in as the ISO year.
>    */
>   void
> ! isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
>   {
>       int            jday;
>
>       jday = isoweek2j(*year, isoweek);
> !     /* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
> !     if (wday > 1)
> !         jday += wday - 2;
> !     else
> !         jday += 6;
>       j2date(jday, year, mon, mday);
>   }
>
> diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
> new file mode 100644
> index 665e969..e7cdb41
> *** a/src/include/utils/timestamp.h
> --- b/src/include/utils/timestamp.h
> *************** extern int    timestamp_cmp_internal(Timest
> *** 236,242 ****
>
>   extern int    isoweek2j(int year, int week);
>   extern void isoweek2date(int woy, int *year, int *mon, int *mday);
> ! extern void isoweekdate2date(int isoweek, int isowday, int *year, int *mon, int *mday);
>   extern int    date2isoweek(int year, int mon, int mday);
>   extern int    date2isoyear(int year, int mon, int mday);
>   extern int    date2isoyearday(int year, int mon, int mday);
> --- 236,242 ----
>
>   extern int    isoweek2j(int year, int week);
>   extern void isoweek2date(int woy, int *year, int *mon, int *mday);
> ! extern void isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday);
>   extern int    date2isoweek(int year, int mon, int mday);
>   extern int    date2isoyear(int year, int mon, int mday);
>   extern int    date2isoyearday(int year, int mon, int mday);

>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

  + It's impossible for everything to be true. +