Table with TIMESTAMP column fetch wrong amount of microseconds

[luminosoda]

import jaydebeapi

from datetime import datetime

conn = jaydebeapi.connect("org.h2.Driver", url="jdbc:h2", driver_args=["login", "password"], jars="h2-2.1.212.jar")

format_ = "%Y-%m-%d %H:%M:%S.%f"
dt1 = datetime(2023, 5, 16, 18, 23, 15, 200000)
dt1_str = dt1.strftime(format_)
dt2 = datetime(2023, 5, 16, 18, 23, 15, 90000)
dt2_str = dt2.strftime(format_)

conn.cursor().execute("CREATE TABLE IF NOT EXISTS example (column TIMESTAMP)")
conn.cursor().execute(f"INSERT INTO example(column) VALUES '{dt1_str}', '{dt2_str}'")
cursor = conn.cursor()
cursor.execute("SELECT * FROM example")
values = cursor.fetchall()

dt1_fetch = datetime.strptime(values[0][0], format_)
print(dt1.microsecond, dt1_fetch.microsecond)
assert dt1 == dt1_fetch
dt2_fetch = datetime.strptime(values[1][0], format_)
print(dt2.microsecond, dt2_fetch.microsecond)
assert dt2 == dt2_fetch

dt1 = 200000, dt1_fetch = 200000
dt2 = 90000, dt2_fetch = 900000 (extra zero)

[luminosoda]

@baztian please merge, this bug cause data inconsistency

[luminosoda]

Temporary solution:

def _to_datetime_patched(rs, col):
    java_val = rs.getTimestamp(col)
    if not java_val:
        return
    d = datetime.datetime.strptime(str(java_val)[:19], "%Y-%m-%d %H:%M:%S")
    d = d.replace(microsecond=java_val.getNanos() // 1000)
    return str(d)
 
conn = jaydebeapi.connect(...)
# Only after the connection
# 93 - TIMESTAMP
jaydebeapi._converters[93] = _to_datetime_patched

[baztian]

I will only merge changes that have an unit test