From: Richard Poole <rp@guests.deus.net>
To: pgsql-admin@postgresql.org
Subject: Re: Type Conversion Error after migrating from 8.3 to 9.2
Date: Mon, 13 May 2013 14:10:21 +0100
Message-ID: <20130513131021.GC10001@roobarb.crazydogs.org> (raw)
In-Reply-To: <CANkM0DjmMZP51QaLMQSxvrTdf2TOzjsQjFjfbfufx6GRr13nkw@mail.gmail.com>
References: <CANkM0DjmMZP51QaLMQSxvrTdf2TOzjsQjFjfbfufx6GRr13nkw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On Mon, May 13, 2013 at 04:34:14PM +0530, Hariraman Jayaraj wrote:
> But in some inserts where we use like
> *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh:mi:ss')::TIMESTAMP*
> it throws error as
> hour "22" is invalid for the 12-hour clock.
The 'hh' pattern has always been documented as meaning an hour no more
than 12 (equivalent to 'hh12'); it was actually a side-effect of the fix
to accept 'am' and 'pm' in either upper or lower case that made the code
start throwing errors rather than silently doing what you expected.
> But the same insert is working with version 8.3
> Only after modifying the to_timestamp with 24hour clock i.e.,
> *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh24:mi:ss')::TIMESTAMP*, its
> working fine in version 9.2
> Is there any other workaround available instead of modifying multiple
> queries as lot of our application coding has this conversion.
One possible workaround would be to define your own to_timestamp function
implementing the behaviour you want:
create or replace function public.to_timestamp(text, text)
returns timestamp with time zone as
$$select pg_catalog.to_timestamp($1, replace($2, 'hh', 'hh24'))$$
language 'sql';
then fix your schema search path so that your new function is the
one that gets hit by an unqualified call to to_timestamp:
set search_path to "$user",public,pg_catalog;
(that is how you would do it in psql; for real live use you would
presumably use ALTER ROLE so each connection has the correct path
by default)
and now your insert will do as you want. This is fraught with danger,
though, because now objects in your public schema will all be seen before
objects in pg_catalog so any name clashes you may have been unaware of
will suddenly reverse their behaviour. So you should definitely start
fixing your code. But this workaround might help you in the short term.
Richard
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rp@guests.deus.net
Subject: Re: Type Conversion Error after migrating from 8.3 to 9.2
In-Reply-To: <20130513131021.GC10001@roobarb.crazydogs.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox