sqlobject-discuss

[SQLObject] Distinct value lookup on DateTimeCol()

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

Hi All,

I have/had this class:

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

The choice for IntCol above DateTimeCol was simply not to complicate my 
very first attempt to sqlobject too much.

As you see in my app I handle dates as three int's
A bonus to this is that I can do queries on distinct values with:

query=sqlobject.sqlbuilder.Select(Birthday.q.month,groupBy=Birthday.q.month)
     resultlist=Birthday._connection.queryAll(str(query))

As my app grew with options I thought date math could be better done 
with DateTime functions.
I changed the class to:

class Birthday(sqlobject.SQLObject):
     date = sqlobject.DateTimeCol(default=mx.DateTime.now())


Now, I have all the date calculations available but...
how do I do my distinct value lookup on day, month and year?

Are there solutions other than converting the int's to a DateTime object 
before doing the date calculations?


Thanks.



Frans.

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

[Oleg B. <ph...@ph...>]

On Wed, Jun 13, 2007 at 11:16:14AM +0200, F.A. Pinkse wrote:
> class Birthday(sqlobject.SQLObject):
>      date = sqlobject.DateTimeCol(default=mx.DateTime.now())

   Calling now() means the default will be calculated by Python once at the
class creation time (usually during import). You certainly want

     date = sqlobject.DateTimeCol(default=mx.DateTime.now)

   This way Python passes to SQLObject a callable, and SQLObject will call
it at a row creation time.

> Now, I have all the date calculations available but...
> how do I do my distinct value lookup on day, month and year?

   Using date/time functions that are provided by the database backend.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

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

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

Hi Oleg,


Oleg Broytmann wrote:
> On Wed, Jun 13, 2007 at 11:16:14AM +0200, F.A. Pinkse wrote:
>> class Birthday(sqlobject.SQLObject):
>>      date = sqlobject.DateTimeCol(default=mx.DateTime.now())
> 
>    Calling now() means the default will be calculated by Python once at the
> class creation time (usually during import). You certainly want
> 
>      date = sqlobject.DateTimeCol(default=mx.DateTime.now)
> 
>    This way Python passes to SQLObject a callable, and SQLObject will call
> it at a row creation time.
> 
>> Now, I have all the date calculations available but...
>> how do I do my distinct value lookup on day, month and year?
> 
>    Using date/time functions that are provided by the database backend.
> 
> Oleg.

Thanks for your remark. You are right about the timestamp.
I do not need this timestamp but it came up as a fix in my early stages 
for an error message when I used: [ I think it needed something in the ()]

from DateTime import DateTime
and in my class:

date=DateTimeCol()


Now that I have added:

from mx import DateTime
if mxdatetime_available:
     col.default_datetime_implementation = MXDATETIME_IMPLEMENTATION

this error does not show up.

As a result of your remark I have cleaned my code to:

class Birthday(sqlobject.SQLObject):
     date = DateTimeCol()



Ok I have to read the backend manual for that.
To see what the benefits are.

Frans.

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

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

Hi Oleg,

I found nothing on SQLite to access the subfields of a datetime object.
Only datetime math is supported by SQLite not as a data type in the 
database.

I moved back to:

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

This gives me back the select on year,month and day.

IF you think the above move is a bad one after all, I would love to hear 
your idea's.


Thanks,


Frans.

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

[Jaime W. <pro...@gm...>]

If you just want to select on a date using datetime, something like the code
below ought to work.  Can anyone explain how you could query based on a
specific year?  For instance, how would you select all birthdays where year
is 1974?

import datetime
from sqlobject import *

sqlhub.processConnection = connectionForURI('sqlite:/:memory:')

class Birthday(SQLObject):
    birthdate = DateTimeCol(notNone = True)

# Create the table, then load in some values.
Birthday.createTable()
Birthday(birthdate = datetime.datetime(1974, 4, 12))
Birthday(birthdate = datetime.datetime(1974, 1, 8))
Birthday(birthdate = datetime.datetime(1984, 3, 22))

# Find exact birthdays (day/month/year) combinations.
bday = Birthday.select(Birthday.q.birthdate == datetime.datetime(1974, 04,
12))[0]


hth,
jw

On 6/17/07, F.A. Pinkse <f_...@ly...> wrote:
>
> Hi Oleg,
>
> I found nothing on SQLite to access the subfields of a datetime object.
> Only datetime math is supported by SQLite not as a data type in the
> database.
>
> I moved back to:
>
> class Birthday(sqlobject.SQLObject):
>      day = IntCol()
>      month = IntCol()
>      year = IntCol()
>
>
> This gives me back the select on year,month and day.
>
> IF you think the above move is a bad one after all, I would love to hear
> your idea's.
>
>
> Thanks,
>
>
> Frans.
>
>
>
>
>
>
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



-- 
"Government does not solve problems; it subsidizes them."
Ronald Reagan

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

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

Hi Jaime,


Jaime Wyant wrote:
> If you just want to select on a date using datetime, something like the 
> code below ought to work.  Can anyone explain how you could query based 
> on a specific year?  For instance, how would you select all birthdays 
> where year is 1974?
> 


 From an earlier discussion I copied this and modified it a little:

events = Event.select ( AND
			( Event.q.date >= datetime ( yearstart, monthstart, daystart),
                            Event.q.date <= datetime ( yearend, 
monthend,dayend) 					) )

In your case you have to substitute
1974 for yearstart
1 for monthstart
1 for daystart

1974 for yearend
12 for monthend
31 for dayend


This is untested because my little app is recovering form a 'transplant' :-)


Frans.

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

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

Hi,

Oeps, I did hit the send button to soon and forgot to add this.

So the year part is solved but now what can I do when I want to 
construct a monthly birthday calender.
As in who celebrates his birthday in the month of june?
Or who do I send a postcard for their birthday next week?

Setting a start and end date in the select?
How would one do that with a DateTimeCol()?

With best regards,


Frans.

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

[TiNo <ti...@gm...>]

Howabout:

yearstart = 1000
yearend = 3000
(or if you really want it time-independant: yearstart = date.today()[0] -
200, yearend = yearstart + 400 oid)
monthstart = montend = june
daystart = 1
dayend = 31

??

Next week is a little more complicated, but you can do the it the same way,
you just have to find the dates for what you call 'next week'...

TiNo

2007/6/18, F.A. Pinkse <f_...@ly...>:
>
> Hi,
>
> Oeps, I did hit the send button to soon and forgot to add this.
>
> So the year part is solved but now what can I do when I want to
> construct a monthly birthday calender.
> As in who celebrates his birthday in the month of june?
> Or who do I send a postcard for their birthday next week?
>
> Setting a start and end date in the select?
> How would one do that with a DateTimeCol()?
>
> With best regards,
>
>
> Frans.
>
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

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

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

Hi TiNo,



To comment on your solution.

  #tested.
Your approach results in all months between the two dates given and not 
between the month given of each individual year between the dates given.


With best regards,

Frans.TiNo wrote:
> Howabout:
> 
> yearstart = 1000
> yearend = 3000
> (or if you really want it time-independant: yearstart = date.today()[0] 
> - 200, yearend = yearstart + 400 oid)
> monthstart = montend = june
> daystart = 1
> dayend = 31
> 
> ??
> 
> Next week is a little more complicated, but you can do the it the same 
> way, you just have to find the dates for what you call 'next week'...
> 
> TiNo
> 
> 2007/6/18, F.A. Pinkse <f_...@ly... <mailto:f_...@ly...>>:
> 
>     Hi,
> 
>     Oeps, I did hit the send button to soon and forgot to add this.
> 
>     So the year part is solved but now what can I do when I want to
>     construct a monthly birthday calender.
>     As in who celebrates his birthday in the month of june?
>     Or who do I send a postcard for their birthday next week?
> 
>     Setting a start and end date in the select?
>     How would one do that with a DateTimeCol()?
> 
>     With best regards,
> 
> 
>     Frans.
> 
> 
> 
>     -------------------------------------------------------------------------
> 
>     This SF.net email is sponsored by DB2 Express
>     Download DB2 Express C - the FREE version of DB2 express and take
>     control of your XML. No limits. Just data. Click to get it now.
>     http://sourceforge.net/powerbar/db2/
>     _______________________________________________
>     sqlobject-discuss mailing list
>     sql...@li...
>     <mailto:sql...@li...>
>     https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 
> 
> 
> ------------------------------------------------------------------------
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

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.