Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)

From: "Brendan Jurd" <direvus(at)gmail(dot)com>
To: "Bruce Momjian" <bruce(at)momjian(dot)us>
Cc: "Guillaume Lelarge" <guillaume(at)lelarge(dot)info>, "Peter Eisentraut" <peter_e(at)gmx(dot)net>, "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
Date: 2007-07-17 14:47:36
Message-ID: 37ed240d0707170747p4f5c26ffx63fff2b5750c62e5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 4/3/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> Because this patch was not completed, I have added it to the TODO list:
>
> * Fix to_date()-related functions to consistently issue errors
>
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php

I'm now taking another run at this issue. Here's what I've got in mind.

There are three distinct conventions for specifying a date that we
consider in Postgres. These are
* Julian day,
* ISO week date, and
* Standard Gregorian.

Within an ISO week date, you can identify a date using either
* year, week and day-of-week, or
* year and day-of-year.

Likewise within a Gregorian date, you can identify a date using
* year, month and day-of-month,
* year, month, week-of-month and day-of-week (extremely weird, but there it is)
* year, week, and day-of-week, or
* year and day-of-year.

Chad Wagner mentioned that Oracle will allow a combination of Julian
and Gregorian formats so long as both formats yield the same date. If
we're going to stick with the theme of imitating Oracle, I propose the
following:

* No mixing of Gregorian and ISO fields permitted. If the format
string contains both Gregorian and ISO normative fields in any
sequence or combination, we throw an ERRCODE_INVALID_DATETIME_FORMAT
and reject the query.
* Either Gregorian or ISO format strings may include a Julian date
field, as long as the results are in agreement. If the results
disagree, we reject the query.
* Purely non-normative fields (like "Q") are completely and silently
disregarded.
* A Gregorian or ISO format may be over-constraining as long as all
values are in agreement. If there are any conflicts we reject the
query.

So, for example, we would reject something like "YYYY-IDDD" out of
hand because it combines the ISO and Gregorian conventions, making it
impossible to ascertain what the user really wants to do.

We would allow YYYY-MM-DD J as long as the result for the YYYY-MM-DD
part matches the result for the J part.

We would also allow something like YYYY-MM-DD D as long as the results
of YYYY-MM-DD and D matched. So to_date('2007-07-18 4', 'YYYY-MM-DD
D') would successfully return the date 18 July 2007, but if you tried
to_date('2007-07-18 5', 'YYYY-MM-DD D') you would get an error.

If there are no objections I'd be happy to cook a patch up.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-07-17 15:13:38 Re: Backend memory growing too much
Previous Message Tom Lane 2007-07-17 13:31:25 Re: Earlier suggestion to get gcov to work by adding $(CFLAGS) to module link line