Thread: BUG #12432: date_trunc returning wrong year and day

The following bug has been logged on the website:

Bug reference:      12432
Logged by:          Brian Quirion
Email address:      bquirion@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Linux
Description:

foodb=> select date_trunc('week', now() - interval '1 week');
       date_trunc
------------------------
 2013-12-30 00:00:00-08
(1 row)

foodb=> select date_trunc('week', now() - interval '2 week');
       date_trunc
------------------------
 2014-12-22 00:00:00-08
(1 row)

I'mn using postgres 8.0.3

bquirion@gmail.com wrote:

> foodb=> select date_trunc('week', now() - interval '1 week');
>        date_trunc
> ------------------------
>  2013-12-30 00:00:00-08
> (1 row)
>
> foodb=> select date_trunc('week', now() - interval '2 week');
>        date_trunc
> ------------------------
>  2014-12-22 00:00:00-08
> (1 row)
>
> I'mn using postgres 8.0.3

8.0.3 was released in 2005.  The latest in the 8.0 branch, 8.0.26 was
released in 2010 containing about 600 commits with bug fixes.  At the
very least you should update to that version and see if it fixes the
problem you have.  In any case you're running a version that has been
out of support for five years now.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

bquirion@gmail.com writes:
> foodb=> select date_trunc('week', now() - interval '1 week');
>        date_trunc
> ------------------------
>  2013-12-30 00:00:00-08
> (1 row)

> foodb=> select date_trunc('week', now() - interval '2 week');
>        date_trunc
> ------------------------
>  2014-12-22 00:00:00-08
> (1 row)

Works for me ...

> I'mn using postgres 8.0.3

A quick look in the commit logs finds an 8.0.4 fix that seems likely
to be the same thing:

Author: Bruce Momjian <bruce@momjian.us>
Branch: REL8_0_STABLE Release: REL8_0_4 [0a056c371] 2005-07-04 14:12:45 +0000

    Fix date_trunct for December dates that are in the next year, e.g.:
        SELECT date_trunc('week', '2002-12-31'::date);
    Backpatch to 8.0.X.
    Per report from Nick Johnson.

You do realize that (1) 8.0.x has been out of support for more than
four years, and (2) the last release in that series was 8.0.26?
You will not get a lot of traction from filing bug reports against
ten-year-old releases.

            regards, tom lane