PostgreSQL 24:00:00 time provokes exception

[Hasier]

In PostgreSQL, a value of 24:00:00 in a time type column is valid, but Python only accepts a max of 23:59:59.999999. Therefore it breaks with 'ValueError: hour must be in 0..23' when retrieving it.

P.S.: I guess it would be a better practice to store 23:59:59.99 in the DB as the SQL standard says, but I think it should be somewhat managed anyway, transforming the retrieved value to 23:59:59.99.

[Mike Bayer]

SQLAlchemy isn't raising a ValueError here, that is your driver.

Feel free to report to the psycopg2 project

import psycopg2

conn = psycopg2.connect(
    user="scott", password="tiger", host="localhost", database="test")
cursor = conn.cursor()

cursor.execute("create table test_date (value time)")
cursor.execute("insert into test_date (value) values ('24:00:00')")

cursor.execute("select value from test_date")
row = cursor.fetchone()
print row

output:

#!


Traceback (most recent call last):
  File "test.py", line 11, in <module>
    row = cursor.fetchone()
ValueError: hour must be in 0..23

[Mike Bayer]

if you'd like to transform invalid time values, use a TypeDecorator. See the docs at http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#augmenting-existing-types. But this won't allow you to retrieve existing values in the DB that are in this format already, since they have to pass through the psycopg2 driver. To extract them you'd need to CAST as VARCHAR on the SQL side so that psycopg2 doesn't see a time type on the way out.

[Mike Bayer]

... the CAST you can do using TypeDecorator's http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#sqlalchemy.types.TypeDecorator.column_expression method

[Hasier]

Oh ok. I just saw the error coming from python2.7/site-packages/sqlalchemy/engine/result.py so reported here, sorry about that :) And thanks for the tips!