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
