Обсуждение: inevitability of to_date() when converting representations whichdon't represent whole timestamps
When being asked to convert a day of the week, the to_date() function returns the same day ('0001-01-01 BC’) no matter
whichday 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
reasonwhy this isn’t possible?
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
matterwhich 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
reasonwhy 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
Re: inevitability of to_date() when convertingrepresentations which don't represent whole timestamps
От
"Peter J. Holzer"
Дата:
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
Вложения
Re: inevitability of to_date() when converting representations whichdon't represent whole timestamps
От
"David G. Johnston"
Дата:
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.
Re: inevitability of to_date() when convertingrepresentations which don't represent whole timestamps
От
"David G. Johnston"
Дата:
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