Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-bugs by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group