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

On 03/29/2017 06:19 PM, 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

The two examples are not the same. In the second you starting from a
known date and extracting a day number. In the first you are asking for
a day of the week that is not anchored to a date, so any date past or
present that is on that day would fit. I have no problem with
normalizing that to a placeholder date.

>
> ….
>
> 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?

What would the requirement be?:

That Day dates w/o a year would be sequential from 0001-01-01 BC?

Or some other Sunday in some other year?

It comes down to what you want "select to_date(‘Monday’, ‘Day’)" to
provide you and for what purpose?

>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com