Re: problem converting strings to timestamps with time zone

Tom Lane wrote:

Edward Ross <efrossuhl@gmail.com> writes:

Here is a sandbox example of what I mean.

CREATE TABLE test_0
(
   string_value varchar(2047),
   timestamp_value timestamp with time zone
);

insert into test_0
      (string_value)
   values
      ('2010-03-12 17:06:21-0800'),
      ('2009-08-14 16:47:40+0500'),
      ('2010-03-22 22:45:59-0400');

As expected, select * from test_0; , produces the following:

string_value                timestamp_value
2010-03-12 17:06:21-0800        <null>
2009-08-14 16:47:40+0500        <null>
2010-03-22 22:45:59-0400        <null>

I would like to parse the strings into their equivalent timestamps
and put them in the timestamp_value column.

My attempt, so far, to update the table:

update value
   set
   timestamp_value =
   to_timestamp(string_value, 'YYYY-MM-DD HH24:MI:SS-XXXX');

Forget to_timestamp; just cast the string to timestamptz.  The
regular timestamp input converter will handle that format fine.

                       regards, tom lane

That worked great.

Thanks very much,

Edward Ross

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