Re: Trying to convert a TIMESTAMP return value to TIME

norvelle(at)ag(dot)arizona(dot)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