The following bug has been logged online:

Bug reference: 1768
Logged by:
Email address: michael.oeztuerk (AT) haufe (DOT) 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 (AT) postgresql (DOT) 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 (AT) haufe (DOT) de wrote:

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