Re: inevitability of to_date() when converting representations which don't represent whole timestamps

I don't understand what you mean by "inevitability" in the subject.

On 2017-03-29 21:19:56 -0400, Shaun Cutts wrote:
> When being asked to convert a day of the week, the to_date() function
> returns the same day ('0001-01-01 BC’) no matter which day is
> converted:
>
> # select to_date(‘Monday’, ‘Day’)
> '0001-01-01 BC’
>
> # select to_date(‘Tuesday’, ‘Day’)
> '0001-01-01 BC’
>
> However, if it were to return a date that was that day of the week, it
> could be inverted:
>
> # select extract(dow from '0001-01-01 BC'::date); — this date should be the result of to_date(‘Sunday’, ‘Day’)
> 6
>
> # select extract(dow from '0001-01-02 BC'::date); — this date should be the result of to_date(‘Monday’, ‘Day’)
> 0
>
> ….
>
> David tells this is not a bug, but it still seems like a reasonable
> requirement on to_date() to me. Is there some reason why this isn’t
> possible?

The documentation warns that to_date “interpret input liberally, with
minimal error checking” and while it “produce[s] valid output, the
conversion can yield unexpected results.”

I would agree that producing the same date for every day of the week
crosses the line between “unexpected (but valid) result” and “bug”.

On the other hand I have no idea what the result of to_date(‘Monday’,
‘Day’) should be. “Any date which is a Monday” seems too vague. “The
nearest Monday”, “the previous Monday”, “the next Monday” might be
useful in practice, but whichever of them you pick, you've picked the
wrong one with a probability of 2/3. “The first monday in the year -1 of
the proleptic Gregorian calendar” would be consistent with how
to_timestamp('12:34:56', 'HH24:MI:SS') works, but apart from that and
being invertible it seems to be a quite useless choice.

hp

--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp(at)hjp(dot)at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html