Thread: ISO week dates
Hey guys,
I have a question regarding the ISO 8601 week date format. Outputting dates in this format seems to be partially supported, and rather inconsistent. The documentation for to_char() lists 'IYYY' (ISO year) and 'IW' (ISO week) as format patterns, but there is no "ISO day of week" format pattern to complete the set.
A full ISO week date is written as "<year>-W<week>-<day>", where <day> is the day of week with Monday = 1 and Sunday = 7.
The format pattern 'D' does not help, since it numbers weekdays beginning at Sunday = 1.
You could use the extract() function instead, but again, support is partial and inconsistent. You can get the right day of week by using the 'dow' field and adding one, the 'week' field returns the ISO week, but the 'year' field returns the Gregorian year!
So to_char() has the ISO year and week, but not the day. extract() has ISO day (sort of) and week, but not the year.
Granted you can put a working ISO format together by using both functions; something like
create function to_iso(timestamp) returns text as $$
SELECT to_char('IYYY', $1) || '-W' || extract(week, $1) || '-' || (extract(dow, $1) + 1)
$$ language sql immutable;
... but this seems unnecessarily awkward. Why not:
* 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'?
Regards,
BJ
I have a question regarding the ISO 8601 week date format. Outputting dates in this format seems to be partially supported, and rather inconsistent. The documentation for to_char() lists 'IYYY' (ISO year) and 'IW' (ISO week) as format patterns, but there is no "ISO day of week" format pattern to complete the set.
A full ISO week date is written as "<year>-W<week>-<day>", where <day> is the day of week with Monday = 1 and Sunday = 7.
The format pattern 'D' does not help, since it numbers weekdays beginning at Sunday = 1.
You could use the extract() function instead, but again, support is partial and inconsistent. You can get the right day of week by using the 'dow' field and adding one, the 'week' field returns the ISO week, but the 'year' field returns the Gregorian year!
So to_char() has the ISO year and week, but not the day. extract() has ISO day (sort of) and week, but not the year.
Granted you can put a working ISO format together by using both functions; something like
create function to_iso(timestamp) returns text as $$
SELECT to_char('IYYY', $1) || '-W' || extract(week, $1) || '-' || (extract(dow, $1) + 1)
$$ language sql immutable;
... but this seems unnecessarily awkward. Why not:
* 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'?
Regards,
BJ
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? -- Peter Eisentraut http://developer.postgresql.org/~petere/
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;
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
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;
Peter Eisentraut wrote:
> 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?
Added to TODO:
* Add ISO day of week format 'ID' to to_char() where Monday = 1
* Add an ISO year field to extract() called 'isoyear'
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Peter Eisentraut wrote: >> 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? > > Added to TODO: > > * Add ISO day of week format 'ID' to to_char() where Monday = 1 > * Add an ISO year field to extract() called 'isoyear' Just verifying, but aren't both formats ISO? In that case maybe it'd be better to have a (per database) setting that specifies which one? I'm sorry for any extra noise, I don't remember much of the original discussion (yeah, I know, archives...). -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
On 11/23/06, Alban Hertroys <alban@magproductions.nl> wrote:
> Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> >> 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?
> >
> > Added to TODO:
> >
> > * Add ISO day of week format 'ID' to to_char() where Monday = 1
> > * Add an ISO year field to extract() called 'isoyear'
>
> Just verifying, but aren't both formats ISO? In that case maybe it'd be
> better to have a (per database) setting that specifies which one?
The term "ISO" is broad and perhaps a little misleading. ISO 8601
specifies many date and time formats, of which the "week date" is one.
The field I have tentatively named "isoyear" refers to the year,
according to the ISO week date calendar, which is similar to, but
distinct from, the Gregorian calendar.
I'm not particularly attached to the name "isoyear". If that is seen
as too vague, perhaps "weekyear" or something similar would work
better. It can easily be changed at this stage.
ISO 8601 provides for dates expressed in the Gregorian style and the
week date style. What I have tried to achieve with this patch, is to
allow users of Postgres to {specify|describe|operate on} dates in
either the Gregorian or week date calendars, as they prefer. It
really depends on context whether the Gregorian or week date is more
desirable.
As far as I know, the standard only provides for one numeric
representation of the "day of week", which begins the week at Monday =
1 and ends at Sunday = 7. Other conventions currently supported in
Postgres, such as Sunday = 0 or Sunday = 1, are to my knowledge
non-ISO. Hence the addition of 'ID' to the formatting functions.
I hope that provides some added clarity.
Brendan Jurd wrote:
> On 11/23/06, Alban Hertroys <alban@magproductions.nl> wrote:
>> Bruce Momjian wrote:
>> > Peter Eisentraut wrote:
>> >> 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?
>> >
>> > Added to TODO:
>> >
>> > * Add ISO day of week format 'ID' to to_char() where Monday = 1
>> > * Add an ISO year field to extract() called 'isoyear'
>>
>> Just verifying, but aren't both formats ISO? In that case maybe it'd be
>> better to have a (per database) setting that specifies which one?
>
> The term "ISO" is broad and perhaps a little misleading. ISO 8601
> specifies many date and time formats, of which the "week date" is one.
> The field I have tentatively named "isoyear" refers to the year,
> according to the ISO week date calendar, which is similar to, but
> distinct from, the Gregorian calendar.
Ah, now I remember; the distinction is "ISO 8601" vs. "Gregorian". You'd
think there'd be an ISO spec describing the gregorian calendar too.
> I'm not particularly attached to the name "isoyear". If that is seen
> as too vague, perhaps "weekyear" or something similar would work
> better. It can easily be changed at this stage.
That's not exactly the point I tried to make.
IMO whether gregorian or iso 8691 interpretation is used depends on user
settings; either locale or a client setting. After all, it only affects
the interpretation of the data.
Being able to force the interpretation to either representation in
queries would be useful too, I suppose.
> ISO 8601 provides for dates expressed in the Gregorian style and the
> week date style. What I have tried to achieve with this patch, is to
> allow users of Postgres to {specify|describe|operate on} dates in
> either the Gregorian or week date calendars, as they prefer. It
> really depends on context whether the Gregorian or week date is more
> desirable.
>
> As far as I know, the standard only provides for one numeric
> representation of the "day of week", which begins the week at Monday =
> 1 and ends at Sunday = 7. Other conventions currently supported in
IIRC, Sunday = 0 would be valid according to ISO 8601. I don't have the
spec available ATM, though. OTOH, I'm quite sure ISO 8601 specifies
weeks to start at monday... Odd that they (apparently) don't start
counting from 0.
> Postgres, such as Sunday = 0 or Sunday = 1, are to my knowledge
> non-ISO. Hence the addition of 'ID' to the formatting functions.
There doesn't seem to be much difference between Sunday = 0 and Sunday = 7.
> I hope that provides some added clarity.
Did you also take the (rather complicated) week numbering schemes into
account? I'm not even sure that this defers from Gregorian week numbers,
if something like that even exists.
IIRC there are years with the first few days in the last week of the
previous year, and there are years that have week numbers go up to 53. I
recall the rule to be that if Januari 1st is before wednesday, it is
called week 1, and otherwise it is whatever the last week number of the
previous year was.
Can you understand why I don't trust week numbers in project planning? :P
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On 11/24/06, Alban Hertroys <alban@magproductions.nl> wrote:
> Brendan Jurd wrote:
> > On 11/23/06, Alban Hertroys <alban@magproductions.nl> wrote:
> >> Just verifying, but aren't both formats ISO? In that case maybe it'd be
> >> better to have a (per database) setting that specifies which one?
> >
> > The term "ISO" is broad and perhaps a little misleading. ISO 8601
> > specifies many date and time formats, of which the "week date" is one.
> > The field I have tentatively named "isoyear" refers to the year,
> > according to the ISO week date calendar, which is similar to, but
> > distinct from, the Gregorian calendar.
>
> Ah, now I remember; the distinction is "ISO 8601" vs. "Gregorian". You'd
> think there'd be an ISO spec describing the gregorian calendar too.
You got the wrong idea. ISO 8601 describes various formats that can
be used to describe dates and times, including:
* Gregorian formats with year, month and day-of-month,
* a "week date" format with year, week, and day-of-week, and
* an "ordinal date" format with the year and day-of-year.
The term "ISO date" ambiguously refers to a date which conforms to any
of the above formats.
>
> > ISO 8601 provides for dates expressed in the Gregorian style and the
> > week date style. What I have tried to achieve with this patch, is to
> > allow users of Postgres to {specify|describe|operate on} dates in
> > either the Gregorian or week date calendars, as they prefer. It
> > really depends on context whether the Gregorian or week date is more
> > desirable.
> >
> > As far as I know, the standard only provides for one numeric
> > representation of the "day of week", which begins the week at Monday =
> > 1 and ends at Sunday = 7. Other conventions currently supported in
>
> IIRC, Sunday = 0 would be valid according to ISO 8601. I don't have the
> spec available ATM, though. OTOH, I'm quite sure ISO 8601 specifies
> weeks to start at monday... Odd that they (apparently) don't start
> counting from 0.
It's not odd. We don't start counting months or weeks from zero.
I can't speak for the authors of 8601, but the numbering of months,
weeks, and days is the same as their ordinal position, so day "1" is
the "first" day, day "2" the second, and so on. This numbering system
lends itself well to natural descriptions of dates; 2006-W12-1 can be
readily understood as meaning "the first day of the twelfth week of
two thousand six".
> Did you also take the (rather complicated) week numbering schemes into
> account? I'm not even sure that this defers from Gregorian week numbers,
> if something like that even exists.
The week numbering was already implemented in Postgres when I proposed
these features. See the formatting fields "IYYY" and "IW" in the
docs, take a look at my original proposal at
http://archives.postgresql.org/pgsql-general/2006-10/msg00028.php and
the patch I submitted at
http://archives.postgresql.org/pgsql-patches/2006-11/msg00050.php
For more information about how week numbering works, see:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/ISO_week_date