Re: Problem in converting int to timestamp value - why?

Mike,

thanks for pointing me to the "Template Pattern Modifier" ,-)) I've just
found it in the doc with your help and it works now perfectly.

Thanks,
Soeren

> On Sun, Sep 19, 2004 at 04:17:52PM +0200, Soeren Gerlach wrote:
>
> > I've an integer column in a certain table that I need to convert into a
> > timestamp value to finally get a day difference to the current date.
> > >From the manual it looks like straight forward, but the following line
> > is a great mistery for me:
> >
> > SELECT to_timestamp(to_char(20041217, '9999 99 99'), 'YYYY MM DD')
> >
> > results in a timestamp "2171-11-05 22:00:00" ??? The int-to-char
> > conversion works well ("2004 12 17") but the to_timestamp() doesn't work
> > as expected - why?
>
> to_char() is returning a leading space:
>
> test=> SELECT 'x' || to_char(20041217, '9999 99 99') || 'y';
> ?column?
> ---------------
> x 2004 12 17y
>
> The leading space is confusing to_timestamp():
>
> test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
> to_timestamp
> ---------------------
> 2171-11-06 06:00:00
>
> You can tell to_timestamp() to account for the leading space or you
> can tell to_char() to suppress it:
>
> test=> SELECT to_timestamp(to_char(20041217, '9999 99 99'), ' YYYY MM DD');
> to_timestamp
> ------------------------
> 2004-12-17 00:00:00-07
>
> test=> SELECT to_timestamp(to_char(20041217, 'FM9999 99 99'), 'YYYY MM DD');
> to_timestamp
> ------------------------
> 2004-12-17 00:00:00-07