Re: problem with select into and timestamp.

From: Jie Liang <jie(at)stbernard(dot)com>
To: 'Geoff ' <geoff(at)metalogicplc(dot)com>, "'Pgsql-Admin (E-mail) '" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: problem with select into and timestamp.
Date: 2002-08-09 18:27:55
Message-ID: 7C760DAA511DC74B99E7D22189F786F101BF2159@mail01.stbernard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I hope this is what you want:

urldb=# create table foo(d date,t time);
CREATE
urldb=# insert into foo values(CURRENT_DATE,CURRENT_TIME);
INSERT 97260539 1
urldb=# select * from foo;
d | t
------------+-----------------
2002-08-09 | 11:20:12.214437
(1 row)

urldb=# create table foobar (newdate timestamp);
CREATE
urldb=# insert into foobar(newdate) select (d||' '||t)::timestamp as newdate
from foo;
INSERT 97260628 1
urldb=# select * from foobar;
newdate
-------------------------------
2002-08-09 11:20:12.214437-07
(1 row)

urldb=# \d foobar
Table "foobar"
Column | Type | Modifiers
---------+--------------------------+-----------
newdate | timestamp with time zone |

Jie Liang

-----Original Message-----
From: Geoff
To: Pgsql-Admin (E-mail)
Sent: 8/9/2002 3:32 AM
Subject: [ADMIN] problem with select into and timestamp.

I've got a small problem with "select into".

I've got an old table with date and time as seperate text columns.
I want to insert these into a new table into 1 column as timestamp
format.
I spent this morning looking at the 7.2 docs and spent ages working on
something that's not supported (i.e. the timestamp (date '2002-08-09',
time
'11:29:00') ) function...arg!

could someone show me the light on how I can get this working correctly
and
make my day easier.

many thanks

Geoff

-----------------------------------------------------------------------
This message is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon, this information by persons or entities other
than
the intended recipient is prohibited. If you receive this message in
error
please contact the sender by return e-mail and delete the message from
your
computer.

Any opinions contained in this message are those of the author and are
not
given or endorsed by Metalogic PLC unless otherwise clearly indicated in
this message and the authority of the author to bind Metalogic is duly
verified.

Metalogic PLC accepts no liability for any errors or omissions in the
context of this message which arise as a result of internet
transmission.
-----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Eisentraut 2002-08-09 22:54:25 Re: initdb "Fails to initialize lc_time" (using 7.3.1)
Previous Message Jie Liang 2002-08-09 18:09:07 Another possible bug in pg_restore