Thread

1. Bug in CAST() with time data types

   Alvaro Herrera <alvherre@protecne.cl> — 2000-12-15T13:58:06Z

   Your name		:	Alvaro Herrera
   Your email address	:	alvherre@protecne.cl
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)  	: AMD Athlon
   
     Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.2.17
   
     PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2
   
     Compiler used (example:  gcc 2.8.0)		: RPM for Mandrake Linux
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   
   When inserting timestamps or interval types whose precision is one full
   minute, Postgres substracts one minute from the data and add 60 seconds
   (00:01:00 becomes 00:00:60). Later, when reading this data Postgres says
   "Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and
   refuses to understand it.
   
   I gave a look at the list of fixed bugs for 7.0.3, but it isn't listed.
   
   (sorry, Mandrake hasn't provided upgraded packages yet, and I don't want to
    mess around with the installation).
   
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible:
   ----------------------------------------------------------------------
   
   rwatch=> CREATE TABLE test (val1 TIMESTAMP, val2 INTERVAL);
   CREATE
   rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:00.00-03' AS
   			TIMESTAMP), CAST ('00:01:00' AS INTERVAL));
   INSERT 4400170 1
   rwatch=> SELECT * FROM test;
              val1            |   val2
   ---------------------------+----------
    2000-12-14 18:25:60.00-03 | 00:00:60
   	    (1 row)
   
   (cutting and pasting the text from the select above):
   
   rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:60.00-03' AS
   			TIMESTAMP));
   ERROR:  Bad timestamp external representation '2000-12-14 18:25:60.00-03'
   
   
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   Sorry, I don't even know where to look. It is probably a bug in the way
   CAST() treats it's arguments, when dealing with time data types.
   
   
   -- 
   Alvaro Herrera (<alvherre[@]protecne.cl>)
   
   
   

2. DON'T APPROVE PREVIOUS Re: Bug in CAST() with time data types

   Alvaro Herrera <alvherre@protecne.cl> — 2000-12-15T14:29:01Z

   On Fri, 15 Dec 2000, Alvaro Herrera wrote:
   
   > Your name		:	Alvaro Herrera
   > Your email address	:	alvherre@protecne.cl
   [...]
   
   Moderator: Sorry, I tried 7.0.3 and the bug is fixed. Please don't
   approve the previous post.
   
   (Anyway, there's a bug in the FTP site, but I don't know who to talk to
   -- do I report it here? It has to do with /pub/latest being a directory
   which contains a symlink called "latest" which in turn points to an
   inexistant entry. I will appreciate any human feedback on this)
   
   -- 
   Alvaro Herrera (<alvherre[@]protecne.cl>)
   
   
   

3. Re: Bug in CAST() with time data types

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-12-16T05:30:12Z

   >   Compiler used (example:  gcc 2.8.0)           : RPM for Mandrake Linux
   ...
   > When inserting timestamps or interval types whose precision is one full
   > minute, Postgres substracts one minute from the data and add 60 seconds
   > (00:01:00 becomes 00:00:60). Later, when reading this data Postgres says
   > "Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and
   > refuses to understand it.
   ...
   > Sorry, I don't even know where to look. It is probably a bug in the way
   > CAST() treats it's arguments, when dealing with time data types.
   
   No, unfortunately it is a bug in the way Mandrake builds their RPMs :(
   
   The default compiler flags for RPM building under Mandrake include both
   "-O3" (shrunk to "-O2" explicitly by the PostgreSQL RPM build) and
   "-ffast-math". The GNU gcc folks strongly recommend against mixing these
   two flags!
   
   afaict the *only* symptom of this misuse of the gcc compiler for
   PostgreSQL is the symptom that you see, with incorrect rounding in the
   date/time types.
   
   I've posted a corrected .rpmrc file on ftp.postgresql.org in the
   /pub/binary area if you want to repair this by rebuilding this from
   .src.rpm. And if you know a way to get in touch with the Mandrake RPM
   builders that would be helpful; I have not had the time to subscribe to
   the cooker mailing list to try to work out this issue.
   
   In the meantime pick up RPMs from the PostgreSQL ftp site, which have
   this problem corrected by building against the above-mentioned .rpmrc
   file.
   
   I've cross-posted this to the RPM contact address for Mandrake (hi!) and
   would be happy to provide more information.
   
                           - Thomas