Error on DB2 time fields that are 24:00:00

[dkornacki-cutco]

Environment

• Python: 3.8
• pyodbc: 4.0.31
• OS: Windows
• DB: Db2 for i 7.4
• Driver: iAccess Client Solutions

Issue

Time fields that are 24:00:00 are throwing ValueError: hour must be in 0..23. Datetime fields were fixed in #783 for #782 but there was an oversight on time-only fields. A similar fix is needed.

[mkleehammer]

The only two fixes I can think of are:

Convert to 00:00. Technically 24:00 is 00:00, just on the next day. This could be surprising if there is an associated date, either explicit or implicit. You would not be able to tell if the original value was 00:00 or 24:00.

Convert to 23:59.999999. This may cause round-trip confusion.

What would you expect the fix to do?

[dkornacki-cutco]

Converting 24:00 to 00:00 and incrementing the day is commonly how this is handled. I think that is the best solution here.

[soundstripe]

Incrementing the day only makes sense if the value in the db has a date. Python does not support a time instance with .hour >= 24.

Possibly a timedelta instance could be created. Unfortunately, the correlation between a SQL type and the Python type that pyodbc returns is very strong and not configurable, likely due to the DBAPI spec that pyodbc implements.

What about raising an exception where one of the args is a timedelta instance with the exact hours+minutes+seconds as read from the db? This would show that this is still not a "happy-path" way of doing things, but would allow trapping for the exception and handling the value.

[janipez]

Would it be possible to check if the received value from the field with time is larger than 24 hours before it gets assigned to a datetime.time object and use the datetime.timedelta instead?

I have an issue with the TIME datatype in MySQL. Everything over 24 hours gets removed. For example, "124:13:35" converts into"4:13:35":

[gordthompson]

For MySQL, mysqlclient already returns a datetime.timedelta object:

import sqlalchemy as sa

connection_url = "mysql+mysqldb://scott:tiger@localhost:3307/mydb"
engine = sa.create_engine(connection_url)
with engine.begin() as conn:
    result = conn.exec_driver_sql("SELECT CAST('124:13:35' AS TIME(0))").scalar()
    print(result)  # 5 days, 4:13:35
    print(repr(result))  # datetime.timedelta(days=5, seconds=15215)
    result = conn.exec_driver_sql("SELECT CAST('4:13:35' AS TIME(0))").scalar()
    print(result)  # 4:13:35
    print(repr(result))  # datetime.timedelta(seconds=15215)

Many, many SQLAlchemy users have found that using mysqlclient (or PyMySQL) works better than using MySQL Connector/ODBC.