dbTalk Databases Forums  

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

mailing.database.pgsql-bugs mailing.database.pgsql-bugs


Discuss [BUGS] BUG #1563: wrong week returnded by date_trunc('week', ...) in the mailing.database.pgsql-bugs forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Dirk Raetzel
 
Posts: n/a

Default [BUGS] BUG #1563: wrong week returnded by date_trunc('week', ...) - 03-26-2005 , 11:36 PM







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)

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

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.