Thread: Trying to convert a TIMESTAMP return value to TIME
Trying to convert a TIMESTAMP return value to TIME
From
norvelle@ag.arizona.edu (Erik Norvelle)
Date:
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 --------------------------------------------
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
Re: Trying to convert a TIMESTAMP return value to TIME
From
norvelle@ag.arizona.edu (Erik Norvelle)
Date:
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 > --------------------------------------------