On 31 March 2011 03:15, Steve Crawford <scrawf...@pinpointresearch.com> wrote:
> On 03/29/2011 04:24 PM, Adrian Klaver wrote:
>> ...
>> Well the strange part is only fails for SUN:...
>> test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
>> to_date
>> ------------
>> 2011-03-28
>> ...
>
> You specified Sunday as the day but the date returned is a Monday. I would
> categorize that as a bug. (Hackers cc'd). Since Sunday is the last day of an
> ISO week, it should have returned 2011-04-03.
>
> My first inclination without consulting source or morning coffee is that
> PostgreSQL is seeing Sunday as day zero. Note that while:
The relevant paragraphs in the docs are:
--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a "month" or "day of month" has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
With the attached patch applied, this is what happens instead:
# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
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.
Cheers,
BJ
diff --git a/src/backend/utils/adt/formatting.c
b/src/backend/utils/adt/formatting.c
index 45e36f9..5ad6437 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -720,12 +720,12 @@ static const KeyWord DCH_keywords[] = {
{"B.C.", 4, DCH_B_C, FALSE, FROM_CHAR_DATE_NONE}, /* B */
{"BC", 2, DCH_BC, FALSE, FROM_CHAR_DATE_NONE},
{"CC", 2, DCH_CC, TRUE, FROM_CHAR_DATE_NONE}, /* C */
- {"DAY", 3, DCH_DAY, FALSE, FROM_CHAR_DATE_NONE}, /* D */
+ {"DAY", 3, DCH_DAY, FALSE, FROM_CHAR_DATE_GREGORIAN},/* D */
{"DDD", 3, DCH_DDD, TRUE, FROM_CHAR_DATE_GREGORIAN},
{"DD", 2, DCH_DD, TRUE, FROM_CHAR_DATE_GREGORIAN},
- {"DY", 2, DCH_DY, FALSE, FROM_CHAR_DATE_NONE},
- {"Day", 3, DCH_Day, FALSE, FROM_CHAR_DATE_NONE},
- {"Dy", 2, DCH_Dy, FALSE, FROM_CHAR_DATE_NONE},
+ {"DY", 2, DCH_DY, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ {"Day", 3, DCH_Day, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ {"Dy", 2, DCH_Dy, FALSE, FROM_CHAR_DATE_GREGORIAN},
{"D", 1, DCH_D, TRUE, FROM_CHAR_DATE_GREGORIAN},
{"FX", 2, DCH_FX, FALSE, FROM_CHAR_DATE_NONE}, /* F */
{"HH24", 4, DCH_HH24, TRUE, FROM_CHAR_DATE_NONE}, /* H */
@@ -768,10 +768,10 @@ static const KeyWord DCH_keywords[] = {
{"b.c.", 4, DCH_b_c, FALSE, FROM_CHAR_DATE_NONE}, /* b */
{"bc", 2, DCH_bc, FALSE, FROM_CHAR_DATE_NONE},
{"cc", 2, DCH_CC, TRUE, FROM_CHAR_DATE_NONE}, /* c */
- {"day", 3, DCH_day, FALSE, FROM_CHAR_DATE_NONE}, /* d */
+ {"day", 3, DCH_day, FALSE, FROM_CHAR_DATE_GREGORIAN},/* d */
{"ddd", 3, DCH_DDD, TRUE, FROM_CHAR_DATE_GREGORIAN},
{"dd", 2, DCH_DD, TRUE, FROM_CHAR_DATE_GREGORIAN},
- {"dy", 2, DCH_dy, FALSE, FROM_CHAR_DATE_NONE},
+ {"dy", 2, DCH_dy, FALSE, FROM_CHAR_DATE_GREGORIAN},
{"d", 1, DCH_D, TRUE, FROM_CHAR_DATE_GREGORIAN},
{"fx", 2, DCH_FX, FALSE, FROM_CHAR_DATE_NONE}, /* f */
{"hh24", 4, DCH_HH24, TRUE, FROM_CHAR_DATE_NONE}, /* h */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
