Search Postgresql Archives

Re: to_timestamp alternatives

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



gkhan <drjohnpayne@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux