From: norvelle@ag.arizona.edu
To: pgsql-sql@postgresql.org
Subject: Re: Trying to convert a TIMESTAMP return value to TIME
Date: 21 Feb 2002 13:13:40 -0800
Message-ID: <48367cfb.0202211313.6c22e6c2@posting.google.com> (raw)
References: <48367cfb.0202201935.3dc51c8a@posting.google.com>
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
> --------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: norvelle@ag.arizona.edu
Subject: Re: Trying to convert a TIMESTAMP return value to TIME
In-Reply-To: <48367cfb.0202211313.6c22e6c2@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox