On Sun, 25 Mar 2012 16:41:09 -0700 (PDT), [email protected] 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?
