Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id E6843633906
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 29 Apr 2010 21:07:00 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 91271-04-3 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 30 Apr 2010 00:06:51 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f199.google.com (mail-pz0-f199.google.com
	[209.85.222.199])
	by mail.postgresql.org (Postfix) with ESMTP id 0DE2F633CE7
	for <pgsql-sql@postgresql.org>; Thu, 29 Apr 2010 21:06:47 -0300 (ADT)
Received: by pzk37 with SMTP id 37so3937808pzk.29
	for <pgsql-sql@postgresql.org>; Thu, 29 Apr 2010 17:06:46 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:in-reply-to
	:references:date:message-id:subject:from:to:cc:content-type;
	bh=4ev0wKCkG3fnoAuiS8M5N26qWEmQlcVYyK7IIAIcafY=;
	b=H4D3CUcu/VUV/Yhm8gIB6s36l1CdMvUwNq5XEAABIWMSd7HF/klG0qg/LKTXEhP7lv
	qypTe0CBQn5Ys+RKv1qi0l1N0rTYKdp3rmqZuA/s8Nrk6eDwxibkDZrEGqY+4AQt8x0l
	SCjJ/PDQuiV/va/J5QT224MryqbTjJSlCI/iA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=cz+2QGMnPBFa7HkxuwPPrXmz6DcqibO0y4gh8i3LsJifafRyKltgP/pNee4EIK3+B9
	Fm171i5JYAuXBCKX+ubvX6CZZ7qwCfbKr9+2Ue4/hOuihH2FXdZmO8eMUd4T2FMnOhng
	PFCXNvjJv2AlJJq8EmkgqtVi/JYDv6PDSB8YI=
MIME-Version: 1.0
Received: by 10.141.108.9 with SMTP id k9mr226931rvm.59.1272586005821;
 Thu, 29
	Apr 2010 17:06:45 -0700 (PDT)
Received: by 10.140.171.21 with HTTP; Thu, 29 Apr 2010 17:06:45 -0700 (PDT)
In-Reply-To: <4BD93C4D.20807@gmail.com>
References: <4BD8FCA6.1020901@gmail.com> <853.1272512343@sss.pgh.pa.us>
	<4BD93C4D.20807@gmail.com>
Date: Thu, 29 Apr 2010 17:06:45 -0700
Message-ID: <s2neae6a62a1004291706h82a66c11mc06f9b01cdc39433@mail.gmail.com>
Subject: Re: problem converting strings to timestamps with time zone
From: DM <dm.aeqa@gmail.com>
To: efrossuhl@gmail.com
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0cd13a76d2bb2604856904c1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.888 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_NONE=-0.0001, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201004/116
X-Sequence-Number: 34043

--000e0cd13a76d2bb2604856904c1
Content-Type: text/plain; charset=ISO-8859-1

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
>

--000e0cd13a76d2bb2604856904c1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Could you please post the exact string how you did?<div><br></div><div>Than=
ks</div><div>Deepak<br><br><div class=3D"gmail_quote">On Thu, Apr 29, 2010 =
at 12:59 AM, Edward Ross <span dir=3D"ltr">&lt;<a href=3D"mailto:efrossuhl@=
gmail.com">efrossuhl@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><div><div></div><div class=3D"h5">Tom Lane =
wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
Edward Ross &lt;<a href=3D"mailto:efrossuhl@gmail.com" target=3D"_blank">ef=
rossuhl@gmail.com</a>&gt; writes:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
Here is a sandbox example of what I mean.<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
CREATE TABLE test_0<br>
(<br>
 =A0 =A0string_value varchar(2047),<br>
 =A0 =A0timestamp_value timestamp with time zone<br>
);<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
insert into test_0<br>
 =A0 =A0 =A0 (string_value)<br>
 =A0 =A0values<br>
 =A0 =A0 =A0 (&#39;2010-03-12 17:06:21-0800&#39;),<br>
 =A0 =A0 =A0 (&#39;2009-08-14 16:47:40+0500&#39;),<br>
 =A0 =A0 =A0 (&#39;2010-03-22 22:45:59-0400&#39;);<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
As expected, select * from test_0; , produces the following:<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
string_value =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0timestamp_value<br>
2010-03-12 17:06:21-0800 =A0 =A0 =A0 =A0&lt;null&gt;<br>
2009-08-14 16:47:40+0500 =A0 =A0 =A0 =A0&lt;null&gt;<br>
2010-03-22 22:45:59-0400 =A0 =A0 =A0 =A0&lt;null&gt;<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
I would like to parse the strings into their equivalent timestamps<br>
and put them in the timestamp_value column.<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
My attempt, so far, to update the table:<br>
</blockquote>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
update value<br>
 =A0 =A0set<br>
 =A0 =A0timestamp_value =3D<br>
 =A0 =A0to_timestamp(string_value, &#39;YYYY-MM-DD HH24:MI:SS-XXXX&#39;);<b=
r>
</blockquote>
<br>
Forget to_timestamp; just cast the string to timestamptz. =A0The<br>
regular timestamp input converter will handle that format fine.<br>
<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane<br>
<br>
</blockquote>
<br></div></div>
That worked great.<br>
<br>
Thanks very much,<br><font color=3D"#888888">
<br>
Edward Ross</font><div><div></div><div class=3D"h5"><br>
<br>
-- <br>
Sent via pgsql-sql mailing list (<a href=3D"mailto:pgsql-sql@postgresql.org=
" target=3D"_blank">pgsql-sql@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-sql" target=3D"_blank">=
http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</div></div></blockquote></div><br></div>

--000e0cd13a76d2bb2604856904c1--