Обсуждение: problem converting strings to timestamps with time zone

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

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

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