Re: [HACKERS] Date conversion using day of week

On Thu, 2011-03-31 at 08:00 -0700, Adrian Klaver wrote:
> On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> > On 31 March 2011 03:15, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> > > On 03/29/2011 04:24 PM, Adrian Klaver wrote:
> > >> ...
> > >> Well the strange part is only fails for SUN:...
[. . .]
> > 
> > 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.
[. . .]
> Just to play Devils advocate here, but why not? The day name is the same either 
> way, it is the index that changes. I am not sure why that could not be context 
> specific?

Just to be clear, the reason I was mixing things in this way was that I
wanted to validate that the dayname being passed was valid for the
current locale, and I could find no easier way of doing it.  FTR, I have
now resorted to finding the given dayname in the results of this query:

select day, to_char(day, 'dy') as dayname, 
       extract('dow' from day) as dayno
  from (
    select current_date + n as day 
      from generate_series(0, 6) as n) d;

If there is an easier way of doing this, please let me know.  As far as
the postgres API goes, exposing a function that would validate a dayname
returning a day number would resolve all of this for considerably less
complexity.  Also throwing an error in the to_date function for
unexpectedly mixed input formats seems quite reasonable.

Thanks for your time and attention.  The commercial RDBMS vendors could
learn a lot about customer support from this forum.

__
Marc Munro