pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem converting strings to timestamps with time zone
4+ messages / 3 participants
[nested] [flat]
* problem converting strings to timestamps with time zone
@ 2010-04-29 03:27 Edward Ross <efrossuhl@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Edward Ross @ 2010-04-29 03:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem converting strings to timestamps with time zone
@ 2010-04-29 03:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Edward Ross <efrossuhl@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-04-29 03:39 UTC (permalink / raw)
To: Edward Ross <efrossuhl@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem converting strings to timestamps with time zone
@ 2010-04-29 07:59 Edward Ross <efrossuhl@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Edward Ross @ 2010-04-29 07:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem converting strings to timestamps with time zone
@ 2010-04-30 00:06 DM <dm.aeqa@gmail.com>
parent: Edward Ross <efrossuhl@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: DM @ 2010-04-30 00:06 UTC (permalink / raw)
To: efrossuhl@gmail.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Could you please post the exact string how you did?
Thanks
Deepak
On Thu, Apr 29, 2010 at 12:59 AM, Edward Ross <efrossuhl@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-04-30 00:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-29 03:27 problem converting strings to timestamps with time zone Edward Ross <efrossuhl@gmail.com>
2010-04-29 03:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-29 07:59 ` Edward Ross <efrossuhl@gmail.com>
2010-04-30 00:06 ` DM <dm.aeqa@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox