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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@hjp.at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html

On Thursday, March 30, 2017, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

I

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”.

What to_date appears to do with the Day specification is throw away the
match just like placeholder text. Left with no other information to
process it then constructs a date - where the day, month, and year are all
missing.

Yes, one could hard-code the word Tuesday to some particular date (absolute
or relative) and deal with the consequent localization issue. But that
isn't in to_dates charter. It doesn't think but simply captures the
necessary date elements from a string into variables and then combines
those values into an actual date. Not all info in the string is useful for
this purpose and so some of it can be discarded.

Maybe the documentation could be improved here but the behavior shown is
not unreasonable.

It would be good to know what Oracel does here since the primary purpose of
to_date is as a compatibility function with that system.

David J.

On Thu, Mar 30, 2017 at 8:21 AM, Shaun Cutts <shauncutts@factfiber.com>
wrote:

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.

​Patches are welcome but I'd suggest that you not attempt to incorporate
this behavior into to_date. That function returns a date and what you want
is a normalized integer (or, in reverse, a locale-specific string).

Both locale-specific so stable, not immutable:
day_of_week(text) : integer
day_of_week(integer) : text

Given that "select to_char(now(), 'Day')" works there should be few, if
any, technical barriers to overcome.

You'd need to decide whether to support only the "ISO 8601" numbering
scheme (1-7) or to add additional arguments and/or function to number
Sunday as 0 instead of 7.

David J.

On 03/30/2017 08:21 AM, Shaun Cutts wrote:

On Mar 30, 2017, at 10:02 AM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general