BUG #1563: wrong week returnded by date_trunc('week', ...)

From: "Dirk Raetzel" <d00273(at)spaetzle(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1563: wrong week returnded by date_trunc('week', ...)
Date: 2005-03-25 08:28:58
Message-ID: 20050325082858.398EEF109A@svr2.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-patches


The following bug has been logged online:

Bug reference: 1563
Logged by: Dirk Raetzel
Email address: d00273(at)spaetzle(dot)de
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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Simon Riggs 2005-03-25 10:18:37 Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Previous Message Stefan Taube 2005-03-25 07:47:12 BUG #1562: OdbcDataAdapter.Update Fails

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2005-03-25 16:09:30 Re: [BUGS] CC Date format code defaults to current centry
Previous Message Tom Lane 2005-03-25 07:48:27 Re: WIP: make EXPLAIN ANALYZE show time spent in triggers