Re: [BUGS] BUG #1563: wrong week returnded by date_trunc('week', - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: [BUGS] BUG #1563: wrong week returnded by date_trunc('week', |
Date | |
Msg-id | 200503300606.j2U66K914935@candle.pha.pa.us Whole thread Raw |
In response to | Re: [BUGS] BUG #1563: wrong week returnded by date_trunc('week', (Robert Creager <Robert_Creager@LogicalChaos.org>) |
List | pgsql-patches |
Robert Creager wrote: -- Start of PGP signed section. > When grilled further on (Tue, 29 Mar 2005 07:43:53 +0200), > Dirk Raetzel <d00273@spaetzle.de> confessed: > > > > > > > On Mon, 28 Mar 2005, Bruce Momjian wrote: > > > > > I have developed a patch to fix the problem. Instead of changing the > > > API, I added code to decrement the year when the week number was 53 and > > > the month was January. It corrected the problem: > > > > The problem arises as well when week number is 52 as in '2006-01-01'. > > Try these at the least: > > select date_trunc_week( '2004-01-01' ); -- should be 2003-12-29 00:00:00 > select date_trunc_week( '2005-01-01' ); -- should be 2004-12-27 00:00:00 > select date_trunc_week( '2005-06-01' ); -- should be 2005-05-30 00:00:00 > select date_trunc_week( '2006-01-01' ); -- should be 2005-12-26 00:00:00 > select date_trunc_week( '2007-01-01' ); -- should be 2007-01-01 00:00:00 > > If it helps, look earlier in this thread for a (overly complicated?) version in > plpgsql. Wow, an early January date can be part of the 52nd week of the previous year too. Here is a new patch, with a documentation mention. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.242 diff -c -c -r1.242 func.sgml *** doc/src/sgml/func.sgml 25 Mar 2005 16:08:39 -0000 1.242 --- doc/src/sgml/func.sgml 30 Mar 2005 06:04:39 -0000 *************** *** 5472,5477 **** --- 5472,5483 ---- week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for <type>timestamp</type> values only) </para> + <para> + Because of this, it is possible for early January dates to be part of the + 52nd or 53rd week of the previous year. For example, <literal>2005-01-01</> + is part of the 53rd week of year 2004, and <literal>2006-01-01</> is part of + the 52nd week of year 2005. + </para> <screen> SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Index: src/backend/utils/adt/timestamp.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.117 diff -c -c -r1.117 timestamp.c *** src/backend/utils/adt/timestamp.c 31 Dec 2004 22:01:22 -0000 1.117 --- src/backend/utils/adt/timestamp.c 30 Mar 2005 06:04:41 -0000 *************** *** 2754,2765 **** switch (val) { case DTK_WEEK: ! isoweek2date(date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday)); tm->tm_hour = 0; tm->tm_min = 0; tm->tm_sec = 0; fsec = 0; break; case DTK_MILLENNIUM: /* see comments in timestamptz_trunc */ if (tm->tm_year > 0) --- 2754,2776 ---- switch (val) { case DTK_WEEK: ! { ! int woy; ! ! woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); ! /* ! * If it is week 52/53 and the month is January, ! * then the week must belong to the previous year. ! */ ! if (woy >= 52 && tm->tm_mon == 1) ! --tm->tm_year; ! isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday)); tm->tm_hour = 0; tm->tm_min = 0; tm->tm_sec = 0; fsec = 0; break; + } case DTK_MILLENNIUM: /* see comments in timestamptz_trunc */ if (tm->tm_year > 0) *************** *** 2874,2886 **** switch (val) { case DTK_WEEK: ! isoweek2date(date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday)); tm->tm_hour = 0; tm->tm_min = 0; tm->tm_sec = 0; fsec = 0; redotz = true; break; /* one may consider DTK_THOUSAND and DTK_HUNDRED... */ case DTK_MILLENNIUM: --- 2885,2908 ---- switch (val) { case DTK_WEEK: ! { ! int woy; ! ! woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); ! /* ! * If it is week 52/53 and the month is January, ! * then the week must belong to the previous year. ! */ ! if (woy >= 52 && tm->tm_mon == 1) ! --tm->tm_year; ! isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday)); tm->tm_hour = 0; tm->tm_min = 0; tm->tm_sec = 0; fsec = 0; redotz = true; break; + } /* one may consider DTK_THOUSAND and DTK_HUNDRED... */ case DTK_MILLENNIUM: *************** *** 3142,3148 **** * Sometimes the last few days in a year will fall into the first week * of the next year, so check for this. */ ! if (result >= 53) { day4 = date2j(year + 1, 1, 4); --- 3164,3170 ---- * Sometimes the last few days in a year will fall into the first week * of the next year, so check for this. */ ! if (result >= 52) { day4 = date2j(year + 1, 1, 4); *************** *** 3198,3204 **** * Sometimes the last few days in a year will fall into the first week * of the next year, so check for this. */ ! if (result >= 53) { day4 = date2j(year + 1, 1, 4); --- 3220,3226 ---- * Sometimes the last few days in a year will fall into the first week * of the next year, so check for this. */ ! if (result >= 52) { day4 = date2j(year + 1, 1, 4);
pgsql-patches by date: