Opened 10 years ago
Closed 8 years ago
#24194 closed Bug (invalid)
Oracle Backend Operations
Reported by: | kl4us | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.7 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
My Object have a filed named timestamp:
timestamp = models.DateTimeField(auto_now_add=True)
When I use a function like:
from django.db import connection extract_hour = connection.ops.datetime_trunc_sql('hh24','timestamp', None) # None because USE_TZ = False startdate = datetime.datetime.now() - datetime.timedelta(days=1) MyObject.objects.filter(timestamp__gt=startdate).extra({'hour':extract_hour}).values("hour", "status").annotate(Count('pk')).order_by('hour')
the result error is:
DatabaseError: ORA-00907: missing right parenthesis
I'm using django 1.7.3 and python 2.7.
Change History (10)
comment:1 by , 10 years ago
Summary: | Oracle Backend Oprations → Oracle Backend Operations |
---|
comment:2 by , 10 years ago
comment:3 by , 10 years ago
Yes sir
SELECT (('timestamp', [])) AS "HOUR", "SMS_SMS"."STATUS", COUNT("SMS_SMS"."ID") AS "PK__COUNT" FROM "SMS_SMS" WHERE "SMS_SMS"."TIMESTAMP" > 2015-01-20 15:48:17.746855 GROUP BY "SMS_SMS"."STATUS", ('timestamp', []), (('timestamp', [])) ORDER BY "HOUR" ASC
comment:4 by , 10 years ago
I think the problem was return sql, [] in datetime_trunc_sql and in all datetime_*_sql function.
If i use
extract_hour = connection.ops.datetime_trunc_sql('hour','timestamp', None) startdate = datetime.datetime.now() - datetime.timedelta(days=1) status_report_2 = MyObject.objects.filter(timestamp__gt=startdate).extra({'hour':extract_hour[0]}).values("hour", "status").annotate(Count('pk')).order_by('hour')
with only the sql of return sql, [] all works fine.
comment:5 by , 10 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:6 by , 10 years ago
Actually, this ticket appears to require substantial corrections to its summary and description.
First of all, connection.ops.datetime_trunc_sql
is internal, undocumented API. For this, my initial reaction was to want to close this ticket as invalid.
However, there is a real issue here -- connection.ops.date_trunc_sql
and connection.ops.datetime_trunc_sql
behave differently: The former returns just sql, and the latter sql and params. This is a wart and should be fixed, even though it is internal API. Also, I haven't reviewed other methods -- I'm not sure about the extent of the required clean-up.
However, the problem is general, and not Oracle-specific. The Oracle-specific parts of the description -- including even the name of the field (fields with keyword names are known to be problematic on Oracle) -- are red herrings.
Since correcting this ticket according to my understanding would involve a complete rewrite, I'm just leaving this comment here for now, waiting for approval or rebuttal from the original poster or others.
comment:7 by , 10 years ago
Shaib,
you are right and my english is bad. Ok, the fields with keyword names are known to be problematic on Oracle, but in this case the problem is (('my_datetime_field', [])) in datetime_extract_sql and datetime_trunc_sql functions with Orcle backend. I didn't test with other backend.
Ok, those are internal function, I felt it was only fair.
comment:8 by , 8 years ago
I created separate ticket for unify datetime functions behavior -> #27802.
comment:9 by , 8 years ago
IMO ticket doesn't contain bug report and it should be closed as invalid. connection.ops.datetime_trunc_sql
is a part of internal API that works properly. If you take only sql from result tuple then everything works fine:
>>> extract_hour,_ = connection.ops.datetime_trunc_sql('hh24','sth', _get_timezone_name(get_current_timezone())) >>> print(extract_hour) CAST(TO_DATE(TO_CHAR((FROM_TZ(sth, '0:00') AT TIME ZONE 'Europe/Warsaw'), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)
comment:10 by , 8 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
Can you show the generated SQL?
You can obtain it with: