From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | gkhan <drjohnpayne(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: to_timestamp alternatives |
Date: | 2015-12-31 20:52:01 |
Message-ID: | 56859571.10201@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-12-31 21:07:48 | Re: to_timestamp alternatives |
Previous Message | gkhan | 2015-12-31 20:30:43 | to_timestamp alternatives |