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.
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.
