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)
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 |
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 |