database filter with datetime ranges

[ZebZiggle]

Howdy!

Starting with something like this:

start = 9pm on Monday
end = 3am on Tuesday

table.objects.filter(moment__range=(start, end))

Only brings back data until midnight on Monday.

Shouldn't range use the time component of a datetime?

Am I missing something?

Thx in advance,
Sandy

[ZebZiggle]

Further investigation ... the sql looks good I think:

...
FROM ...
WHERE ( ...
AND "igapp_stockticker"."moment" < 2007-06-14 05:00:00
AND "igapp_stockticker"."moment" >= 2007-06-13 05:00:00)

Is there some special markup required for postgres to utilize the time
info?

Baffled.

PS> Yes, the data exists.

[Nis Jorgensen]

ZebZiggle wrote:
> Further investigation ... the sql looks good I think:
>
> ...
> FROM ...
> WHERE ( ...
> AND "igapp_stockticker"."moment" < 2007-06-14 05:00:00
> AND "igapp_stockticker"."moment" >= 2007-06-13 05:00:00)
>
> Is there some special markup required for postgres to utilize the time
> info?
>

That looks very strange. Date literals are supposedly required to be in
single quotes in postgresql.

Nis

[ZebZiggle]

Yes, and it gets worse. I changed my query to be:

ticks = list(StockTicker.objects.filter(exchange = 1) \
.extra(where = ["symbol = '@Gold' AND moment >= '%s' AND
moment < '%s' " % (postgresDate(start), postgresDate(end))]) \
.order_by('moment'))

(which works directly in psql)

But the single quotes are being escaped to \' blah \' which again
causes the query to fail. Is there a way to turn the escaping off?

I may have to go down to raw sql for the whole query.

-Sandy

[ZebZiggle]

Actually ... scratch that last point ... my bad. I think I'm onto the
solution and the above query seems fine.

But there's definitely a django bug in there somewhere. I'll submit it
later.

[Malcolm Tredinnick]

What you see is not quite what you (or the database) gets, in this case.

The SQL printed by Django in its debug log is not precisely what is sent
to the backend. There is still a layer of quoting that is done by the
database wrapper (psycopg or MySQLdb, etc) and we print out the debug
SQL *before* that happens.

So what is sent to the database is correctly quoted. Otherwise every
datetime query in Django would fail.

Regards,
Malcolm