Is there a better way to extract the time of day ?

Question

I'm trying to extract the time of day from a 'timestamp' column in PostgreSQL. Here is how I did it but... it's awful. An idea of how to do it better ?

SELECT (
    date_part('hour', date_demande)::text   || ' hours '   ||
    date_part('minute', date_demande)::text || ' minutes ' ||
    date_part('second', date_demande)::text || ' seconds'
    )::interval AS time_of_day

FROM table;

Answer 1

It depends in which format you want it ofcourse.

But instead of using date_part it might be easier to use to_char instead. http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

So in your case perhaps something like this:

to_char(current_timestamp, 'HH:MI:SS')

Answer 2

If you need the exact time from a timestamp, just cast to time:

SELECT
    CAST(colname AS time)
FROM
    tablename;

If you need formatting, to_char() is your best option.