DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=n/01J28UKtANHsb857EYLPiugk9YdqB4HsS1qO8dwrS1nYbeas4ZMkMCrYo9FLY43I
	HdBOL+1ZB040CjZrAA1hYLrONuxvTA9OLQMT141GEdR4o/qiR2YrGfDk02Rh4IVl6F5h
	ebnGyRkbxqPa4OdkE6KNp9rr6d8fhCVKYTlfk=
Message-ID: <4BD8FCA6.1020901@gmail.com>
Date: Wed, 28 Apr 2010 20:27:34 -0700
From: Edward Ross <efrossuhl@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: problem converting strings to timestamps with time zone
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have a table with varchar and timestamp with time zone columns, among 
others.  Insertions into this table are usually done by a java program; 
and there are many tens of thousands of records in the table.

Recently, after changes in the java software, many timestamps values 
inadvertently ended up in one of the varchar columns.  Rather than 
writing some more java to rectify the situation, I was hoping it could 
be done with one update statement.

However I'm having no luck constructing such a statement; I can't find a 
way to include the timezones in the update. It seems like this would be 
a rather common situation, but I've found no answers in the 
documentation nor the archives.

If anyone knows how to do this, I would be most grateful.

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');
                                                        ^
                                                        |
                                                        |
The X's just mark where I would like to specify a time zone. But 
apprently to_timestamp has no way of inputting time zones.  Does anyone 
know of another way?

Thanks,

Edward Ross