Here is some SQL that might make the burndown chart easier.

[jeremy@…]

Here is some SQL that would eliminate the need to have a cron job or a ticket hook. It just gets the historical times from the ticket_change table.

Feel free to use it or not.

Output

startofday	nice_time	totalhours	estimatedhours	remaininghours
1178582400	05/08/2007	0	319	319
1178668800	05/09/2007	3	320	317
1178755200	05/10/2007	13	319	306
1178841600	05/11/2007	26	317	291
1179100800	05/14/2007	41	316	275
1179187200	05/15/2007	51	306	255

SQL

drop view change_dates;
create view change_dates as select distinct time / 86400 * 86400 as startofday
from ticket_change where field = 'totalhours' or field = 'estimatedhours'
union select cast(strftime('%s', 'now') / 86400 as int) * 86400 as startofday;

drop view totalhours_log;
create view totalhours_log as
select tc.ticket, tc.time as changetime, tc.oldvalue as value
from ticket_change tc
where tc.field = 'totalhours'
union
select t.id as ticket, 9999999999 as changetime, th.value as value
from ticket t LEFT JOIN ticket_custom th on t.id = th.ticket and name = 'totalhours';

drop view estimatedhours_log;
create view estimatedhours_log as
select tc.ticket, tc.time as changetime, tc.oldvalue as value
from ticket_change tc
where tc.field = 'estimatedhours'
union
select t.id as ticket, 9999999999 as changetime, th.value as value
from ticket t LEFT JOIN ticket_custom th on t.id = th.ticket and name = 'estimatedhours';

select a.startofday, a.nice_time, a.totalhours, b.estimatedhours, b.estimatedhours - a.totalhours as remaininghours
from (select x.startofday as startofday, strftime('%m/%d/%Y', x.startofday, 'unixepoch') as nice_time, sum(th.value) as totalhours from totalhours_log th JOIN (
select t.id as ticket, cd.startofday as startofday, min(tl.changetime) as changetime from ticket t JOIN change_dates cd JOIN totalhours_log tl on (t.id = tl.ticket and cd.startofday < tl.changetime)
where t.milestone = 'v0.1.0'
group by t.id, cd.startofday) x on th.changetime = x.changetime and x.ticket = th.ticket
group by x.startofday) a JOIN (select x.startofday as startofday, strftime('%m/%d/%Y', x.startofday, 'unixepoch') as nice_time, sum(eh.value) as estimatedhours from estimatedhours_log eh JOIN (
select t.id as ticket, cd.startofday as startofday, min(el.changetime) as changetime from ticket t JOIN change_dates cd JOIN estimatedhours_log el on (t.id = el.ticket and cd.startofday < el.changetime)
where t.milestone = 'v0.1.0'
group by t.id, cd.startofday) x on eh.changetime = x.changetime and x.ticket = eh.ticket
group by x.startofday) b on a.startofday = b.startofday;

[daan]

Nice idea! But, this seems difficult to maintain for multiple database backends. For larger projects, I also see some performance issues. Plus, when the database schema of Trac changes, all history could be lost.