How to extract hour, minutes and seconds from a DATE

Question

I have the following query:

select cast(max_bid_ts as TIMESTAMP) from my_table;

I cast the max_bid_ts because it was a double and I wanted to be a TMESTAMP. This query returns something like this

2016-04-21 12:41:46.313999872

I only want the hour:minutes:seconds part. In this case would be 12:41:46. What would be the best way to do this?

Answer 1

Try something like this:

select to_char(YourTimeStampField,'HH:MI:SS') from YourTable

And if you want a 24-hour time format, try this:

select to_char(YourTimeStampField,'HH24:MI:SS') from YourTable

Another way would be:

select YourTimeStampField::time from YourTable