I spent the better part of the day struggling with Oracle and unsurprisingly
nothing works :(
Given this model:
class Event(models.Model):
dt = models.DateTimeField()
I'm trying to implement this query with time zone support enabled:
Event.objects.filter(dt__month=1)
That's a test from Django's test suite:
https://github.com/aaugustin/django/blob/queryset-datetimes/tests/modeltests/timezones/tests.py#L424
----------------------------------------
I have found a syntax that should work, but it crashes Oracle.
>>> import cx_Oracle
>>> conn = cx_Oracle.connect('django', 'Im_a_lumberjack',
>>> cx_Oracle.makedsn('127.0.0.1', 1521, 'orcl'))
>>> c = conn.cursor()
>>> c.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
>>> NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
>>> c.execute("""INSERT INTO "TIMEZONES_EVENT" ("DT") VALUES (:arg0)""",
>>> ['2010-12-31 22:30:00'])
>>> c.execute("""SELECT FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg) FROM
>>> "TIMEZONES_EVENT" """, ['Africa/Nairobi']).fetchall()
[(datetime.datetime(2011, 1, 1, 1, 30),)]
* At this point we have a single object in the database with DT == 2010-12-31
22:30:00 Z == 2011-01-01 01:30:00 +03:00.
* Oracle knows that Africa/Nairobi is +03:00.
>>> c.execute("""SELECT EXTRACT(MONTH FROM CAST((FROM_TZ("DT", 'UTC') AT TIME
>>> ZONE (:arg)) AS DATE)) FROM "TIMEZONES_EVENT" """,
>>> ['Africa/Nairobi']).fetchall()
[(1,)]
* This expression dutifully extracts the month in local time.
* The time zone name is passed in argument.
>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM
>>> CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE ('Africa/Nairobi')) AS DATE)) =
>>> 1""").fetchall()
[(datetime.datetime(2010, 12, 31, 22, 30),)]
* That's almost what I want, but the time zone name is hardcoded. You'll see
why in a second.
>>> c.execute("""SELECT "DT" FROM "TIMEZONES_EVENT" WHERE EXTRACT(MONTH FROM
>>> CAST((FROM_TZ("DT", 'UTC') AT TIME ZONE (:arg)) AS DATE)) = 1""",
>>> ['Africa/Nairobi']).fetchall()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
* That's exactly the same query as above, except the time zone name is passed
in argument.
* Not only does it crash, but it closes the database connection!
Obviously I'm hitting a bug in Oracle. Does anyone have ideas to solve this?
----------------------------------------
One alternative is the use string interpolation to put the time zone name in
the query. (It works; that's how I first implemented the feature.)
To avoid SQL injection via this parameter, heavy-handed sanitization of the
time zone name will be necessary, like r'^[\w/+-]+$'.
Pro:
- Removes the hack in DateTimeSQLCompiler to inject the time zone parameter.
- Works around the bug in Oracle.
Cons:
- Prevents using arbitrary time zone names. This was discussed at length on the
ticket , but I don't know how much of a problem it is in practice. Windows has
non-standard, localized time zone names; do databases use them?
--
Aymeric.
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
