From: | gkhan <drjohnpayne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | to_timestamp alternatives |
Date: | 2015-12-31 20:30:43 |
Message-ID: | 1451593843000-5879723.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-12-31 20:52:01 | Re: to_timestamp alternatives |
Previous Message | Adrian Klaver | 2015-12-31 20:16:31 | Re: SSL connection issue via perl |