Re: [GENERAL] Date conversion using day of week

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(dot)klaver(at)gmail(dot)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(at)momjian(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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