The following bug has been logged online:

Bug reference: 1768
Logged by:
Email address: michael.oeztuerk@haufe.de
PostgreSQL version: 8.0.3
Operating system: Linux / Debian (Version 3.0)
Description: to_char result of an interval differs between 7.x and
8.x
Details:

When using the following SQL statement the result of a 8.0.3 seems to be
wrong.

Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
Result of a 8.0.3: "00010000 15:02:12"
The error in the Result is that it´s "one year behind".

The same statement given to a 7.3.4 delivers the correct result: "00000000
15:02:12"

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, Jul 14, 2005 at 08:01:11AM +0100, michael.oeztuerk@haufe.de wrote:

> When using the following SQL statement the result of a 8.0.3 seems to be
> wrong.
>
> Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
> Result of a 8.0.3: "00010000 15:02:12"
> The error in the Result is that it´s "one year behind".

Yeah, it's strange:

alvherre=# select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
to_char
---------------------
0001-00-00 15:02:12
(1 fila)

alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
to_char
---------------------
0101-00-00 15:02:12
(1 fila)

alvherre=# select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-7)
(1 fila)


On 7.4 however the year stays at 0, but centuries seem wrong too:

alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
to_char
---------------------
0100-00-00 15:02:12
(1 row)

alvherre=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 row)


--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"The problem with the future is that it keeps turning into the present"
(Hobbes)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera wrote:
> On Thu, Jul 14, 2005 at 08:01:11AM +0100, michael.oeztuerk@haufe.de wrote:
>
> > When using the following SQL statement the result of a 8.0.3 seems to be
> > wrong.
> >
> > Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
> > Result of a 8.0.3: "00010000 15:02:12"
> > The error in the Result is that it??s "one year behind".
>
> Yeah, it's strange:

Wow, the to_char(interval) code was worse than I thought. I just
committed these changes:

o Fix to_char(interval) to return proper year and century values.
o Fix to_char(interval) to return large year/month/day/hour values that
are larger than possible timestamp values.
o Prevent to_char(interval) format specifications that make no sense,
like Month.
o Clean up formatting.c code to more logically handle return lengths.

I think we agreed that to_char(interval) is fixable and that no better
solution has been proposed, so we are going to have to announce that in
the 8.1 release notes (in 8.0 we said we were going to remove it).

I have implemented this TODO:

* Prevent to_char() on interval from returning meaningless values

For example, to_char('1 month', 'mon') is meaningless. Basically,
most date-related parameters to to_char() are meaningless for
intervals because interval is not anchored to a date.


and it now shows proper return values:

test=> select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
to_char
---------------------
0000-00-00 15:02:12
(1 row)

and

test=> select to_char(interval '-1000000 year -1000 month -9991 day 19999 hour 650 minute', 'YYYY-MM-DD HH:MI:SS');
to_char
-------------------------------
-1000083--4--9991 20009:50:00
(1 row)

Not pretty, but it is accurate.


---------------------------------------------------------------------------


>
> alvherre=# select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
> to_char
> ---------------------
> 0001-00-00 15:02:12
> (1 fila)
>
> alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
> to_char
> ---------------------
> 0101-00-00 15:02:12
> (1 fila)
>
> alvherre=# select version();
> version
> ----------------------------------------------------------------------------------------------
> PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-7)
> (1 fila)
>
>
> On 7.4 however the year stays at 0, but centuries seem wrong too:
>
> alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
> to_char
> ---------------------
> 0100-00-00 15:02:12
> (1 row)
>
> alvherre=# select version();
> version
> --------------------------------------------------------------------------------------------
> PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
> (1 row)
>
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> "The problem with the future is that it keeps turning into the present"
> (Hobbes)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match