The following bug has been logged online:
Bug reference: 1563
Logged by: Dirk Raetzel
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)
Description: wrong week returnded by date_trunc('week', ...)
Details:
date_trunc('week', ...) returns the wrong week for first days in January if
their calendar week belongs to the previous week.
Example:
select date_trunc('week', timestamp '2005-01-01');
gives: 2006-01-02 00:00:00 but 2004-12-27 00:00:00 would be right.
sample code:
create table weektest (
date timestamp
);
copy weektest from STDIN;
'1999-01-01'
'2000-01-01'
'2001-01-01'
'2002-01-01'
'2003-01-01'
'2004-01-01'
'2005-01-01'
'2006-01-01'
'2007-01-01'
'2008-01-01'
'2009-01-01'
\.
date | week_t | week_p
---------------------+---------------------+--------
1999-01-01 00:00:00 | 2000-01-03 00:00:00 | 53
2000-01-01 00:00:00 | 2000-12-25 00:00:00 | 52
2001-01-01 00:00:00 | 2001-01-01 00:00:00 | 1
2002-01-01 00:00:00 | 2001-12-31 00:00:00 | 1
2003-01-01 00:00:00 | 2002-12-30 00:00:00 | 1
2004-01-01 00:00:00 | 2003-12-29 00:00:00 | 1
2005-01-01 00:00:00 | 2006-01-02 00:00:00 | 53
2006-01-01 00:00:00 | 2006-12-25 00:00:00 | 52
2007-01-01 00:00:00 | 2007-01-01 00:00:00 | 1
2008-01-01 00:00:00 | 2007-12-31 00:00:00 | 1
2009-01-01 00:00:00 | 2008-12-29 00:00:00 | 1
(11 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
