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
