On 03/30/2017 08:21 AM, Shaun Cutts wrote:
On Mar 30, 2017, at 10:02 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
