--Signature_Sat__26_Mar_2005_23_00_53_-0700_7zDDe1UB/GueB_3r
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

When grilled further on (Fri, 25 Mar 2005 08:28:58 +0000 (GMT)),
"Dirk Raetzel" <d00273 (AT) spaetzle (DOT) de> confessed:

I brought this up a couple of weeks ago in Hackers since I created this error
last year :-( Never got feedback on whether the following function solved the
problem correctly or not. If you would agree this works, then I'll see about
moving it into C.

CREATE OR REPLACE FUNCTION
date_trunc_week(timestamp without time zone)
RETURNS timestamp without time zone
AS '
DECLARE
reading_time ALIAS FOR $1;
year timestamp;
dow integer;
temp interval;
weeks text;
adjust text;
BEGIN
year := date_trunc( ''year''::text, reading_time );
dow := date_part( ''dow'', year );
IF dow >= 4 THEN
adjust := 1 - dow || '' day'';
ELSIF dow != 1 THEN
adjust := dow - 6 || '' day'';
ELSE
adjust := ''0 day'';
END IF;
temp := reading_time - (year + adjust::interval);
weeks := trunc(date_part( ''days'', temp ) / 7) || '' weeks'';
RETURN year + adjust::interval + weeks::interval;
END;
' LANGUAGE plpgsql;


select date_trunc_week( '2004-01-01' ); -- 2003-12-29 00:00:00
select date_trunc_week( '2005-01-01' ); -- 2004-12-27 00:00:00
select date_trunc_week( '2005-06-01' ); -- 2005-05-30 00:00:00
select date_trunc_week( '2006-01-01' ); -- 2005-12-26 00:00:00
select date_trunc_week( '2007-01-01' ); -- 2007-01-01 00:00:00

--
22:56:20 up 9 days, 2:46, 7 users, load average: 4.72, 5.79, 4.76
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

--Signature_Sat__26_Mar_2005_23_00_53_-0700_7zDDe1UB/GueB_3r
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iEYEARECAAYFAkJGTBUACgkQLQ/DKuwDYzl7UACgiEOM+OHyiObt22ANpVqbKWxu
f14AoI1mEDl7IXxlu/OCR/ukV4YRs1Lw
=8TJm
-----END PGP SIGNATURE-----

--Signature_Sat__26_Mar_2005_23_00_53_-0700_7zDDe1UB/GueB_3r--

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) org> writes:
I don't recall seeing that ... anyway, the problem seems to be that
timestamp_trunc implements this as

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;

which looks plausible on its face ... but given 2005-01-01, date2isoweek
returns 53 --- which represents the 53rd week of 2004, which is correct
--- and then isoweek2date thinks it is supposed to compute the 53rd week
of 2005, which is not what's wanted.

We need to change the function APIs so that date2isoweek passes back
some indication of which year it thought the week belongs to, and then
isoweek2date must use that instead of the original year number.

Each of these functions is used in several places, so the change is not
quite trivial, but still not a big deal. Who wants to fix it?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--Signature_Mon__28_Mar_2005_21_15_43_-0700_EYJ9j_jTODy9YY8v
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

When grilled further on (Sun, 27 Mar 2005 02:26:02 -0500),
Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> confessed:

I'll take a look at it next weekend (4/2 or 4/3) if it's still not done.

Cheers,
Rob

--
20:43:32 up 14:07, 9 users, load average: 3.15, 2.76, 2.00
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

--Signature_Mon__28_Mar_2005_21_15_43_-0700_EYJ9j_jTODy9YY8v
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iEYEARECAAYFAkJI1nsACgkQLQ/DKuwDYzkLVACfcwyidZrDjY4JtWevhfG9SSOv
YVEAn1XunAwjf9lXxivHZceuDugsCBQO
=4VXT
-----END PGP SIGNATURE-----

--Signature_Mon__28_Mar_2005_21_15_43_-0700_EYJ9j_jTODy9YY8v--