Re: [HACKERS] [GENERAL] Date conversion using day of week
Patch applied.
---
On Sat, Sep 1, 2012 at 05:14:39PM -0400, Bruce Momjian wrote:
[Properly posted to hackers list]
On Fri, Apr 1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
On 1 April 2011 02:00, Adrian Klaver adrian.kla...@gmail.com wrote:
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Just to play Devils advocate here, but why not? The day name is the same
either
way, it is the index that changes. I am not sure why that could not be
context
specific?
To be perfectly honest, it's mostly because I was hoping not to spend
very much more of my time in formatting.c. Every time I go in there I
come out a little bit less sane. I'm concerned that if I do anything
---
Agreed!
further to it, I might inadvertently summon Chattur'gha or something.
But since you went to the trouble of calling me on my laziness, let's
take a look at the problem.
At the time when the day-of-week token gets converted into a numeric
value and put into the TmFromChar.d field, the code has no knowledge
of whether the overall pattern is Gregorian or ISO (the DY field could
well be at the front of the pattern, for example).
Later on, in do_to_timestamp, the code expects the 'd' value to make
sense given the mode (it should be zero-based on Sunday for Gregorian,
or one-based on Monday for ISO). That's all well and good *except* in
the totally bizarre case raised by the OP.
To resolve it, we could make TmFromChar.d always stored using the ISO
convention (because zero then has the useful property of meaning not
set) and converted to the Gregorian convention as necessary in
do_to_timestamp.
I did quite a bit if study on this and have a fix in the attached patch.
Brendan above is correct about the cause of the problems. Basically,
'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
other times 0-6 with Sunday as start. Plus, zero was used to designate
not supplied in ISO tests. Obviously the number and the start value
both caused problems.
The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
throughout, allowing any mix of Gregorian and ISO week designations. It
is converted to ISO (or Unix format 0-6, Sunday=0) as needed.
Sample output:
test= select to_date('2011-13-MON', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
test= select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-04-03
(1 row)
test= select to_date('2011-13-SAT', 'IYYY-IW-DY');
to_date
2011-04-02
(1 row)
test= select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
(1 row)
test= select to_date('2011-13-7', 'IYYY-IW-ID');
to_date
2011-04-03
(1 row)
test= select to_date('2011-13-0', 'IYYY-IW-ID');
to_date
2011-04-03
(1 row)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c
b/src/backend/utils/adt/formatting.c
new file mode 100644
index 25af8a2..2aa6df1
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** typedef struct
*** 412,418
mi,
ss,
,
! d,
dd,
ddd,
mm,
--- 412,418
mi,
ss,
,
! d, /* stored as
1-7, Sunday = 1, 0 means missing */
dd,
ddd,
mm,
*** DCH_from_char(FormatNode *node, char *in
*** 2897,2902
--- 2897,2903
from_char_seq_search(value, s, days,
ONE_UPPER,
MAX_DAY_LEN, n);
from_char_set_int(out-d, value, n);
+ out-d++;
break;
Re: [HACKERS] [GENERAL] Date conversion using day of week
[Properly posted to hackers list]
On Fri, Apr 1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
On 1 April 2011 02:00, Adrian Klaver adrian.kla...@gmail.com wrote:
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Just to play Devils advocate here, but why not? The day name is the same
either
way, it is the index that changes. I am not sure why that could not be
context
specific?
To be perfectly honest, it's mostly because I was hoping not to spend
very much more of my time in formatting.c. Every time I go in there I
come out a little bit less sane. I'm concerned that if I do anything
---
Agreed!
further to it, I might inadvertently summon Chattur'gha or something.
But since you went to the trouble of calling me on my laziness, let's
take a look at the problem.
At the time when the day-of-week token gets converted into a numeric
value and put into the TmFromChar.d field, the code has no knowledge
of whether the overall pattern is Gregorian or ISO (the DY field could
well be at the front of the pattern, for example).
Later on, in do_to_timestamp, the code expects the 'd' value to make
sense given the mode (it should be zero-based on Sunday for Gregorian,
or one-based on Monday for ISO). That's all well and good *except* in
the totally bizarre case raised by the OP.
To resolve it, we could make TmFromChar.d always stored using the ISO
convention (because zero then has the useful property of meaning not
set) and converted to the Gregorian convention as necessary in
do_to_timestamp.
I did quite a bit if study on this and have a fix in the attached patch.
Brendan above is correct about the cause of the problems. Basically,
'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
other times 0-6 with Sunday as start. Plus, zero was used to designate
not supplied in ISO tests. Obviously the number and the start value
both caused problems.
The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
throughout, allowing any mix of Gregorian and ISO week designations. It
is converted to ISO (or Unix format 0-6, Sunday=0) as needed.
Sample output:
test= select to_date('2011-13-MON', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
test= select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-04-03
(1 row)
test= select to_date('2011-13-SAT', 'IYYY-IW-DY');
to_date
2011-04-02
(1 row)
test= select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
(1 row)
test= select to_date('2011-13-7', 'IYYY-IW-ID');
to_date
2011-04-03
(1 row)
test= select to_date('2011-13-0', 'IYYY-IW-ID');
to_date
2011-04-03
(1 row)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 25af8a2..2aa6df1
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** typedef struct
*** 412,418
mi,
ss,
,
! d,
dd,
ddd,
mm,
--- 412,418
mi,
ss,
,
! d,/* stored as 1-7, Sunday = 1, 0 means missing */
dd,
ddd,
mm,
*** DCH_from_char(FormatNode *node, char *in
*** 2897,2902
--- 2897,2903
from_char_seq_search(value, s, days, ONE_UPPER,
MAX_DAY_LEN, n);
from_char_set_int(out-d, value, n);
+ out-d++;
break;
case DCH_DY:
case DCH_Dy:
*** DCH_from_char(FormatNode *node, char *in
*** 2904,2909
--- 2905,2911
from_char_seq_search(value, s, days, ONE_UPPER,
MAX_DY_LEN, n);
from_char_set_int(out-d, value, n);
+ out-d++;
break;
case DCH_DDD:
from_char_parse_int(out-ddd, s, n);
*** DCH_from_char(FormatNode *node, char *in
*** 2919,2929
break;
case DCH_D:
from_char_parse_int(out-d, s, n);
- out-d--;
s += SKIP_THth(n-suffix);
break;
case DCH_ID:
from_char_parse_int_len(out-d, s, 1, n);
s += SKIP_THth(n-suffix);
break;
case DCH_WW:
--- 2921,2933
break;
case DCH_D:
from_char_parse_int(out-d, s, n);
s += SKIP_THth(n-suffix);
break;
case DCH_ID:
Re: [HACKERS] [GENERAL] Date conversion using day of week
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
On 31 March 2011 03:15, Steve Crawford scrawf...@pinpointresearch.com wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
The relevant paragraphs in the docs are:
--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a month or day of month has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
With the attached patch applied, this is what happens instead:
# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Just to play Devils advocate here, but why not? The day name is the same either
way, it is the index that changes. I am not sure why that could not be context
specific?
Cheers,
BJ
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 02:00, Adrian Klaver adrian.kla...@gmail.com wrote: On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: If we wanted to make it work, then I think the thing to do would be to add a new set of formatting tokens IDY, IDAY etc. I don't like the idea of interpreting DY and co. differently depending on whether the other tokens happen to be ISO week or Gregorian. Just to play Devils advocate here, but why not? The day name is the same either way, it is the index that changes. I am not sure why that could not be context specific? To be perfectly honest, it's mostly because I was hoping not to spend very much more of my time in formatting.c. Every time I go in there I come out a little bit less sane. I'm concerned that if I do anything further to it, I might inadvertently summon Chattur'gha or something. But since you went to the trouble of calling me on my laziness, let's take a look at the problem. At the time when the day-of-week token gets converted into a numeric value and put into the TmFromChar.d field, the code has no knowledge of whether the overall pattern is Gregorian or ISO (the DY field could well be at the front of the pattern, for example). Later on, in do_to_timestamp, the code expects the 'd' value to make sense given the mode (it should be zero-based on Sunday for Gregorian, or one-based on Monday for ISO). That's all well and good *except* in the totally bizarre case raised by the OP. To resolve it, we could make TmFromChar.d always stored using the ISO convention (because zero then has the useful property of meaning not set) and converted to the Gregorian convention as necessary in do_to_timestamp. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On Thu, 2011-03-31 at 08:00 -0700, Adrian Klaver wrote:
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
On 31 March 2011 03:15, Steve Crawford scrawf...@pinpointresearch.com
wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
[. . .]
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
[. . .]
Just to play Devils advocate here, but why not? The day name is the same
either
way, it is the index that changes. I am not sure why that could not be
context
specific?
Just to be clear, the reason I was mixing things in this way was that I
wanted to validate that the dayname being passed was valid for the
current locale, and I could find no easier way of doing it. FTR, I have
now resorted to finding the given dayname in the results of this query:
select day, to_char(day, 'dy') as dayname,
extract('dow' from day) as dayno
from (
select current_date + n as day
from generate_series(0, 6) as n) d;
If there is an easier way of doing this, please let me know. As far as
the postgres API goes, exposing a function that would validate a dayname
returning a day number would resolve all of this for considerably less
complexity. Also throwing an error in the to_date function for
unexpectedly mixed input formats seems quite reasonable.
Thanks for your time and attention. The commercial RDBMS vendors could
learn a lot about customer support from this forum.
__
Marc Munro
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 02:35, Marc Munro m...@bloodnok.com wrote: Just to be clear, the reason I was mixing things in this way was that I wanted to validate that the dayname being passed was valid for the current locale, and I could find no easier way of doing it. Ah, I see. In that case I think to_date would have disappointed you even if IYYY-IW-DY did work, since the inputs do not appear to be checked against the localised versions of the day names. They are only checked against the hard-coded English names. to_date and to_char are asymmetric in this sense -- localisation only happens on the way out. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/31/2011 08:27 AM, Brendan Jurd wrote: On 1 April 2011 02:00, Adrian Klaveradrian.kla...@gmail.com wrote: On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: If we wanted to make it work, then I think the thing to do would be to add a new set of formatting tokens IDY, IDAY etc. I don't like the idea of interpreting DY and co. differently depending on whether the other tokens happen to be ISO week or Gregorian. Just to play Devils advocate here, but why not? The day name is the same either way, it is the index that changes. I am not sure why that could not be context specific? To be perfectly honest, it's mostly because I was hoping not to spend very much more of my time in formatting.c. Every time I go in there I come out a little bit less sane. I'm concerned that if I do anything further to it, I might inadvertently summon Chattur'gha or something. But since you went to the trouble of calling me on my laziness, let's take a look at the problem. I understand, my foray into formatting.c has left an impression. At the time when the day-of-week token gets converted into a numeric value and put into the TmFromChar.d field, the code has no knowledge of whether the overall pattern is Gregorian or ISO (the DY field could well be at the front of the pattern, for example). Later on, in do_to_timestamp, the code expects the 'd' value to make sense given the mode (it should be zero-based on Sunday for Gregorian, or one-based on Monday for ISO). That's all well and good *except* in the totally bizarre case raised by the OP. Now I am confused the docs say: D day of the week, Sunday(1) to Saturday(7) ID ISO day of the week, Monday(1) to Sunday(7) This would seem to say they both are one-based but differ on the day that is 1. To resolve it, we could make TmFromChar.d always stored using the ISO convention (because zero then has the useful property of meaning not set) and converted to the Gregorian convention as necessary in do_to_timestamp. Since I am in this deep might as well go deeper. When I see the requirement: IYYY-IW-IDY(proposed) or YYY-WW-DY which is implied GYYY-GWW-GDY I see the constant being pulled out: I YYY-W-DY G YYY-W-DY I know this presents backwards compatibility issues. Also that the data formatting functions are supposed to track Oracle behavior. It just seems a way to simplify the formatting process. Thanks for taking the time to explain the process. Cheers, BJ -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 03:32, Adrian Klaver adrian.kla...@gmail.com wrote: Now I am confused the docs say: D day of the week, Sunday(1) to Saturday(7) ID ISO day of the week, Monday(1) to Sunday(7) This would seem to say they both are one-based but differ on the day that is 1. That's correct for the user-facing interpretation. Internally, however, Gregorian day-of-week is represented with Sunday = 0. I can't see any good reason in the code for why that should be so, but it was like that when I found it, and until now I haven't had any cause to mess with it. My suggestion for moving forward basically still stands, though. We'd need to standardise the use of TmFromChar.d to either one of the 1-based conventions, and convert to the other one as required in do_to_timestamp. The Gregorian convention is probably the right choice for the standard, even though it has the week starting on a Sunday (ridiculous!) because it means less converting for the majority of cases. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/31/2011 08:00 AM, Adrian Klaver wrote:
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
On 31 March 2011 03:15, Steve Crawfordscrawf...@pinpointresearch.com wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
The relevant paragraphs in the docs are:
--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a month or day of month has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
With the attached patch applied, this is what happens instead:
# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Just to play Devils advocate here, but why not? The day name is the same either
way, it is the index that changes. I am not sure why that could not be context
specific?
A week day represented as an int is ambiguous - as you mention, the
index is necessary to decode to the correct day. Sunday is unambiguous
so we could do something reasonable. But from everything I've read
(though I didn't actually shell out 130CHF for a full 33-page copy of
ISO8601:2004), the ISO *week* date format does not represent day-of-week
as other than a numeric value so it would not really be an ISO8601
formatted date and I would be tempted to thrown an error. However...
This whole discussion opens a #10 sized can o' worms. Admittedly, I
don't have good knowledge of any SQL-mandated interpretations of an ISO
date - but based on my reading of ISO formatting I see the following issues:
1. What we describe in the documentation as an ISO date is actually an
ISO *week* date - a special purpose format included within ISO8601.
2011-03-31 is also an ISO date as are 20110331, 20110331T013212 and
20110331T21.3344298. Fixing this is probably as simple as a
clarification in the documentation.
2. The ISO week-date format is defined as having the week-number
prefaced by a W as in 2011-W03-7. From the ISO8601 FAQ page: Week
date is an alternative date representation used in many commercial and
industrial applications. It is: -Www-D where is the Year in the
Gregorian calendar, ww is the week of the year between 01 (the first
week) and 52 or 53 (the last week), and D is the day in the week between
1 (Monday) and 7 (Sunday). Example: 2003-W14-2 represents the second day
of the fourteenth week of 2003. However PostgreSQL does *not* accept
that as input even as specified as an ISO date:
select to_date('2003-W14-2', 'IYYY-IW-ID');
ERROR: invalid value W1 for IW
DETAIL: Value must be an integer.
Fixing this would require both a coding change and a decision whether or
not to throw an error on incorrectly formatted input.
3. ISO8601 requires zero-padding. PostgreSQL, however, does not complain
if that padding is missing. The following should be 2011-04-2
(actually, 2011-W04-2 as noted above) but PostgreSQL accepts:
select to_date('2011-4-2', 'IYYY-IW-ID');
to_date
2011-01-25
However in ISO dates the hyphens are supposed to only be for easier
reading by humans. But if we just remove them:
select to_date('201142', 'IYYYIWID');
to_date
2011-10-17
(Monday of the 42nd week).
Fix it and throw an error (and suffer the howls of anguish when backward
compatibility is shattered) or tiptoe quietly
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 04:16, Steve Crawford scrawf...@pinpointresearch.com wrote: This whole discussion opens a #10 sized can o' worms. Admittedly, I don't have good knowledge of any SQL-mandated interpretations of an ISO date - but based on my reading of ISO formatting I see the following issues: 1. What we describe in the documentation as an ISO date is actually an ISO *week* date - a special purpose format included within ISO8601. 2011-03-31 is also an ISO date as are 20110331, 20110331T013212 and 20110331T21.3344298. Fixing this is probably as simple as a clarification in the documentation. In the docs paragraph I quoted upthread, the full name ISO week date is given. Elsewhere the shorthand ISO or ISO date is used, in contrast to the ordinary Gregorian style. This is the only sense in which we refer to ISO in the context of to_date, but I have no real objection to expanding this to the full name ISO week date everywhere it is mentioned, if people find the current usage ambiguous. 2. The ISO week-date format is defined as having the week-number prefaced by a W as in 2011-W03-7. ... However PostgreSQL does *not* accept that as input even as specified as an ISO date: It does, but you must use the somewhat awkward quoting notation to indicate that the W is a literal character in the input string, not a formatting character: 'IYYY-WIW-ID' ... What I've concluded is that the root of the entire problem is providing ISO formatting options in pieces at all. The ISO date format has various requirements like ordering from largest temporal term to smallest, zero-padding, W prefacing an ISO week, no skipping of temporal terms (201105 is May 2011, never the 5th of an unknown month), etc. all intended to make an ISO date string unambiguous. As such, it should only require a single format option saying this is an ISO8601 date string and mixing of ISO and Gregorian date formatting becomes impossible. I agree with your summary of the ISO standards. Unfortunately, to_date and its cohorts are not targeting ISO. They are targeting quasi-compatibility with some Oracle functions of the same name, I suppose to make life easier for folks who are migrating from Oracle to Postgres. Any proposed reform of these (admittedly weird and kludgy) functions is viewed through that lens, and usually rejected on those grounds. I've been down that road before. There's not much point having compatibility functions if they aren't, well, compatible. In the big picture, to_date isn't meant to be the general entry point for parsing dates. If you wanted to make ISO8601 work as a syntax for inputting date type literals vis. SELECT date '2011-W14-01', you might have a better shot at getting that off the ground. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/31/2011 10:51 AM, Brendan Jurd wrote:
I agree with your summary of the ISO standards. Unfortunately,
to_date and its cohorts are not targeting ISO. They are targeting
quasi-compatibility with some Oracle functions of the same name, I
suppose to make life easier for folks who are migrating from Oracle to
Postgres. Any proposed reform of these (admittedly weird and kludgy)
functions is viewed through that lens, and usually rejected on those
grounds. I've been down that road before. There's not much point
having compatibility functions if they aren't, well, compatible.
In the big picture, to_date isn't meant to be the general entry point
for parsing dates. If you wanted to make ISO8601 work as a syntax for
inputting date type literals vis. SELECT date '2011-W14-01', you might
have a better shot at getting that off the ground.
Well, to return to the original issue, should we allow the day to be
spelled out and fix it (as noted in this thread it is non-standard but
also unambiguous and we already allow plenty of non-standard formats) or
throw an error? For me personally, either would be fine. What isn't
correct is the current behavior:
select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
Cheers,
Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 05:16, Steve Crawford scrawf...@pinpointresearch.com wrote: Well, to return to the original issue, should we allow the day to be spelled out and fix it (as noted in this thread it is non-standard but also unambiguous and we already allow plenty of non-standard formats) or throw an error? For me personally, either would be fine. What isn't correct is the current behavior: I started out thinking we should throw the error, but I am coming around to the idea of fixing it. I outlined how that might work in reply to Adrian Klaver elsewhere in the thread [1]. Cheers, BJ [1] http://archives.postgresql.org/pgsql-hackers/2011-03/msg01906.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
So does:
steve=# select to_date('2011-13-0', 'IYYY-IW-ID');
to_date
2011-03-28
So something isn't right. All sorts of other stuff is allowed as well -
I don't know if that's by design or not:
steve=# select to_date('2011-13--23', 'IYYY-IW-ID');
to_date
2011-03-04
steve=# select to_date('2011-13-56', 'IYYY-IW-ID');
to_date
2011-05-22
Agreed, maintaining ISO arguments across the board is the way to go:
Monday
select to_date('2011-13-1', 'IYYY-IW-ID');...
We have to distinguish Gregorian and ISO days when represented as an
integer since they define the start-of-week differently. Same with year.
I don't think I've ever seen and ISO-week-date written as 2011-13-SUN
but it *does* define a distinct date (which is not Monday). And even if
PostgreSQL were updated to throw an error on that mix of formats it
still leaves the problem of ISO day-of-week equal to zero.
Cheers,
Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/30/2011 09:15 AM, Steve Crawford wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
I started going through the source (formatting.c,timestamp.c), got as
far as the Julian date functions before the brain imploded and I had to
take a break:) I would agree it has to do with the difference in the
week rotating around either Sunday or Monday.
select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
So does:
steve=# select to_date('2011-13-0', 'IYYY-IW-ID');
to_date
2011-03-28
So something isn't right. All sorts of other stuff is allowed as well -
I don't know if that's by design or not:
Well I can see how this is possible and indeed likely. The permutations
of all the possible date/time representations is immense. It just
emphasizes that when dealing with time consistency is good.
steve=# select to_date('2011-13--23', 'IYYY-IW-ID');
to_date
2011-03-04
steve=# select to_date('2011-13-56', 'IYYY-IW-ID');
to_date
2011-05-22
Cheers,
Steve
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 31 March 2011 03:15, Steve Crawford scrawf...@pinpointresearch.com wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I would
categorize that as a bug. (Hackers cc'd). Since Sunday is the last day of an
ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
The relevant paragraphs in the docs are:
--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a month or day of month has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
With the attached patch applied, this is what happens instead:
# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Cheers,
BJ
diff --git a/src/backend/utils/adt/formatting.c
b/src/backend/utils/adt/formatting.c
index 45e36f9..5ad6437 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -720,12 +720,12 @@ static const KeyWord DCH_keywords[] = {
{B.C., 4, DCH_B_C, FALSE, FROM_CHAR_DATE_NONE}, /* B */
{BC, 2, DCH_BC, FALSE, FROM_CHAR_DATE_NONE},
{CC, 2, DCH_CC, TRUE, FROM_CHAR_DATE_NONE}, /* C */
- {DAY, 3, DCH_DAY, FALSE, FROM_CHAR_DATE_NONE},/* D */
+ {DAY, 3, DCH_DAY, FALSE, FROM_CHAR_DATE_GREGORIAN},/* D */
{DDD, 3, DCH_DDD, TRUE, FROM_CHAR_DATE_GREGORIAN},
{DD, 2, DCH_DD, TRUE, FROM_CHAR_DATE_GREGORIAN},
- {DY, 2, DCH_DY, FALSE, FROM_CHAR_DATE_NONE},
- {Day, 3, DCH_Day, FALSE, FROM_CHAR_DATE_NONE},
- {Dy, 2, DCH_Dy, FALSE, FROM_CHAR_DATE_NONE},
+ {DY, 2, DCH_DY, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ {Day, 3, DCH_Day, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ {Dy, 2, DCH_Dy, FALSE, FROM_CHAR_DATE_GREGORIAN},
{D, 1, DCH_D, TRUE, FROM_CHAR_DATE_GREGORIAN},
{FX, 2, DCH_FX, FALSE, FROM_CHAR_DATE_NONE}, /* F */
{HH24, 4, DCH_HH24, TRUE, FROM_CHAR_DATE_NONE}, /* H */
@@ -768,10 +768,10 @@ static const KeyWord DCH_keywords[] = {
{b.c., 4, DCH_b_c, FALSE, FROM_CHAR_DATE_NONE}, /* b */
{bc, 2, DCH_bc, FALSE, FROM_CHAR_DATE_NONE},
{cc, 2, DCH_CC, TRUE, FROM_CHAR_DATE_NONE}, /* c */
- {day, 3, DCH_day, FALSE, FROM_CHAR_DATE_NONE},/* d */
+ {day, 3, DCH_day, FALSE, FROM_CHAR_DATE_GREGORIAN},/* d */
{ddd, 3, DCH_DDD, TRUE, FROM_CHAR_DATE_GREGORIAN},
{dd, 2, DCH_DD, TRUE, FROM_CHAR_DATE_GREGORIAN},
- {dy, 2, DCH_dy, FALSE, FROM_CHAR_DATE_NONE},
+ {dy, 2, DCH_dy, FALSE, FROM_CHAR_DATE_GREGORIAN},
{d, 1, DCH_D, TRUE, FROM_CHAR_DATE_GREGORIAN},
{fx, 2, DCH_FX, FALSE, FROM_CHAR_DATE_NONE}, /* f */
{hh24, 4, DCH_HH24, TRUE, FROM_CHAR_DATE_NONE}, /* h */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
