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

On 03/30/2017 08:21 AM, Shaun Cutts wrote:
>
>> On Mar 30, 2017, at 10:02 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> 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.
>
> Normalizing to a placeholder date is indeed what I’m after.
>>
>> What would the requirement be?:
>>
>> That Day dates w/o a year would be sequential from 0001-01-01 BC?
>
> Yes — that sounds fine, so:
>
> to_date(‘Sunday’, ‘Day’) -> ‘0001-01-01 BC’::date
> to_date(‘Monday’, ‘Day’) -> ‘0001-01-02 BC’::date
>
> and so on.

I tend to doubt that is going to happen as it would change current
behavior out from under code that depends on it. I was asking more in
the vein of what you where after. See possible solution below.

>
>>
>> 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?
>>
>
> My use case is to convert the name of a day to a day of the week number
> — now testing in English, but ultimately locale insensitive, so relying
> on to_date() to recognize the day in whatever the database locale is.
>

To build on David's suggestion, something like maybe:

WITH day_mapping AS (
SELECT
to_char(week_date,
'Day') AS day_name,
to_char(week_date,
'D') day_number
FROM
generate_series('03/26/2017'::date,
'04/01/2017'::date,
'1 day') AS week_date
)
SELECT
*
FROM
day_mapping
WHERE
trim(day_name) = 'Sunday';

day_name | day_number
-----------+------------
Sunday | 1

This would be for non-ISO numbering. Change 'D' to 'ID' to get ISO day
numbering.

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