Oracle Backend Operations

[kl4us]

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.

[Aymeric Augustin]

Can you show the generated SQL?

You can obtain it with:

qs = MyObject.objects.filter....   # the failing query
print(qs.query)

[kl4us]

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

[kl4us]

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.

[Shai Berger]

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.

[kl4us]

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.

[Mariusz Felisiak]

I created separate ticket for unify datetime functions behavior -> #27802.

[Mariusz Felisiak]

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)