PostgreSQL: Convert timestamp to time - or retrieve only time from timestamp column

Question

What I want is something equivalent to this:

select to_time( to_timestamp ( '03.04.2014 12:34:00', 'DD.MM.YYYY HH24:MI:SS' ) ) 

Problem here is that to_time does not exist. I want this to result in

12:34:00 (without the date) 

Is this possible ?

Answer 1

select timestamp '2014-04-03 12:34:00'::time 

I prefer ANSI date/timestamp literals as they are shorter to write than to_timestamp()

The above is equivalent to:

select to_timestamp ('03.04.2014 12:34:00', 'DD.MM.YYYY HH24:MI:SS')::time 

the ::time is Postgres' short hand notation for casting a value.

The following would be complete ANSI SQL if you want that:

select cast(timestamp '2014-04-03 12:34:00' as time)