test_date_from_ticks fails

[zanpeeters]

Running pytest I get:

FAILED tests/test_psycopg_dbapi20.py::test_date_from_ticks[0-1970-01-01] - assert datetime.date(1969, 12, 31) in [datetime.date(1970, 1, 1)]

Tested with:

• psycopg 3.2.7
• postgresql 17.4
• python 3.13.3
• macOS 15.4
• Apple M4

This issue was also reported here for riscv64: #883

I think the problem is in TimestampFromTicks(ticks) in dbapi20.py. It uses time.localtime(0) to find the epoch start, with offset to local timezone. This is turned into a timezone-aware datetime object.

I'm in timezone UTC-7 so TimestampFromTicks(0) returns:

datetime.datetime(1969, 12, 31, 17, 0, tzinfo=datetime.timezone(datetime.timedelta(days=-1, seconds=61200)))

DateFromTicks(0) calls .date() on that object, which ignores the timezone and returns datetime.date(1969, 12, 31), which is incorrect.

TimeFromTicks(0) returns datetime.time(17, 0) which is also incorrect. However, in test_time_from_ticks, the hour is forced to 0 so the test passes.

According to the Python documentation (referred to from PEP-249):

The epoch is the point where the time starts, the return value of time.gmtime(0). It is January 1, 1970, 00:00:00 (UTC) on all platforms.

I think the solution is to use time.gmtime(0) in TimestampFromTicks and set the timezone to UTC.

For reference, SQLAlchemy sets epoch like this:

epoch = dt.datetime.fromtimestamp(0, dt.timezone.utc).replace(tzinfo=None)

[dvarrazzo]

Thank you for the info. I can reproduce the issue by moving west of Greenwich:

TZ="America/New_York" pytest "tests/test_psycopg_dbapi20.py::test_date_from_ticks[0-1970-01-01]"
...
FAILED tests/test_psycopg_dbapi20.py::test_date_from_ticks[0-1970-01-01] - assert datetime.date(1969, 12, 31) in [datetime.date(1970, 1, 1)]

I can't remember how that implementation came about. Funnily enough, it was written in New Zealand, so it was close to go so east to become west...

commit be4e3ee85dc773878d67edf0b7880824bf7059aa
Date:   Sat Apr 11 16:54:47 2020 +1200

Would this implementation be correct too?

def TimestampFromTicks(ticks: float) -> dt.datetime:
    epoch = dt.datetime(1970, 1, 1, tzinfo=dt.timezone.utc)
    return epoch + dt.timedelta(seconds=ticks)

It does pass the tests and it is independent from the timezone where it runs:

$ TZ="America/New_York" python -c 'import psycopg; print(psycopg.TimestampFromTicks(0))'
1970-01-01 00:00:00+00:00

It does change the value returned, but I would say that the previous value was wrong and that the TimestampFromTick() is a senescent function, only included to state compatibility with the DBAPI.

I also notice that the function returns the wrong value in psycopg2 too:

$ TZ="America/New_York" python -c 'import psycopg2; print(psycopg2.TimestampFromTicks(0))'
'1969-12-31T19:00:00-05:00'::timestamptz

So this function has been wrong for more than 20 years and nobody complained.

I am almost tempted to disable the test and declare the function wrong for so long that it's now a feature...

Your proposal:

I think the solution is to use time.gmtime(0) in TimestampFromTicks and set the timezone to UTC.

For reference, SQLAlchemy sets epoch like this:

epoch = dt.datetime.fromtimestamp(0, dt.timezone.utc).replace(tzinfo=None)

This doesn't set the timezone to utc, it returns a naive tz:

python -c 'import datetime as dt; print(dt.datetime.fromtimestamp(0, dt.timezone.utc).replace(tzinfo=None))'
1970-01-01 00:00:00

So, we can do one of three things here:

• nothing, and mark the test XFAIL if tzoffset is negative;
• return the correct tz-aware timestamp (seconds from epoch in utc). It means a change in value of the object returned;
• return the correct tz-naive timestamp (seconds from 1970-1-1 midnight). It means a change in type, in a certain sense (the type is the same in python but certain operations might fail, it is different in postgres).

I think the second option is the best one, the first the most conservative.

[dvarrazzo]

I am sorry, but everything I wrote above is wrong 😛 It was pretty late for me yesterday and I was busy with other bigger fishes to fry too...

TimestampFromTicks has been correct all the time, both in psycopg 2 and 3: it returns the local version of the epoch, a timestamp equivalent to 1970-1-1T00:00Z in whatever local time is called.

The problem is rather with the implementation of DateFromTicks and TimeFromTicks which return the date or the time by truncating the localized timestamp.

Therefore we can solve the problem by returning the time in TimestampFromTicks as tz-aware with the zone set to UTC. As a consequence, DateFromTicks and TimeFromTicks would return the expected values.

A different way to solve the problem would be to decouple the Date and Time functions from the Timestamp function. However I think that returning different dates and time would be confusing (the epoch timestamp would be in 1969 in NY but the date would be in 1970).

Because we don't change the type of the returned object (a tz-aware datetime) and its value (it was and still would be == 1970-01-01Z) this fix should be safe.

[zanpeeters]

It was pretty late for me yesterday...

Wrong timezone? 😃

Thanks for the replies and fixes. I tested the commit with several different timezones and it all works.

I was going to report another test failure, with timezone set to Asia/Kolkata (India, UTC +5:30) test_time_from_ticks failed on the minutes, but that is also fixed with these changes.