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 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 http://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); >
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 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 http://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
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 05:16, Steve Crawford 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/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 04:16, Steve Crawford 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-"W"IW-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 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 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: 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 away? O
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 1 April 2011 03:32, Adrian Klaver 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:27 AM, Brendan Jurd wrote: On 1 April 2011 02:00, Adrian Klaver 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 02:35, Marc Munro 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 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 > > 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:00, Adrian Klaver 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 Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: > On 31 March 2011 03:15, 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: > 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 31 March 2011 03:15, 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: 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
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 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