Re: [SQLObject] Distinct value lookup on DateTimeCol()

[F.A. P. <f_...@ly...>]

Hi All in this thread.


I could not let go.
Some break through.
I found something which works for me.
So with the proper set of if's and but's.

My Database backend is SQLite.

This is my data class, ok Birthday should have been Birthdate, but 
what's in a name.

class Birthday(sqlobject.SQLObject):
     day = sqlobject.IntCol()
     month = sqlobject.IntCol()
     year = sqlobject.IntCol()
     bdate=sqlobject.DateTimeCol()

I have filled the database with some data.

When I do this: [ I only changed the layout to be more readable here][Db 
is the prompt of the Debuger]

Db> list(Birthday.select())

[<Birthday 1 day=11 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 2 day=12 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 3 day=11 month=9 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 4 day=11 month=9 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 5 day=10 month=10 year=1951 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 6 day=6 month=7 year=1968 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 7 day=29 month=1 year=1974 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 8 day=12 month=8 year=1977 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 9 day=2 month=2 year=1979 datetype=None 
bdate='datetime.datetime...)'>]

These are all the rows. check==Ok!

When I do this:
Db> list(Birthday.select(func.substr(Birthday.q.bdate,1,4)=='1947'))

[<Birthday 1 day=11 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 2 day=12 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 3 day=11 month=9 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 4 day=11 month=9 year=1947 datetype=None 
bdate='datetime.datetime...)'>]

I get only the year 1947, check==Ok!!!

When I do this:
Db> list(Birthday.select(func.substr(Birthday.q.bdate,6,2)=='08'))

[<Birthday 1 day=11 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 2 day=12 month=8 year=1947 datetype=None 
bdate='datetime.datetime...)'>,
  <Birthday 8 day=12 month=8 year=1977 datetype=None 
bdate='datetime.datetime...)'>]

THen I get only month == 8   check==Ok!!!!!!!

If it can all be done in an other more efficient way I would be glad to 
hear.

Thanks for being patience and thinking with me.


Frans.