I have a table called Opinion in PostgreSQL database that contains a couple of columns. However, the relevant ones are:

• report_version_id, represents an integer type
• content_created_at, represents a timestampz type
• assigned, represents a bool type

Let be this query:

select count(*) 
from opinion o 
where report_version_id = 14 
and assigned = true 
and date_trunc('day', content_created_at) = '2021-05-23';

using DBeaver client, I get a count: 374, but when using SQLAlchemy (v1.4.11) in a Python script (v3.7) I get another count, 372. This is the code used in Python:

return (session.query(Opinion).filter(func.date_trunc('day', Opinion.content_created_at) == date)
        .filter(Opinion.report_version_id == report_version_id,
                Opinion.assigned == True)
        .count())

I have played around and doing counts without using dates everything matches, so my guessing is 1) there is something I'm doing wrong and don't know, or 2) there is a bug in this version when dealing with dates in filter.

I'm completely sure I'm connected to the same database, and no transactions have been executed while connected from the 2 different sources (DBeaver client and Terminal in local from where I'm running the Python script).

Edit:

This is the query I get when compiling it:

SELECT opinion.report_version_id ...
FROM opinion 
WHERE date_trunc(%(date_trunc_1)s, opinion.content_created_at) = %(date_trunc_2)s 
AND opinion.report_version_id = %(report_version_id_1)s 
AND opinion.assigned = true

As per Anudeep suggested in his answer, I'm adding the literal query:

SELECT opinion.report_version_id ...
FROM opinion 
WHERE date_trunc('day', opinion.content_created_at) = '2021-05-23' 
AND opinion.report_version_id = 14 
AND opinion.assigned = true

I have also compared what I get using the DBeaver client and the script, and I seem to be getting more because there is is a difference in the timezone applied. Records that I'm getting are from the next day (2021-05-24) within 1 hour ahead.

Answer

Check that time zone when connected is the same as the one when this data was inserted into the db. You can set this configuration like this:

creds = {
    "url": f"{DB_ENGINE}://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}",
    "echo": False,
    "connect_args": {
        "options": "-c timezone=utc-1"
    }
}
session = sessionmaker(bind=engine or sqlalchemy.engine_from_config(creds, prefix=""))()