PostgreSQL time conversion/formatting from "24:00:00" to "00:00:00" in SELECT statement

Question

I have some market data with time fields stored in a PostgreSQL database. PostgreSQL uses the format "00:00:00" to "24:00:00" to store times (see http://www.postgresql.org/docs/9.1/static/datatype-datetime.html) which works perfectly as long as I only work within the database.

The problem is that I have to do some data processing (using Python) afterwards and the Python datetime.time format only supports the hours from "00:00:00" to "23:00:00". So if I fetch a record that contains "24:00:00" using the psycopg2 module I get an error "ValueError: hour must be in 0..23" because the time field cannot be converted properly.

My idea for a clean workaround is to convert the time field that contains the "24:00:00" hour already in the SELECT statement to "00:00:00". This would solve the problem as the converter function would not fail afterwards.

I have already looked at the formatting functions (see http://www.postgresql.org/docs/9.4/static/functions-formatting.html) but could not find anything suitable..

Is there a way to realize this using SQL?

Thanks in advance!

Answer 1

When you add time to a date the result is a timestamp which you can cast to time:

select (current_date + market_data_time)::time;