Discrepancy in Record Count Between Django ORM and Raw SQL Query
I'm encountering an issue where the count of records returned by a Django ORM query does not match the count returned by a raw SQL query. Here is the relevant part of my Django view:
start_date = datetime(2024, 10, 19, 0, 0, 0)
end_date = datetime(2024, 10, 19, 23, 59, 59)
dbug = Reservations.objects.all().filter(updated_at__range=(start_date, end_date))
print(dbug.count())
Above returns 6529
The Django settings.py contains:
TIME_ZONE = 'Asia/Tehran'
USE_TZ = False
I have tried SQL query same bellow:
SELECT COUNT(*) FROM "consultant_reservations"
WHERE updated_at BETWEEN '2024-10-19 00:00:00' AND '2024-10-19 23:59:59';
count
-------
6540
(1 row)
Here is discrepancy within SQL query result (which is 6540) I have tried in psql terminal and Django ORM result (which is 6529)
Please let me presenting an example:
Trying SQL query same as:
SELECT * FROM "consultant_reservations"
WHERE updated_at BETWEEN '2024-10-19 00:00:00' AND '2024-10-19 23:59:59' LIMIT 4;
Result:
id | idd | voip_number | client_id | client_mobile | reserve_duration | status | reserve_timestamp | created_at | updated_at | consultant_id_id | mobile_id | created_by | updated_by
-------+---------+-------------+-----------+---------------+------------------+----------+------------------------+------------------------+------------------------+------------------+-----------+------------+------------
76407 | 2011050 | 2217 | 1101151 | 09355648120 | 3600 | reserved | 2024-10-19 19:30:00+00 | 2024-10-14 08:40:03+00 | 2024-10-19 20:28:01+00 | 5052 | 2395781 | 3445 | 0
1408 | 1958653 | 1119 | 754939 | 09142477905 | 3600 | reserved | 2024-10-19 05:30:00+00 | 2024-09-28 06:17:04+00 | 2024-10-19 06:28:01+00 | 3791 | 974986 | 87 | 0
1514 | 1958759 | 2571 | 947805 | 09334143576 | 3600 | reserved | 2024-10-19 09:30:00+00 | 2024-09-28 06:34:05+00 | 2024-10-19 10:28:01+00 | 5374 | 1711586 | 3802 | 0
60371 | 1997347 | 2589 | 1070143 | 09033927800 | 3600 | reserved | 2024-10-19 12:30:00+00 | 2024-10-09 11:42:37+00 | 2024-10-19 13:28:02+00 | 5385 | 2279104 | 3814 | 0
(4 rows)
Trying Django query same as:
start_date = datetime(2024, 10, 19, 0, 0, 0)
end_date = datetime(2024, 10, 19, 23, 59, 59)
dbug = Reservations.objects.all().filter(updated_at__range=(start_date, end_date))[:4]
data = list(dbug.values())
df = pd.DataFrame(data)
print(df.head(4))
Result is:
id idd consultant_id_id voip_number client_id client_mobile ... status reserve_timestamp created_at created_by updated_by updated_at
0 76407 2011050 5052 2217 1101151 09355648120 ... reserved 2024-10-19 23:00:00 2024-10-14 12:10:03 3445 0 2024-10-19 23:58:01
1 1408 1958653 3791 1119 754939 09142477905 ... reserved 2024-10-19 09:00:00 2024-09-28 09:47:04 87 0 2024-10-19 09:58:01
2 1514 1958759 5374 2571 947805 09334143576 ... reserved 2024-10-19 13:00:00 2024-09-28 10:04:05 3802 0 2024-10-19 13:58:01
3 60371 1997347 5385 2589 1070143 09033927800 ... reserved 2024-10-19 16:00:00 2024-10-09 15:12:37 3814 0 2024-10-19 16:58:02
[4 rows x 14 columns]
Shows that the encountered discrepancy within sql result between orm one.
The consultant_reservations table is appropriated the Reservation model.
Regards.