Re: to_timestamp alternatives

gkhan <drjohnpayne(at)gmail(dot)com> writes:
> Hi. I have a practical need to convert some badly-formatted date/times into
> 'timestamp without time zone' data types. Like other scientists, I try to
> avoid timezone problems by sticking to UTC and using the 'timestamp without
> time zone' data type whenever possible.

> In this case, I used the to_timestamp() function as follows:
> SELECT to_timestamp('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')

I think you're wasting your time with to_timestamp. The timestamp type
itself is perfectly capable of parsing this, and most other reasonable
inputs too.

regression=# set datestyle = dmy;
SET
regression=# select '09.03.2014 03:00:00'::timestamp;
timestamp
---------------------
2014-03-09 03:00:00
(1 row)

In particular, since what to_timestamp() returns is timestamp WITH time
zone, converting its result to timestamp WITHOUT time zone will cause a
timezone rotation which is what is messing you up. If you feel you really
must do things this way, set the timezone parameter to "UTC" so there's no
zone conversion.

> In the recent thread "BUG #12739: to_timestamp function conver string to
> time incorrectly", tom lane suggests avoiding to_timestamp(). However, I
> don't see an easy way to get around it in my case. Can anyone suggest a
> good alternative? Please note that I want to avoid relying on global
> variables such as 'SET TIMEZONE = ...' if possible, since those just
> introduce more potential for confusion, IMHO.

You haven't provided one bit of convincing explanation as to why you
reject doing things in the multiple ways that will work, and insist on
doing it in a way that won't.

If your statement that you want to work exclusively in UTC isn't really
true, and you have a reason to want the global setting of TIMEZONE to be
something else, you could consider making a wrapper function that sets
TIMEZONE to UTC transiently while invoking to_timestamp and then coercing
its result to timestamp without time zone. Something like

create function to_timestamp_utc(text, text) returns timestamp
as $$ begin return to_timestamp($1, $2)::timestamp; end; $$
language plpgsql
strict
immutable
set timezone = utc;

regards, tom lane