- changed status to closed
PostgreSQL 24:00:00 time provokes exception
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.
Comments (4)
-
repo owner -
repo owner 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.
-
repo owner ... 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
-
reporter 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!
- Log in to comment
SQLAlchemy isn't raising a ValueError here, that is your driver.
Feel free to report to the psycopg2 project
output: