[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@xxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
