Geek Answers Handbook

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

I am trying to extract the time of day from the "timestamp" column in PostgreSQL. This is how I did it, but ... it's terrible. Idea 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;
+5
source share
2 answers

If you need the exact time with a timestamp, just enter the time:

SELECT
    CAST(colname AS time)
FROM
    tablename;

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

+6
source

It depends on the format in which you want to use it.

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

, - :

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

More articles:


All Articles