sqlobject-discuss

[SQLObject] Date utils

[Philippe N. <sw...@fr...>]

Hi,

I need to sort and count SQLObjects per date. An example might really show
what I want :)

class Article(SQLObject):
    date = DateTimeCol()
    ...
    
    
I have many many Articles, and I'd like to build a Calendar showing how many
articles were published by month and/or year. For instance:

10 articles on 05/22
3 articles on 05/14

I see one durty solution which would like this (untested) :

calendar = {} # keys: day numbers of the month
for article in Article.select(orderBy=Article.q.date):
    if article.getYear() != 2004 or article.getMonth() != 5:
       continue
    try:
        calendar[article.getDay()] += 1
    except KeyError:
        calendar[article.getDay()] = 1
	

it isn't really efficient because it fetches all articles. Is there a clean
way to select SQLObjects given a specific date interval on DateTimeCol ?

Philippe

Re: [SQLObject] Date utils

[Neil Blakey-M. <nb...@mi...>]

On Wed 2004-06-02 (14:05), Philippe Normand wrote:
> I see one durty solution which would like this (untested) :
> 
> calendar = {} # keys: day numbers of the month
> for article in Article.select(orderBy=Article.q.date):
>     if article.getYear() != 2004 or article.getMonth() != 5:
>        continue
>     try:
>         calendar[article.getDay()] += 1
>     except KeyError:
>         calendar[article.getDay()] = 1
> 	
> 
> it isn't really efficient because it fetches all articles. Is there a clean
> way to select SQLObjects given a specific date interval on DateTimeCol ?

I just compare them to a datetime, and that works just fine for me.
Although, there's a bug where the datetime conversion uses '%s' instead
of '%S' for seconds, which probably wouldn't affect your usage.

a = sql.table.acct
if start_date:
    criteria.append(a.stamp_inserted >= start_date)
if end_date:
    criteria.append(a.stamp_inserted < end_date)

Neil
-- 
Neil Blakey-Milner
nb...@mi...
http://mithrandr.moria.org/

Re: [SQLObject] Date utils

[Philippe N. <sw...@fr...>]

On Wed, Jun 02, 2004 at 02:13:58PM +0200, Neil Blakey-Milner wrote:
> > 	
> > 
> > it isn't really efficient because it fetches all articles. Is there a clean
> > way to select SQLObjects given a specific date interval on DateTimeCol ?
> 
> I just compare them to a datetime, and that works just fine for me.
> Although, there's a bug where the datetime conversion uses '%s' instead
> of '%S' for seconds, which probably wouldn't affect your usage.
> 
> a = sql.table.acct
> if start_date:
>     criteria.append(a.stamp_inserted >= start_date)
> if end_date:
>     criteria.append(a.stamp_inserted < end_date)
> 

But it doesn't change the problem, too many objects are fetched from the db.
Or am I missing something ?

I'd like to compare dates directly in select() and not in select() results.
Following your code snippet, I'll try something like:

table.select((Table.q.stamp_inserted >= start_date) &
             (Table.q.stamp_inserted < end_date))

Cheers,

Philippe

Re: [SQLObject] Date utils

[Neil Blakey-M. <nb...@mi...>]

On Wed 2004-06-02 (15:15), Philippe Normand wrote:
> > a = sql.table.acct
> > if start_date:
> >     criteria.append(a.stamp_inserted >= start_date)
> > if end_date:
> >     criteria.append(a.stamp_inserted < end_date)
> > 
> 
> But it doesn't change the problem, too many objects are fetched from the db.
> Or am I missing something ?
> 
> I'd like to compare dates directly in select() and not in select() results.
> Following your code snippet, I'll try something like:
> 
> table.select((Table.q.stamp_inserted >= start_date) &
>              (Table.q.stamp_inserted < end_date))

Sorry, I tend to build up a criteria list, and go:

	table.select(AND(*criteria))

So yes, it will work for select queries just fine.  Just mind the bug if
seconds are at all important to you.

Neil
-- 
Neil Blakey-Milner
nb...@mi...
http://mithrandr.moria.org/