Can't extract time from timestamp Postgres

Question

Hi im trying to extract time from a timestamp in Postgres

SELECT extract(time from '2000-01-01 01:12:00'::timestamp)

Result should be: 01:12:00

It looks like that time is not a valid argument for extract. Is that right?

Solution 1

Try this:

s=# SELECT cast ('2000-01-01 01:12:00'::timestamp as time);
   time
----------
 01:12:00
(1 row)

doesn't extract - then, you might want to use cast.

and yes - https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

time is not a valid aprt

Solution 2

select '2000-01-01 01:12:00'::timestamp::time