[Fwd: help building datetime from varchars]

[Shane D.]

I have tried contacting the list owner, but nobody responds so I am
knowingly sending administrative mail to the list. Sorry folks.

For months, particularly since February, I have been receiving e-mail
that is out-of-date. Since they are relatively old (weeks to months) I
cannot say whether or not they are duplicates. A copy of such an e-mail
I just received this morning is attached along its headers. Note that
postgresql.org received it on 27 Apr and that it was relayed to my mail
server within the last 24 hours (May 3 at 11:54pm GMT-4 to be exact).
Why does this happen and can't it be made to stop? (And I'm not picking
on Brent. His is just one of 8 messages I received this morning from
the month of April.)

Shane

-------- Original Message --------
Return-path:
<pg*********************************************** *@postgresql.org>
Received: from conversion-daemon.sms1.wright.edu by sms1.wright.edu
(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003)) id
<0H************@sms1.wright.edu> for sh**********@wright.edu; Tue, 04
May 2004 00:55:40 -0400 (EDT)
Received: from avs1.wright.edu (avs1.wright.edu [130.108.128.93]) by
sms1.wright.edu (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8
2003)) with ESMTP id <0H************@sms1.wright.edu> for
sh**********@wright.edu; Tue, 04 May 2004 00:55:40 -0400 (EDT)
Received: from CONVERSION-DAEMON.avs1.wright.edu by avs1.wright.edu
(PMDF V6.2-X27 #30758) id <0H************@avs1.wright.edu> for
sh**********@wright.edu; Tue, 04 May 2004 00:55:40 -0400 (EDT)
Received: from zippy.ims.net (zippy.ims.net [208.166.202.2]) by
avs1.wright.edu (PMDF V6.2-X27 #30758) with ESMTP id
<0H************@avs1.wright.edu> for sh**********@wright.edu; Tue, 04
May 2004 00:55:40 -0400 (EDT)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by
zippy.ims.net (8.11.6/linuxconf) with ESMTP id i444s9B02154 for
<sh**********@wright.edu>; Mon, 03 May 2004 23:54:22 -0500
Received: from localhost (unknown [200.46.204.2]) by
svr1.postgresql.org (Postfix) with ESMTP id 49B76D1B50C for
<pg**************************@localhost.postgresql .org>; Tue, 27 Apr
2004 23:15:48 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71]) by localhost
(neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id
25085-03 for <pg**************************@localhost.postgresql .org>;
Tue, 27 Apr 2004 23:15:50 -0300 (ADT)
Received: from clam.niwa.co.nz (clam.niwa.cri.nz [202.36.29.1]) by
svr1.postgresql.org (Postfix) with ESMTP id BFB03D1B448 for
<pg***********@postgresql.org>; Tue, 27 Apr 2004 23:15:43 -0300 (ADT)
Received: from storm.niwa.co.nz (storm.niwa.co.nz [192.168.59.10]) by
clam.niwa.co.nz (8.12.3p3/8.12.3) with ESMTP id i3S2FjSb092102 for
<pg***********@postgresql.org>; Wed, 28 Apr 2004 14:15:46 +1200 (NZST
envelope-from b.****@niwa.co.nz)
Received: from localhost (woodb@localhost) by storm.niwa.co.nz
(8.11.6/8.11.6) with ESMTP id i3S2Fjc55463 for
<pg***********@postgresql.org>; Wed, 28 Apr 2004 14:15:45 +1200
Date: Wed, 28 Apr 2004 14:15:45 +1200 (NZST)
From: Brent Wood <b.****@niwa.co.nz>
Subject: [GENERAL] help building datetime from varchars
In-reply-to: <Pi**************************************@emo.org. tr>
Sender: pg*****************@postgresql.org
To: pg***********@postgresql.org
Message-id: <20**************************@storm.niwa.co.nz>
MIME-version: 1.0
Content-type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
X-Original-To: pg**************************@localhost.postgresql. org
X-Scanned-By: MIMEDefang 2.33 (www . roaringpenguin . com / mimedefang)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level:
X-Mailing-List: pgsql-general
Original-recipient: rfc822;sh**********@wright.edu

Hopefully someone can point me in the proper direction....

I have a table containg (amongst others) two varchar attrs date_s &
time_s.

They contain strings like:

date_s | time_s
------------+----------
01/10/1989 | 00:30:00

Can someone suggest an sql to turn these into a single datetime?


I have tried the following with results I don't understand:

env2003=# select date_s || time_s, to_timestamp(date_s || time_s,'DD/MM/YYYYHH:MM:SS') from event limit 1;
?column? | to_timestamp
--------------------+------------------------
01/10/198900:30:00 | 1991-06-05 00:00:00+12
(1 row)

env2003=# select date_s || ' ' || time_s, to_timestamp(date_s || ' ' || time_s,'DD/MM/YYYY HH:MM:SS') from event limit 1;
?column? | to_timestamp
---------------------+------------------------
01/10/1989 00:30:00 | 1991-06-05 00:00:00+12
(1 row)

Thanks,

Brent Wood
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly