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
