Thread: Type Conversion Error after migrating from 8.3 to 9.2

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