Re: [web2py] Re: DAL or SQL?
On Sun, 25 Mar 2012 16:41:09 -0700 (PDT), niph...@gmail.com said:
> Doh, you're right. All the "datetime" api on fields are extracting, not
> converting the actual value
Yes, and I've also found that, with Sqlite at least, web2py doesn't
carry out datetime arithmetic correctly within the DAL (not entirely
Web2py's fault).
Rather than have another field in the table for duration, which could
easily get out of sync with start/stop times, I created a view instead:
CREATE VIEW v_durations as select
id,f_date,f_start,f_end,strftime('%s',t_periods.f_end)-strftime('%s',t_periods.f_start)
as f_duration,f_category,f_task,f_user from t_periods;
I use that view, rather than the t_periods table, for reporting. It's
defined in the model as migrate=false, and it works really well. If I move
to another database (probably MySQL for production), I only need to create
one new view. This way, edits to the table are no problem, and the
duration from the view is always correct.
--
"You can have everything in life you want if you help enough other people
get what they want" - Zig Ziglar.
Who did you help today?
Re: [web2py] Re: DAL or SQL?
problem is doing it within the db ^_^ Actually your answer is correct, and I need to rectify my example period_to_update = db(db.periods.id==1).select().first() start_time = period_to_update.start_time elapsed = request.now - start_time period_to_update.update_record(end_time=request.now, seconds_elapsed=elapsed.days*24*3600 + elapsed.seconds) Il giorno lunedì 26 marzo 2012 13:29:07 UTC+2, tsvim ha scritto: > > > Not sure if this is relevant, but you can do the following with datetime. > > >>> import datetime > >>> a = datetime.datetime(2007, 12, 6, 16, 29, 43, 79043) > >>> b = datetime.datetime(2011, 10, 3, 12, 0, 0, 0) > >>> c = b-a > >>> c > datetime.timedelta(1396, 70216, 920957) > >>> (c.microseconds + (c.seconds + c.days * 3600 * 24) * 10**6) / 10**6 > 120684616 > > (I have Python 2.6 at work, otherwise you can do c.total_seconds()) > > On Monday, March 26, 2012 1:41:09 AM UTC+2, Niphlod wrote: >> >> Doh, you're right. All the "datetime" api on fields are extracting, not >> converting the actual value >> At this point you can sum up years difference, month difference, etc etc >> etc separately and then extract the time passed by. >> . >> duration_ye = (db.periods.end_time.year() - >> db.periods.start_time.year()).sum() >> duration_mo = (db.periods.end_time.month() - >> db.periods.start_time.month()).sum() >> duration_da = (db.periods.end_time.day() - >> db.periods.start_time.day()).sum() >> . >> >> This will be a "itchy" shot anyway even if you know that the months >> difference is "1" you can't really say how many days passed >> >> Maybe at this stage is better to save unix time, that is an integer, and >> proceed simply with >> >> duration = (db.periods.end_time - db.periods.start_time).sum() >> >> Or, having the application filling in the seconds elapsed when it >> completes something like >> period_to_update = db(db.periods.id==1).select().first() >> start_time = period_to_update.start_time >> period_to_update.update_record(end_time=request.now, >> seconds_elapsed=(request.now - start_time).seconds) >> >> Sorry for the wrong previous advices, really, I should have tested with >> all kinds of values (instead of 2012-01-01 00:00:00 and 2012-01-01 >> 00:00:15). >> >
Re: [web2py] Re: DAL or SQL?
Not sure if this is relevant, but you can do the following with datetime. >>> import datetime >>> a = datetime.datetime(2007, 12, 6, 16, 29, 43, 79043) >>> b = datetime.datetime(2011, 10, 3, 12, 0, 0, 0) >>> c = b-a >>> c datetime.timedelta(1396, 70216, 920957) >>> (c.microseconds + (c.seconds + c.days * 3600 * 24) * 10**6) / 10**6 120684616 (I have Python 2.6 at work, otherwise you can do c.total_seconds()) On Monday, March 26, 2012 1:41:09 AM UTC+2, Niphlod wrote: > > Doh, you're right. All the "datetime" api on fields are extracting, not > converting the actual value > At this point you can sum up years difference, month difference, etc etc > etc separately and then extract the time passed by. > . > duration_ye = (db.periods.end_time.year() - > db.periods.start_time.year()).sum() > duration_mo = (db.periods.end_time.month() - > db.periods.start_time.month()).sum() > duration_da = (db.periods.end_time.day() - > db.periods.start_time.day()).sum() > . > > This will be a "itchy" shot anyway even if you know that the months > difference is "1" you can't really say how many days passed > > Maybe at this stage is better to save unix time, that is an integer, and > proceed simply with > > duration = (db.periods.end_time - db.periods.start_time).sum() > > Or, having the application filling in the seconds elapsed when it > completes something like > period_to_update = db(db.periods.id==1).select().first() > start_time = period_to_update.start_time > period_to_update.update_record(end_time=request.now, > seconds_elapsed=(request.now - start_time).seconds) > > Sorry for the wrong previous advices, really, I should have tested with > all kinds of values (instead of 2012-01-01 00:00:00 and 2012-01-01 > 00:00:15). >
Re: [web2py] Re: DAL or SQL?
Doh, you're right. All the "datetime" api on fields are extracting, not converting the actual value At this point you can sum up years difference, month difference, etc etc etc separately and then extract the time passed by. . duration_ye = (db.periods.end_time.year() - db.periods.start_time.year()).sum() duration_mo = (db.periods.end_time.month() - db.periods.start_time.month()).sum() duration_da = (db.periods.end_time.day() - db.periods.start_time.day()).sum() . This will be a "itchy" shot anyway even if you know that the months difference is "1" you can't really say how many days passed Maybe at this stage is better to save unix time, that is an integer, and proceed simply with duration = (db.periods.end_time - db.periods.start_time).sum() Or, having the application filling in the seconds elapsed when it completes something like period_to_update = db(db.periods.id==1).select().first() start_time = period_to_update.start_time period_to_update.update_record(end_time=request.now, seconds_elapsed=(request.now - start_time).seconds) Sorry for the wrong previous advices, really, I should have tested with all kinds of values (instead of 2012-01-01 00:00:00 and 2012-01-01 00:00:15).
Re: [web2py] Re: DAL or SQL?
On Mon, 19 Mar 2012 14:45:51 -0700 (PDT), niph...@gmail.com said:
> for every "period", duration is calculated as:
> duration = db.periods.end_time.seconds() -
> db.periods.start_time.seconds()
This is wrong.
I reworked my data model so that the period start and stop fields were of
type datetime. I then found the flaw in your logic above: the seconds()
method returns the value of the seconds part of the datetime rather than
the number of seconds of the duration.
You can see this below. For one row (id==257), these are the values:
So, start at 18:18:54 today, stop at 18:19:01, so the duration should be 6
seconds. However:
>>> duration = db.t_periods.f_end.seconds() -
>>> db.t_periods.f_start.seconds()
result = db(db.t_periods.id==257).select(db.t_periods.id,duration)
>>> for r in result:
... print r
...
at
0xa15e1b4>, 't_periods_archive': ,
0xa15e1b4>'id': 257, 'delete_record': at 0xa15e10c>}>,
0xa15e1b4>'_extra': web2py_extract('second',t_periods.f_start))": -53}>}>
...giving -53 seconds. In other words, it is the difference in the value
of seconds rather than the duration.
--
"You can have everything in life you want if you help enough other people
get what they want" - Zig Ziglar.
Who did you help today?
Re: [web2py] Re: DAL or SQL?
""" In my original posting, I said: "the periods table has id, date, start_time, end_time (and other fields)". I suppose I assumed that the presence of a 'date' field implied that the *_time fields were indeed of type time, but I'm sorry if that wasn't clear. """ my point was: my model implied datetime, your's not, don't blame web2py if my method don't work applied to your model. so, there is an hiccup to your model: time fields don't represent a point in time, it has no sense trying to "subtract" them e.g. a record that has start time 09:00:00 and an end time of 10:00:00 means that it has passed one hour, one hour and one day or one hour and two days(and so on)? Anyway, if you do durations = db.periods.end_time - db.periods.start_time it works. but sqlite actually returns the difference in hours, and seems to round that as an integer.
Re: [web2py] Re: DAL or SQL?
> Perhaps you could add the seconds as a virtual lazy field.
That sounds worth investigating, thanks.
> We shall come to an agreement when you explain plainly what is your real
> model... it seems that your start and end columns are "time" and not
> "datetime"... Time fields don't have a "seconds()" method, only datetime!
In my original posting, I said: "the periods table has id, date,
start_time, end_time (and other fields)". I suppose I assumed that the
presence of a 'date' field implied that the *_time fields were indeed of
type time, but I'm sorry if that wasn't clear. For clarity:
db.define_table('t_periods',
Field('f_date', type='date',
label=T('Date')),
Field('f_period_start', type='time',
label=T('Period Start')),
Field('f_period_end', type='time',
label=T('Period End')),
...
--
"You can have everything in life you want if you help enough other people
get what they want" - Zig Ziglar.
Who did you help today?
Re: [web2py] Re: DAL or SQL?
We shall come to an agreement when you explain plainly what is your real model... it seems that your start and end columns are "time" and not "datetime"... Time fields don't have a "seconds()" method, only datetime!
Re: [web2py] Re: DAL or SQL?
Perhaps you could add the seconds as a virtual lazy field.
On Wednesday, March 21, 2012 11:25:41 PM UTC-7, backseat wrote:
>
> On Mon, 19 Mar 2012 14:45:51 -0700 (PDT), niph...@gmail.com said:
>
> > for every "period", duration is calculated as:
> > duration = db.periods.end_time.seconds() -
> > db.periods.start_time.seconds()
> >
> > now, get a list of all "durations"...
> >
> > result = db(db.periods.id>0).select(db.periods.id, duration)
>
> This does not work: every 'duration' is None:
>
> >>> duration = db.t_periods.f_period_end.seconds() -
> db.t_periods.f_period_start.seconds()
> >>> result = db(db.t_periods.id>0).select(db.t_periods.id, duration)
> >>> result[0]
> at
> 0x923479c>, 't_periods_archive': ,
> 0x923479c>'id': 40, 'delete_record': at 0x9234764>}>,
> 0x923479c>'_extra': 0x923479c>{"(web2py_extract('second',t_periods.f_period_end) -
> 0x923479c>web2py_extract('second',t_periods.f_period_start))": None}>}>
> >>>
>
> All rows give the same result (None).
>
> I can see that what I need to be doing is calculating the end time in
> seconds and subtracting the start time in seconds, but I can't see how to
> do that via the DAL.
>
> Back to my original question: is the only way to do this to use
> hand-crafted SQL?
> --
> "You can have everything in life you want if you help enough other people
> get what they want" - Zig Ziglar.
>
> Who did you help today?
>
>
Re: [web2py] Re: DAL or SQL?
On Mon, 19 Mar 2012 14:45:51 -0700 (PDT), niph...@gmail.com said:
> for every "period", duration is calculated as:
> duration = db.periods.end_time.seconds() -
> db.periods.start_time.seconds()
>
> now, get a list of all "durations"...
>
> result = db(db.periods.id>0).select(db.periods.id, duration)
This does not work: every 'duration' is None:
>>> duration = db.t_periods.f_period_end.seconds() -
db.t_periods.f_period_start.seconds()
>>> result = db(db.t_periods.id>0).select(db.t_periods.id, duration)
>>> result[0]
at
0x923479c>, 't_periods_archive': ,
0x923479c>'id': 40, 'delete_record': at 0x9234764>}>,
0x923479c>'_extra': {"(web2py_extract('second',t_periods.f_period_end) -
0x923479c>web2py_extract('second',t_periods.f_period_start))": None}>}>
>>>
All rows give the same result (None).
I can see that what I need to be doing is calculating the end time in
seconds and subtracting the start time in seconds, but I can't see how to
do that via the DAL.
Back to my original question: is the only way to do this to use
hand-crafted SQL?
--
"You can have everything in life you want if you help enough other people
get what they want" - Zig Ziglar.
Who did you help today?
Re: [web2py] Re: DAL or SQL?
On Wed, 21 Mar 2012 14:43:42 -0700 (PDT), niph...@gmail.com said: > rows = db(db.t_periods.id==40).select(db.t_periods.f_period_end) > for row in rows: > print row.f_period_end.seconds() No, that gives an error: >>> rows = db(db.t_periods.id==40).select(db.t_periods.f_period_end) >>> for row in rows: ... print row.f_period_end.seconds() ... Traceback (most recent call last): File "", line 2, in AttributeError: 'datetime.time' object has no attribute 'seconds' I arrived at this point because your earlier examples were giving None as the duration, and I was trying to narrow down where the problem lay. By the way, thank you for your continuing help, it is very much appreciated. -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
Re: [web2py] Re: DAL or SQL?
Wait "seconds()" is a method of a datetime field when you call the "sum", the method is called, but if you are going to fetch it directly, you must use: rows = db(db.t_periods.id==40).select(db.t_periods.f_period_end) for row in rows: print row.f_period_end.seconds()
Re: [web2py] Re: DAL or SQL?
I'm having problems getting a valid value for seconds() from a time field under Sqlite. I can extract a time: >>> for row in db(db.t_periods.id==40).select(db.t_periods.f_period_end): ... print row ... However, if I try to get the seconds(), it returns None: >>> for row in >>> db(db.t_periods.id==40).select(db.t_periods.f_period_end.seconds()): ... print row ... }> Both Niphlod's examples and the manual suggest that this should give the time in seconds, but I'm getting None. Have I misunderstood? -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
Re: [web2py] Re: DAL or SQL?
I'm using sqlite, and I'm embarrassed about my errors. Thanks for your help. -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
Re: [web2py] Re: DAL or SQL?
PS: by the way, your query is not what I suggested :P assuming the example I posted, you are trying to do durations = db(db.periods>0).select(db.periods.id,duration,groupby=db.periods.task_id) error here is db(db.periods>0) and not db(db.periods*.id*>0) corrected this error, you have another one you can't group for something that is not in the select list nor having something in the select list that is not in the groupby... either durations = db(db.periods.id>0).select(db.periods.task_id,duration,groupby=db.periods.id) #useless or durations = db(db.periods.id>0).select(db.periods.task_id,duration,groupby=db.periods.task_id) #right one!
Re: [web2py] Re: DAL or SQL?
what db engine are you using ? Il giorno martedì 20 marzo 2012 21:55:58 UTC+1, backseat ha scritto: > > I've hit an error (web2py bug?) in Niphlod's well-explained example. > > I *was* running web2py.1.99.4, which gave this: > > >>> duration=(db.t_periods.f_period_end.seconds() - > >>> db.t_periods.f_period_start.seconds()).sum() > >>> result=db(db.t_periods>0).select(db.t_periods.id > ,duration,groupby=db.t_periods.f_task_id) > Traceback (most recent call last): > File "", line 1, in > File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 6333, > in select return self.db._adapter.select(self.query,fields,attributes) > File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 1274, > in select sql = self._select(query, fields, attributes) > File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 1672, > in _select sql = super(SQLiteAdapter, self)._select(query, fields, > attributes) File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", > line 1155, in _select if query and not query.ignore_common_filters: > AttributeError: 'bool' object has no attribute 'ignore_common_filters' > >>> > > Some Googling suggested this problem was fixed after 1.99.4 was released, > so I've just downloaded 1.99.7, which gives: > > >>> durations=(db.t_periods.f_period_end.seconds() > -db.t_periods.f_period_start.seconds()).sum() > > >>> result=db(db.t_periods>0).select(db.t_periods.id > ,durations,groupby=db.t_periods.f_task_id) > Traceback (most recent call last): > File "", line 1, in > File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 7578, > in select return adapter.select(self.query,fields,attributes) > File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1315, > in select rows = response(sql) > File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1305, > in response self.execute(sql) > File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1392, > in execute return self.log_execute(*a, **b) > File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1386, > in log_execute ret = self.cursor.execute(*a, **b) > OperationalError: no such column: True > >>> > > Is this a web2py bug, or am I doing something wrong? > -- > "You can have everything in life you want if you help enough other people > get what they want" - Zig Ziglar. > > Who did you help today? > >
Re: [web2py] Re: DAL or SQL?
I've hit an error (web2py bug?) in Niphlod's well-explained example. I *was* running web2py.1.99.4, which gave this: >>> duration=(db.t_periods.f_period_end.seconds() - >>> db.t_periods.f_period_start.seconds()).sum() >>> result=db(db.t_periods>0).select(db.t_periods.id,duration,groupby=db.t_periods.f_task_id) Traceback (most recent call last): File "", line 1, in File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 6333, in select return self.db._adapter.select(self.query,fields,attributes) File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 1274, in select sql = self._select(query, fields, attributes) File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 1672, in _select sql = super(SQLiteAdapter, self)._select(query, fields, attributes) File "/home/kae/hg/kae/web2py.1.99.4.pytrack2/gluon/dal.py", line 1155, in _select if query and not query.ignore_common_filters: AttributeError: 'bool' object has no attribute 'ignore_common_filters' >>> Some Googling suggested this problem was fixed after 1.99.4 was released, so I've just downloaded 1.99.7, which gives: >>> durations=(db.t_periods.f_period_end.seconds() >>> -db.t_periods.f_period_start.seconds()).sum() >>> result=db(db.t_periods>0).select(db.t_periods.id,durations,groupby=db.t_periods.f_task_id) Traceback (most recent call last): File "", line 1, in File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 7578, in select return adapter.select(self.query,fields,attributes) File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1315, in select rows = response(sql) File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1305, in response self.execute(sql) File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1392, in execute return self.log_execute(*a, **b) File "/home/kae/hg/kae/web2py.1.99.7.pytrack2/gluon/dal.py", line 1386, in log_execute ret = self.cursor.execute(*a, **b) OperationalError: no such column: True >>> Is this a web2py bug, or am I doing something wrong? -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
Re: [web2py] Re: DAL or SQL?
Thanks Niphlod, that looks good and I will try it soon. -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
