Thread: BUG #5714: TZ pattern error on to_timestamp

The following bug has been logged online:

Bug reference:      5714
Logged by:          Alexander Loechel
Email address:      Alexander.Loechel@unibw.de
PostgreSQL version: 8.4.5
Operating system:   Ubuntu Server 10.04. LTS
Description:        TZ pattern error on to_timestamp
Details:

After an update on my ubuntu system of postgres 8.4.4 to 8.4.5 the
to_timestamp methode produces different results.

in a CREAT View statement I have following methode call:
>>to_timestamp(dbtimestamp, 'YYYY/MM/DD HH24:MI:SS TZ') AS "timestamp"<<

Where dbtimestamp is a String coming from a CMS.

before migration it produces normal timestamps
after migration I get following Message:

"ERROR: TZ/tz format patterns are not supported in to_date"

I guess this behaviour is not intended as chapter 9.9 of the manual allows a
TZ pattern on to_timestamp().

"Alexander Loechel" <Alexander.Loechel@unibw.de> writes:
> After an update on my ubuntu system of postgres 8.4.4 to 8.4.5 the
> to_timestamp methode produces different results.

> in a CREAT View statement I have following methode call:
> to_timestamp(dbtimestamp, 'YYYY/MM/DD HH24:MI:SS TZ') AS "timestamp"<<

> Where dbtimestamp is a String coming from a CMS.

> before migration it produces normal timestamps
> after migration I get following Message:

> "ERROR: TZ/tz format patterns are not supported in to_date"

Testing shows that that example fails in every Postgres release back to
7.1.  The spelling of the error message varies a bit, but they all agree
that a TZ format spec isn't supported.

I don't know what changed in your installation, but it wasn't this.

            regards, tom lane

On Sun, Oct 17, 2010 at 11:10:09AM -0400, Tom Lane wrote:
> "Alexander Loechel" <Alexander.Loechel@unibw.de> writes:
> > After an update on my ubuntu system of postgres 8.4.4 to 8.4.5 the
> > to_timestamp methode produces different results.
>
> > in a CREAT View statement I have following methode call:
> > to_timestamp(dbtimestamp, 'YYYY/MM/DD HH24:MI:SS TZ') AS "timestamp"<<
>
> > Where dbtimestamp is a String coming from a CMS.
>
> > before migration it produces normal timestamps
> > after migration I get following Message:
>
> > "ERROR: TZ/tz format patterns are not supported in to_date"
>
> Testing shows that that example fails in every Postgres release back to
> 7.1.  The spelling of the error message varies a bit, but they all agree
> that a TZ format spec isn't supported.
>
> I don't know what changed in your installation, but it wasn't this.

Any reason why Pg can't parse TZ?
perhaps it should be also listed in
http://www.postgresql.org/docs/current/interactive/functions-formatting.html
doc, that you can't use TZ (and some other patterns too)?

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Sun, Oct 17, 2010 at 11:10:09AM -0400, Tom Lane wrote:
>> Testing shows that that example fails in every Postgres release back to
>> 7.1.  The spelling of the error message varies a bit, but they all agree
>> that a TZ format spec isn't supported.

> Any reason why Pg can't parse TZ?

Nobody's bothered to do the work ...

> perhaps it should be also listed in
> http://www.postgresql.org/docs/current/interactive/functions-formatting.html
> doc, that you can't use TZ (and some other patterns too)?

Nobody's bothered to do the work for that, either.

            regards, tom lane