Re: to_timestamp alternatives

On 12/31/2015 12:30 PM, gkhan wrote:
> 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')
>
> ...but I discovered that the command above gives me the same result one hour
> earlier:
> SELECT to_timestamp('09.03.2014'||' '||lpad('2:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS').
>
> That's because to_timestamp was silently converting into my local time zone
> (UTC -7), even though I was putting the result into a 'timestamp without
> time zone' variable.  Like commenters on the thread "to_timestamp() and
> timestamp without time zone", I consider the silent conversion to be bad
> behavior, or at least I wish that the documentation warned the user more
> clearly, and I would greatly prefer a function that just dealt in UTC.
>
> 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.

Why not simplify:

test=> select ('09.03.2014'||' '||lpad('3:00:00',8,'0'))::timestamp;
       timestamp
---------------------
  2014-09-03 03:00:00
(1 row)

test=> select ('09.03.2014'||' '||lpad('2:00:00',8,'0'))::timestamp;
       timestamp
---------------------
  2014-09-03 02:00:00
(1 row)

>
> Thanks!
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com