Peter Eisentraut a ecrit le 07/10/2006 09:01:
> Brendan Jurd wrote:
>> * add an ISO day format pattern to to_char() called 'ID', which
>> starts at Monday = 1, and
>> * add an ISO year field to extract() called 'isoyear'?
>
> That seems reasonable. Do you volunteer?
>

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
to_char(('2006-10-'||a+2)::date, 'D') as "D field"
from generate_series(1, 15) as a;
dow | ID field | D field
-----------+----------+---------
TUESDAY | 2 | 3
WEDNESDAY | 3 | 4
THURSDAY | 4 | 5
FRIDAY | 5 | 6
SATURDAY | 6 | 7
SUNDAY | 7 | 1
MONDAY | 1 | 2
TUESDAY | 2 | 3
WEDNESDAY | 3 | 4
THURSDAY | 4 | 5
FRIDAY | 5 | 6
SATURDAY | 6 | 7
SUNDAY | 7 | 1
MONDAY | 1 | 2
TUESDAY | 2 | 3
(15 rows)

I just want to know if my patch is interesting... and if it's OK, I can
work on the ISO year field.

Regards.


--
Guillaume.

Index: src/backend/utils/adt/formatting.c
================================================== =================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.113
diff -r1.113 formatting.c
555a556
> DCH_ID,
599a601
> DCH_id,
698c700,701
< {"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
---
> {"ID", 2, dch_date, DCH_ID, TRUE}, /* I */
> {"IW", 2, dch_date, DCH_IW, TRUE},
742c745,746
< {"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
---
> {"id", 2, dch_date, DCH_ID, TRUE}, /* i */
> {"iw", 2, dch_date, DCH_IW, TRUE},
832c836
< DCH_FX, -1, DCH_HH24, DCH_IW, DCH_J, -1, -1, DCH_MI, -1, -1,
---
> DCH_FX, -1, DCH_HH24, DCH_ID, DCH_J, -1, -1, DCH_MI, -1, -1,
835c839
< DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iw, DCH_j, -1, -1, DCH_mi,
---
> DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_id, DCH_j, -1, -1, DCH_mi,
2369a2374
> case DCH_ID:
2373c2378,2381
< sprintf(inout, "%d", tm->tm_wday + 1);
---
> if (arg == DCH_D)
> sprintf(inout, "%d", tm->tm_wday + 1);
> else
> sprintf(inout, "%d", (tm->tm_wday == 0) ? 7 : tm->tm_wday);
2380a2389,2390
> if (arg == DCH_ID && tmfc->d == 7)
> tmfc->d = 0;


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Guillaume Lelarge wrote:
> I've tried to work on the first one, the ISO day field. My patch is
> attached and is against CVS HEAD. It only takes care of the code,
> nothing is done for documentation matter. It works with me :

I haven't been following this thread, but I just wanted to point out
that we prefer context diffs.

Please resend the patch as a context diff, using "diff -c" or "cvs diff
-c", so that it's easier to review.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Heikki Linnakangas a ecrit le 12/10/2006 12:43:
> Guillaume Lelarge wrote:
>> I've tried to work on the first one, the ISO day field. My patch is
>> attached and is against CVS HEAD. It only takes care of the code,
>> nothing is done for documentation matter. It works with me :
>
> I haven't been following this thread, but I just wanted to point out
> that we prefer context diffs.
>
> Please resend the patch as a context diff, using "diff -c" or "cvs diff
> -c", so that it's easier to review.
>

Sorry, you're right. Here it is.


--
Guillaume.

Index: src/backend/utils/adt/formatting.c
================================================== =================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.113
diff -c -r1.113 formatting.c
*** src/backend/utils/adt/formatting.c 4 Oct 2006 00:29:59 -0000 1.113
--- src/backend/utils/adt/formatting.c 12 Oct 2006 10:54:01 -0000
***************
*** 553,558 ****
--- 553,559 ----
DCH_HH24,
DCH_HH12,
DCH_HH,
+ DCH_ID,
DCH_IW,
DCH_IYYY,
DCH_IYY,
***************
*** 597,602 ****
--- 598,604 ----
DCH_hh24,
DCH_hh12,
DCH_hh,
+ DCH_id,
DCH_iw,
DCH_iyyy,
DCH_iyy,
***************
*** 695,701 ****
{"HH24", 4, dch_time, DCH_HH24, TRUE}, /* H */
{"HH12", 4, dch_time, DCH_HH12, TRUE},
{"HH", 2, dch_time, DCH_HH, TRUE},
! {"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
{"IYYY", 4, dch_date, DCH_IYYY, TRUE},
{"IYY", 3, dch_date, DCH_IYY, TRUE},
{"IY", 2, dch_date, DCH_IY, TRUE},
--- 697,704 ----
{"HH24", 4, dch_time, DCH_HH24, TRUE}, /* H */
{"HH12", 4, dch_time, DCH_HH12, TRUE},
{"HH", 2, dch_time, DCH_HH, TRUE},
! {"ID", 2, dch_date, DCH_ID, TRUE}, /* I */
! {"IW", 2, dch_date, DCH_IW, TRUE},
{"IYYY", 4, dch_date, DCH_IYYY, TRUE},
{"IYY", 3, dch_date, DCH_IYY, TRUE},
{"IY", 2, dch_date, DCH_IY, TRUE},
***************
*** 739,745 ****
{"hh24", 4, dch_time, DCH_HH24, TRUE}, /* h */
{"hh12", 4, dch_time, DCH_HH12, TRUE},
{"hh", 2, dch_time, DCH_HH, TRUE},
! {"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
{"iyyy", 4, dch_date, DCH_IYYY, TRUE},
{"iyy", 3, dch_date, DCH_IYY, TRUE},
{"iy", 2, dch_date, DCH_IY, TRUE},
--- 742,749 ----
{"hh24", 4, dch_time, DCH_HH24, TRUE}, /* h */
{"hh12", 4, dch_time, DCH_HH12, TRUE},
{"hh", 2, dch_time, DCH_HH, TRUE},
! {"id", 2, dch_date, DCH_ID, TRUE}, /* i */
! {"iw", 2, dch_date, DCH_IW, TRUE},
{"iyyy", 4, dch_date, DCH_IYYY, TRUE},
{"iyy", 3, dch_date, DCH_IYY, TRUE},
{"iy", 2, dch_date, DCH_IY, TRUE},
***************
*** 829,838 ****
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! DCH_FX, -1, DCH_HH24, DCH_IW, DCH_J, -1, -1, DCH_MI, -1, -1,
DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
! DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iw, DCH_j, -1, -1, DCH_mi,
-1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_ssss, DCH_tz, DCH_us, -1, DCH_ww,
-1, DCH_y_yyy, -1, -1, -1, -1

--- 833,842 ----
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! DCH_FX, -1, DCH_HH24, DCH_ID, DCH_J, -1, -1, DCH_MI, -1, -1,
DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
! DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_id, DCH_j, -1, -1, DCH_mi,
-1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_ssss, DCH_tz, DCH_us, -1, DCH_ww,
-1, DCH_y_yyy, -1, -1, -1, -1

***************
*** 2367,2376 ****
}
break;
case DCH_D:
INVALID_FOR_INTERVAL;
if (is_to_char)
{
! sprintf(inout, "%d", tm->tm_wday + 1);
if (S_THth(suf))
str_numth(p_inout, inout, S_TH_TYPE(suf));
return strlen(p_inout);
--- 2371,2384 ----
}
break;
case DCH_D:
+ case DCH_ID:
INVALID_FOR_INTERVAL;
if (is_to_char)
{
! if (arg == DCH_D)
! sprintf(inout, "%d", tm->tm_wday + 1);
! else
! sprintf(inout, "%d", (tm->tm_wday == 0) ? 7 : tm->tm_wday);
if (S_THth(suf))
str_numth(p_inout, inout, S_TH_TYPE(suf));
return strlen(p_inout);
***************
*** 2378,2383 ****
--- 2386,2393 ----
else
{
sscanf(inout, "%1d", &tmfc->d);
+ if (arg == DCH_ID && tmfc->d == 7)
+ tmfc->d = 0;
return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Guillaume Lelarge wrote:
> I've tried to work on the first one, the ISO day field. My patch is
> attached and is against CVS HEAD. It only takes care of the code,
> nothing is done for documentation matter. It works with me :
>
> toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
> to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
> to_char(('2006-10-'||a+2)::date, 'D') as "D field"
> from generate_series(1, 15) as a;

There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
is the two-digit ISO week, but 'ID' would be the one-digit ISO
day-of-the-week. I'm not sure we can fix that, but I wanted to point
it out.

We should also support a format for ISO day-of-the-year, which might
be 'IDDD'.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Peter Eisentraut a écrit :
> Guillaume Lelarge wrote:
>> I've tried to work on the first one, the ISO day field. My patch is
>> attached and is against CVS HEAD. It only takes care of the code,
>> nothing is done for documentation matter. It works with me :
>>
>> toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
>> to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
>> to_char(('2006-10-'||a+2)::date, 'D') as "D field"
>> from generate_series(1, 15) as a;
>
> There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
> is the two-digit ISO week, but 'ID' would be the one-digit ISO
> day-of-the-week. I'm not sure we can fix that, but I wanted to point
> it out.
>

Is there a two digit ISO day of the week ? If not, we should use ID. As
you say, I don't know what we can do about that. I used Brendan Jurd's
idea, perhaps he can tell us more on this matter.

> We should also support a format for ISO day-of-the-year, which might
> be 'IDDD'.
>

I will work tomorrow on this one.

Regards.


--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Guillaume Lelarge a ecrit le 12/10/2006 20:20:
> Peter Eisentraut a écrit :
>> We should also support a format for ISO day-of-the-year, which might
>> be 'IDDD'.
>>
>
> I will work tomorrow on this one.
>

Don't we already have it ? It seems ISO day-of-the-year is between 001
and 366 in leap years. Isn't this the definition for DDD format ? if Im'
right, I just need to add the IDDD pattern ?


--
Guillaume.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Guillaume Lelarge wrote:
> Guillaume Lelarge a ecrit le 12/10/2006 20:20:
> > Peter Eisentraut a écrit :
> >> We should also support a format for ISO day-of-the-year, which
> >> might be 'IDDD'.
> >
> > I will work tomorrow on this one.
>
> Don't we already have it ? It seems ISO day-of-the-year is between
> 001 and 366 in leap years. Isn't this the definition for DDD format ?
> if Im' right, I just need to add the IDDD pattern ?

The ISO 8601 day-of-the-year is aligned with the week-of-the-year. It
should be the case that day one of week one is also day one of the
year.

(As a particular example, day one of 2006 is January 2, 2006.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 10/13/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> Peter Eisentraut a écrit :
> >
> > There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
> > is the two-digit ISO week, but 'ID' would be the one-digit ISO
> > day-of-the-week. I'm not sure we can fix that, but I wanted to point
> > it out.
> >
>
> Is there a two digit ISO day of the week ? If not, we should use ID. As
> you say, I don't know what we can do about that. I used Brendan Jurd's
> idea, perhaps he can tell us more on this matter.
>

Thanks for your work so far Guillaume. I agree with Peter, it is
inconsistent to have a one-digit field represented by a two-character
code. However, I don't see a way around it. 'D' is already taken to
mean the non-ISO day-of-week, and 'I' is taken to mean the last digit
of the ISO year (although to be honest I don't see where this would be
useful).

This sort of thing is not unprecedented in to_char(). For example,
the codes 'HH24' and 'HH12' are four characters long, but resolve to a
two-digit result. 'DAY' resolves to nine characters, and so on.

Basically I think we're stuck with ID for day-of-week and IDDD for day-of-year.

I will take a look at implementing 'isoyear' for extract(), and also
start putting together a patch for the documentation. If Guillaume is
still interested in adding the IDDD field to to_char(), wonderful, if
not I will pick up from his ID patch and add IDDD to it.

Regards,
BJ

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Brendan Jurd a écrit :
> On 10/13/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:
>> Peter Eisentraut a écrit :
>> >
>> > There is an inconsistency here: 'IYYY' is the four-digit ISO year,
>> 'IW'
>> > is the two-digit ISO week, but 'ID' would be the one-digit ISO
>> > day-of-the-week. I'm not sure we can fix that, but I wanted to point
>> > it out.
>> >
>>
>> Is there a two digit ISO day of the week ? If not, we should use ID. As
>> you say, I don't know what we can do about that. I used Brendan Jurd's
>> idea, perhaps he can tell us more on this matter.
>>
>
> Thanks for your work so far Guillaume. I agree with Peter, it is
> inconsistent to have a one-digit field represented by a two-character
> code. However, I don't see a way around it. 'D' is already taken to
> mean the non-ISO day-of-week, and 'I' is taken to mean the last digit
> of the ISO year (although to be honest I don't see where this would be
> useful).
>
> This sort of thing is not unprecedented in to_char(). For example,
> the codes 'HH24' and 'HH12' are four characters long, but resolve to a
> two-digit result. 'DAY' resolves to nine characters, and so on.
>
> Basically I think we're stuck with ID for day-of-week and IDDD for
> day-of-year.
>
> I will take a look at implementing 'isoyear' for extract(), and also
> start putting together a patch for the documentation. If Guillaume is
> still interested in adding the IDDD field to to_char(), wonderful, if
> not I will pick up from his ID patch and add IDDD to it.
>

Sorry for the late answer. I'm still interested but, to be honest, I
don't think I will have the time to do it. Perhaps in a month or so.

Regards.


--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 11/9/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> Brendan Jurd a écrit :
> > I will take a look at implementing 'isoyear' for extract(), and also
> > start putting together a patch for the documentation. If Guillaume is
> > still interested in adding the IDDD field to to_char(), wonderful, if
> > not I will pick up from his ID patch and add IDDD to it.
> >
>
> Sorry for the late answer. I'm still interested but, to be honest, I
> don't think I will have the time to do it. Perhaps in a month or so.
>

No problem Guillaume. I'm actually nearly done adding in all these
features. Thank you for getting the ball rolling!

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend