postgresql: Loss of precision on large timestamps

mentioned in merge request !1164 (merged)

I also got hit by that issue in another test, namely swh/storage/tests/test_postgresql_flavor_mirror.py::TestStorage::test_revision_add_get_arbitrary.

Fortunately, the issue does not come from PostgreSQL but from Python that is rounding the number of seconds when calling datetime.timestamp() in db_to_date conversion function.

$ python
Python 3.12.3 (main, Jan 17 2025, 18:03:48) [GCC 13.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import iso8601
>>> from swh.storage.postgresql.converters import db_to_date
>>> date = iso8601.parse_date('3058-10-26 03:46:08.999997+00:00')
>>> date
datetime.datetime(3058, 10, 26, 3, 46, 8, 999997, tzinfo=datetime.timezone(datetime.timedelta(0), '+00:00'))
>>> offset = b'+0000'
>>> db_to_date(date, offset)
TimestampWithTimezone(timestamp=Timestamp(seconds=34359738369, microseconds=999997), offset_bytes=b'+0000')
>>> date.timestamp()
34359738369.0

I guess we could remove the microseconds before calling timestamp() to avoid the rounding ?

This rounding issue seems only to occur for very particular edge cases:

year is greater than 3057
there is no rounding when the datetime is naive

$ python
Python 3.12.3 (main, Jan 17 2025, 18:03:48) [GCC 13.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from datetime import datetime, timedelta, timezone
>>> date = datetime(3058, 10, 26, 3, 46, 8, 999997, tzinfo=timezone(timedelta(0), '+00:00'))
>>> date.timestamp()
34359738369.0
>>> date = datetime(3057, 10, 26, 3, 46, 8, 999997, tzinfo=timezone(timedelta(0), '+00:00'))
>>> date.timestamp()
34328202368.999996
>>> date = datetime(3059, 10, 26, 3, 46, 8, 999997, tzinfo=timezone(timedelta(0), '+00:00'))
>>> date.timestamp()
34391274369.0
>>> date = datetime(3058, 10, 26, 3, 46, 8, 999997)
>>> date.timestamp()
34359731168.999996

the issue does not come from PostgreSQL but from Python that is rounding the number of seconds

That's reassuring in a way, but still...

I got curious how was implemented the timestamp method and found some interesting things.

First we can see that if the datetime is naive, a different computation is performed involving a division between an int and a float.

For a datetime with timezone, the substraction between it and _EPOCH whose value is datetime(1970, 1, 1, tzinfo=timezone.utc) and the total_seconds method is called on the produced timedelta to get the float timestamp.

This is where it becomes interesting, that method performs a division between two integers but as the / operator is used, a float division is made. With an edge case datetime like the one in the example above, if we force one operand of the division to be a float type, the computed value is the expected one, see below.

$ python
Python 3.12.3 (main, Jan 17 2025, 18:03:48) [GCC 13.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from datetime import datetime, timedelta, timezone
>>> EPOCH = datetime(1970, 1, 1, tzinfo=timezone.utc)
>>> date = datetime(3058, 10, 26, 3, 46, 8, 999997, tzinfo=timezone(timedelta(0), '+00:00'))
>>> diff = (date - EPOCH)
>>> ((diff.days * 86400 + diff.seconds) * 10**6 + diff.microseconds) / 10**6
34359738369.0
>>> ((diff.days * 86400.0 + diff.seconds) * 10**6 + diff.microseconds) / 10**6
34359738368.99999

Looks like the way Python performs a float division between integers under the hood implies some loss of precision.

The total_seconds implementation is in place since 15 years by the way.

Well, I guess by default the C implementation of timedelta.total_seconds is used by the Python interpreter but there is the same issue, both parameters of PyNumber_TrueDivide are Python integers.

As with the Python implementation, the rounding issue can be fixed by forcing one operand of the division to a float. I could fix the issue using that simple diff:

diff --git a/Modules/_datetimemodule.c b/Modules/_datetimemodule.c
index 8b202cc5178..a16e3cb676e 100644
--- a/Modules/_datetimemodule.c
+++ b/Modules/_datetimemodule.c
@@ -2973,10 +2973,12 @@ delta_total_seconds(PyObject *self, PyObject *Py_UNUSED(ignored))
     PyObject *current_mod = NULL;
     datetime_state *st = GET_CURRENT_STATE(current_mod);
 
-    total_seconds = PyNumber_TrueDivide(total_microseconds, CONST_US_PER_SECOND(st));
+    PyObject *st_float = PyNumber_Float(CONST_US_PER_SECOND(st));
+    total_seconds = PyNumber_TrueDivide(total_microseconds, st_float);
 
     RELEASE_CURRENT_STATE(st, current_mod);
     Py_DECREF(total_microseconds);
+    Py_DECREF(st_float);
     return total_seconds;
 }

This does not break the cpython tests.

mentioned in merge request !1167 (merged)

closed with merge request !1167 (merged)

mentioned in commit 9db347ae

postgresql: Loss of precision on large timestamps

Designs

Child items ...

Activity