Обсуждение: Problem in converting int to timestamp value - why?

Hello,

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?

I'm running Postgresql 7.4


Thanks a lot,
Soeren Gerlach


-------------------------------------------------------------
Heute schon gelacht?  http://all-about-shift.com/dailystrips/

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

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Michael Fuhr <mike@fuhr.org> writes:
> 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

I'd still say this is a bug.  If to_timestamp can't match the input to
the pattern, it should throw an error, not silently return garbage.

            regards, tom lane

On Sun, Sep 19, 2004 at 12:31:26PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > 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
>
> I'd still say this is a bug.  If to_timestamp can't match the input to
> the pattern, it should throw an error, not silently return garbage.

Agreed that it's a bug.  Returning garbage violates the Principle
of Least Surprise.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, 2004-09-19 at 16:09 -0600, Michael Fuhr wrote:
> On Sun, Sep 19, 2004 at 12:31:26PM -0400, Tom Lane wrote:
> > Michael Fuhr <mike@fuhr.org> writes:
> > > 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
> >
> > I'd still say this is a bug.  If to_timestamp can't match the input to
> > the pattern, it should throw an error, not silently return garbage.

I already start work on new to_char() that will more paranoid, for now
is enough for right usage follow docs.

    Karel

--
Karel Zak
http://home.zf.jcu.cz/~zakkr/