Re: Strange results from to_timestamp

Attachment now attached. :-)

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

Bruce Momjian wrote:
>
> Interesting bug report. The problem is that sscanf(buf, "%d", &val)
> eats leading white space, but our functions were not handling that.
>
> I have applied the attached patch that fixes this:
>
> test=> select to_timestamp(' 0300','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0300-01-01 00:00:00-05
> (1 row)
>
> test=> select to_timestamp(' 030004','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0300-04-01 00:00:00-05
> (1 row)
>
> test=> select to_timestamp(' 040004','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0400-04-01 00:00:00-05
> (1 row)
>
> It doesn't throw an error for too mamy format strings, but it does avoid
> the incorrect values. The fix will appear in 8.2.
>
> ---------------------------------------------------------------------------
>
> Mario Weilguni wrote:
> > mydb=# select to_timestamp('00000000 0300','yyyymmdd hh24mi');
> > to_timestamp
> > ---------------------------
> > 0001-01-01 03:00:00+01 BC
> > (1 row)
> >
> > Questionable, but probably valid.
> >
> >
> >
> > mydb=# select to_timestamp(' 0300','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0300-12-25 03:00:00+01
> > (1 row)
> >
> > This puzzles me. Where is the 25th of december coming from?
> >
> >
> >
> > mydb=# select to_timestamp(' 030004','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0382-04-23 03:00:00+01
> > (1 row)
> >
> > Same as above.
> >
> >
> > mydb=# select to_timestamp(' 040004','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0509-10-10 04:00:00+01
> >
> >
> > I think all except the first one should raise a warning, isn't it? Where can I
> > find the source code of this function?
> >
> > Best regards,
> > Mario Weilguni
> >
> >
> > ---------------------------(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
> >
>
> --
> Bruce Momjian http://candle.pha.pa.us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(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
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +