Django raw sql with datetime

Question

I use postgres for my django project, and for complicated queries to db i use connection.cursor(). Today I have a problem with raw sql query with datetime in filter:

    with connection.cursor() as cursor:

        cursor.execute(
            "SELECT * from orders_orderstatus WHERE datetime > '2017-09-05 16:07:16'"
        )
        row = [item[0] for item in cursor.fetchall()]
    return row

As result we have empty list. But if I query this from psql console, I see that result is not empty:

SELECT * FROM orders_orderstatus WHERE datetime > '2017-09-05 16:07:16';

id  |       status       |           datetime            
----+--------------------+-------------------------------+
256 | created    | 2017-09-05 16:10:59.602091+07
257 | delivered  | 2017-09-05 16:11:00.834547+07 
258 | created    | 2017-09-05 16:11:03.499364+07 

Why django doesn't receive this results?

Answer 1

This is show your how python and psql interpret datetime's strings tzinfo.

psql using your string time as UTC. python send it to db with +hours_of_your_time_zone

If your tz +07 so try in python:

with connection.cursor() as cursor:

    cursor.execute(
        "SELECT * from orders_orderstatus WHERE datetime > '2017-09-05 09:07:16'"
    )
    row = [item[0] for item in cursor.fetchall()]
return row

In future try to use datetime objects with tz.

Looks like you have setting:

USE_TZ=True