[issue2551223] Timestamps are truncated in mysql and postgresql for session and otk database tables.

New submission from John Rouillard:

When using postgres or mysql as the database, roundup creates tables
for storing otks and session data. These define the X_time column as
real and float(20) respectively.

Googling turns up the mysql ignores the 20 length. So each one of them
uses a 32 bit float under the hood that keeps 6-8 digits precision. So
a timestamp for today from time.time() like: 1658718284.7616878 gets
stored in mysql as 1658720000.0. This adds/removes 10000 seconds from
the timestamp or about 2.7 hours.

For postgres I got:

  value to be stored     = 1658709741.578105
  value returned from db = 1658709800.0 

so the 741 was rounded up to 800 to keep 8 digits of precision.

for mysql I got:

  value to be stored     = 1658710040.9544694
  value returned from db = 1658710000.0

so 0040 got truncated (or rounded) to 0000.

While this doesn't matter in many cases as the normal timeout is a
week, there are tokens stored there that are much shorter than 2 hours
(for mysql) or 2 minutes for postgres.

I discovered this while adding a test for
BasicDatabase::updateTimestamp.  The test creates a session with a
timestamp value of now-62seconds. Then I call updateTimestamp.

Since I crafted the timestamp to be more than 1 minute old,
updateTimestamp should update the timestamp to now.

However because of the truncation/rounding, the timestamp difference
is not less than "now - 60 seconds" and the update isn't done and the
test fails.

Changing the schema to use double for mysql and float for postgres
produces a value that has 15 digits of precision and the test has run
150 times without failure.

So looks like 2.3.0 will have another schema update.

sqlite stored 15 significant digits and anydbm stores it as a string
so they store the entire number including some/all the decimal places.

----------
assignee: rouilj
components: Database
messages: 7617
nosy: rouilj
priority: high
severity: major
status: new
title: Timestamps are truncated in mysql and postgresql for session and otk database tables.
type: behavior
versions: 2.2.0

_________________________________________________
Roundup tracker <[email protected]>
<https://issues.roundup-tracker.org/issue2551223>
_________________________________________________