Lists: | pgsql-general |
---|
From: | Soeren Gerlach <soeren(at)all-about-shift(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem in converting int to timestamp value - why? |
Date: | 2004-09-19 14:17:52 |
Message-ID: | 1095603472.1903.13.camel@hermes.all-about-shift.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Soeren Gerlach <soeren(at)all-about-shift(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem in converting int to timestamp value - why? |
Date: | 2004-09-19 15:12:32 |
Message-ID: | 20040919151232.GA94970@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | Soeren Gerlach <soeren(at)all-about-shift(dot)com> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem in converting int to timestamp value - why? |
Date: | 2004-09-19 16:19:06 |
Message-ID: | 1095610746.1897.16.camel@hermes.all-about-shift.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | Soeren Gerlach <soeren(at)all-about-shift(dot)com>, pgsql-general(at)postgresql(dot)org, Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> |
Subject: | Re: Problem in converting int to timestamp value - why? |
Date: | 2004-09-19 16:31:26 |
Message-ID: | 19495.1095611486@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Michael Fuhr <mike(at)fuhr(dot)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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Soeren Gerlach <soeren(at)all-about-shift(dot)com>, pgsql-general(at)postgresql(dot)org, Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> |
Subject: | Re: Problem in converting int to timestamp value - why? |
Date: | 2004-09-19 22:09:22 |
Message-ID: | 20040919220922.GA95715@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sun, Sep 19, 2004 at 12:31:26PM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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/
From: | Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Soeren Gerlach <soeren(at)all-about-shift(dot)com>, List pgsql-general <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: Problem in converting int to timestamp value - why? |
Date: | 2004-09-20 09:20:34 |
Message-ID: | 1095672034.2836.24.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)fuhr(dot)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/