pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrying to convert a TIMESTAMP return value to TIME
3+ messages / 2 participants
[nested] [flat]
* Trying to convert a TIMESTAMP return value to TIME
@ 2002-02-21 03:35 Erik Norvelle <norvelle@ag.arizona.edu>
0 siblings, 2 replies; 3+ messages in thread
From: norvelle@ag.arizona.edu @ 2002-02-21 03:35 UTC (permalink / raw)
To: pgsql-sql
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
--------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trying to convert a TIMESTAMP return value to TIME
@ 2002-02-21 21:13 Erik Norvelle <norvelle@ag.arizona.edu>
parent: Erik Norvelle <norvelle@ag.arizona.edu>
1 sibling, 0 replies; 3+ messages in thread
From: norvelle@ag.arizona.edu @ 2002-02-21 21:13 UTC (permalink / raw)
To: pgsql-sql
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
> --------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trying to convert a TIMESTAMP return value to TIME
@ 2002-02-22 02:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Norvelle <norvelle@ag.arizona.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-02-22 02:07 UTC (permalink / raw)
To: norvelle@ag.arizona.edu; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-02-22 02:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-21 03:35 Trying to convert a TIMESTAMP return value to TIME Erik Norvelle <norvelle@ag.arizona.edu>
2002-02-21 21:13 ` Erik Norvelle <norvelle@ag.arizona.edu>
2002-02-22 02:07 ` Tom Lane <tgl@sss.pgh.pa.us>
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