Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C9992475989 for ; Thu, 21 Feb 2002 21:07:17 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1M27ES12975; Thu, 21 Feb 2002 21:07:14 -0500 (EST) To: norvelle@ag.arizona.edu (Erik Norvelle) Cc: pgsql-sql@postgresql.org Subject: Re: Trying to convert a TIMESTAMP return value to TIME In-reply-to: <48367cfb.0202201935.3dc51c8a@posting.google.com> References: <48367cfb.0202201935.3dc51c8a@posting.google.com> Comments: In-reply-to norvelle@ag.arizona.edu (Erik Norvelle) message dated "20 Feb 2002 19:35:02 -0800" Date: Thu, 21 Feb 2002 21:07:14 -0500 Message-ID: <12972.1014343634@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/298 X-Sequence-Number: 6721 norvelle@ag.arizona.edu (Erik Norvelle) writes: > 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. A little bit of experimentation found that you can get there indirectly by casting first to time-with-time-zone: regression=# select TO_TIMESTAMP('10:45', 'HH:MI'); to_timestamp --------------------- 0001-01-01 10:45 BC (1 row) regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::time; ERROR: Cannot cast type 'timestamp with time zone' to 'time without time zone' regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::timetz; to_timestamp -------------- 10:45:00+00 (1 row) regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::timetz::time; to_timestamp -------------- 10:45:00 (1 row) However, if you don't need the formatting control of to_timestamp (ie, the input data should be valid time input anyway), why not forget to_timestamp and just cast text to time? regression=# select '10:45'::text::time; time ---------- 10:45:00 (1 row) regards, tom lane