Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id DEA60475A31 for ; Thu, 21 Feb 2002 16:40:56 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1LLDqP67655 for pgsql-sql@postgresql.org; Thu, 21 Feb 2002 15:13:52 -0600 (CST) (envelope-from news) From: norvelle@ag.arizona.edu (Erik Norvelle) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Trying to convert a TIMESTAMP return value to TIME Date: 21 Feb 2002 13:13:40 -0800 Organization: http://groups.google.com/ Lines: 44 Message-ID: <48367cfb.0202211313.6c22e6c2@posting.google.com> References: <48367cfb.0202201935.3dc51c8a@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200202/307 X-Sequence-Number: 6729 I'm posting a followup to my own message to indicate the solution I found. I found I had to use the CAST operator, as follows: INSERT INTO EVENTS (EVENT_ID, EVENT_TIME, TITLE) VALUES ('20394XCB', CAST(TO_TIMESTAMP('10:45', 'HH:MI') AS TIME WITH TIME ZONE), 'An Event'); Apparently, once the timestamp has been cast to TIME WITH TIME ZONE, PostgreSQL can figure out how to convert to a standard TIME type (ie. no timezone). -Erik Norvelle norvelle@ag.arizona.edu (Erik Norvelle) wrote in message news:<48367cfb.0202201935.3dc51c8a@posting.google.com>... > I have a table with a column of type TIME, and I am trying to insert a > row into it in the following manner: > > INSERT INTO EVENTS (EVENT_ID, EVENT_TIME, TITLE) VALUES ('20394XCB', > TO_TIMESTAMP('10:45', 'HH:MI'), 'An Event'); > > I get the following error: 'column "event_time" is of type 'time > without time zone' but expression is of type 'timestamp with time > zone' You will need to rewrite or cast the expression'. > > I have looked extensively at the PostgreSQL documentation, and have > failed to find any info on how to get a TIME value out of a TIMESTAMP, > or else how to convert a CHAR time like '10:45' directly to a TIME. > The suggestion about casting given by the error message was > tantalizing, but I don't know how to make the cast happen. > > Any clues or ideas from Pgsql gurus? TKA. > > -Erik Norvelle > > -------------------------------------------- > Erik Norvelle > Support Systems Analyst, Sr. > Distributed Learning Laboratory > Educational Communications and Technologies > College of Agriculture and Life Sciences > The University of Arizona > -------------------------------------------- > Credo in Unum Deum > --------------------------------------------