Extracting seconds also returns fractional seconds on PostgreSQL and Oracle.

[Joey Lange]

In the Django documentation (​https://docs.djangoproject.com/en/4.0/ref/models/querysets/#second) the __second extractor is implied to be dealing with integer values for second in a date time. In at least PostgreSQL, the values from EXTRACTing second from a date time will include "any fractional seconds" (​https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT) which means it could be more specific than an integer.

Because of the implication in Django's documentation, I had expected this filter to work as expected for a row whose date_created and date_modified are within a second of each other but a couple of milliseconds off:

.filter(date_created__second=F("date_modified__second"))

However, that ends up not being true given the Postgres behavior.

My recommendation is merely a documentation update to highlight the discrepancy of behavior between a value coalesced to Python from the DB data, and how the column values are perceived on the database side within a query. I'm struggling for adequate language to explain in such a context, though :-P happy to discuss further and work together toward this improvement.

[Mariusz Felisiak]

Thanks for the report! We should have consistent behavior on all backends. What do you think about adding DATE_TRUNC() in this case? For example:

django/db/backends/postgresql/operations.py

@@ -36 +36 @@
             return "EXTRACT('isodow' FROM %s)" % field_name
         elif lookup_type == 'iso_year':
             return "EXTRACT('isoyear' FROM %s)" % field_name
+        elif lookup_type == 'second':
+            return f"EXTRACT('second' FROM DATE_TRUNC('second', {field_name}))"
         else:
             return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
 

[Mohamed Nabil Rady]

​https://github.com/django/django/pull/15450

[Tim Graham]

"Ready for checkin" is set by a patch reviewer (and there's no need to set "Easy pickings").

[Mariusz Felisiak <felisiak.mariusz@…>]

In b7f26355:

Refs #33517 -- Prevented second lookup from returning fractional seconds on PostgreSQL.

[Mariusz Felisiak]

This is still an issue on Oracle. We could use EXTRACT(SECOND FROM CAST({field_name} AS TIMESTAMP(0))) but it won't work with DurationField.

[Joey Lange]

Great suggestion, Mariusz! You've convinced me that consistent and clear behavior is more important here! Let me know if I can be of assistance as this moves forward -- sorry I missed the earlier notification(s).

[Yash Singhal]

hey joey Lange, I'm interested in solving this issue, can I solve it, or its already been assigned to someone?

[Joey Lange]

Looks like we're already PR'd and merged, pal! Thanks for the initiative, though!

​https://github.com/django/django/pull/15450

[Mariusz Felisiak]

This is still an issue on Oracle, see comment.

[Yash Singhal]

@joey Lange, "it is still an issue on oracle" as told by @Mariusz Felisiak, so can I work on this?

[Joey Lange]

Truthfully I'm just the reporter of this issue, I have no official stake in the maintenance of Django as a whole -- so, go ahead, though I'm not necessarily the person (if there is one) to be giving permission.

[Carlton Gibson]

Hi Yash — you don't need permission. Please do take a look! Thanks

[Yash Singhal]

Hey Mariusz Felisiak, can you please guide me, like on which file I can found this and what the code is look like and do I need to create a test too? As I'm new to it so I have a little knowledge about this.

[Hisham Mahmood]

Pull request: ​https://github.com/django/django/pull/17769

[GitHub <noreply@…>]

In bbfbf0a:

Refs #33517 -- Prevented second lookup from returning fractional seconds on Oracle.