The attached patch implements my proposal to extend support for the
ISO week date calendar.
I have added two new format fields for use with to_char, to_date and
to_timestamp:
- ID for day-of-week
- IDDD for day-of-year
This makes it possible to convert ISO week dates to and from text
fully represented in either week ('IYYY-IW-ID') or day-of-year
('IYYY-IDDD') format.
I have also added an 'isoyear' field for use with extract / date_part.
The patch includes documentation updates and some extra tests in the
regression suite for the new fields.
I have tried to implement these features with as little disruption to
the existing code as possible. I built on the existing date2iso*
functions in src/backend/utils/adt/timestamp.c, and added a few
functions of my own, but I wonder if these functions would be more
appropriately located in datetime.c, alongside date2j and j2date?
I'd also like to raise the topic of how conversion from text to ISO
week dates should be handled, where the user has specified a bogus
mixture of fields. Existing code basically ignores these issues; for
example, if a user were to call to_date('1998-01-01 2454050',
'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
the year field from YYYY, then overwriting year, month and day with
the values from the Julian date in J, then setting the month and day
normally from MM and DD.
2006-01-01 is not a valid representation of either of the values the
user specified. Now you might say "ask a silly question, get a silly
answer"; the user shouldn't send nonsense arguments to to_date and
expect a sensible result. But perhaps the right way to respond to a
broken timestamp definition is to throw an error, rather than behave
as though everything has gone to plan, and return something which is
not correct.
The same situation can arise if the user mixes ISO and Gregorian data;
how should Postgres deal with something like to_date('2006-250',
'IYYY-DDD')? The current behaviour in my patch is actually to assume
that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
of the ISO year 2006" is total gibberish. But perhaps it should be
throwing an error message.
That's all for now, thanks for your time.
BJ