= Time Tracking =

Support for basic time tracking is an often request feature, but it does not look like it is going to be integrated into Trac any time soon. Please read http://projects.edgewall.com/trac/ticket/710 for further details. Still there are ways to enhance the ticket system with the desired functionality. This page is meant as an overview of working solutions and as a howto.

So far two solutions are available that are described below, both modify the Trac's source code:

 * Extending the data model  (for Trac 0.8)
 * Using custom ticket fields (for Trac 0.9)

== Extending the data model ==

A patch has been posted to http://projects.edgewall.com/trac/ticket/1005 that adds two new fields to the ticket table, "Planned work" and "Actual work", that can be edited in the ticket view. It also extends the Roadmap with a progress bar that displays how much word has been done and how much remains.

== Using custom ticket fields ==

A less invasive way to add time tracking is through TracTicketsCustomFields. Custom fields are meant as an extension mechanism that allows to store additional data with tickets without changing the underlying data model. 

This patch touches quite a few spots though:

 * Patch Trac to provide a progress bar in the Roadmap view
 * Modify trac.ini for each project to add custom fields
 * Add a ticket query
 * Use a Subversion commit hook to log spent time

Here is a screen shot of the roadmap and of the ticket report:
http://steffenpingel.de/trac/trac-roadmap.png
http://steffenpingel.de/trac/trac-timetracking-report.png 

===== Patching Trac =====

Download and apply the [http://steffenpingel.de/patches/trac/trac-time-tracking-0.9.diff time tracking patch]:

 * Download and unzip/untar Trac
 * Run {{{patch -p0 < trac-time-tracking-0.9.diff}}} from the created trac-0.9 directory
 * Reinstall Trac (run {{{./setup.py install}}})

===== Modifying trac.ini =====

Add the following section to your trac.ini (this needs to be used for each project that you want to use with time tracking):

{{{
[ticket-custom]
tt_estimated = text
tt_estimated.label = Time planned

tt_spent = text
tt_spent.label = Time spent

tt_remaining = text
tt_remaining.label = Time remaining
}}}

===== Adding the query =====

Open your Trac project in a web browser and goto "View Tickets" -> "New Report" (you might need to grant REPORT_CREATE permission first). Enter a title like "Time Tracking" and the following query:

====== SQLite ======

{{{
#!sql
SELECT DISTINCT 

   id AS ticket,
   (CASE WHEN pl.value ISNULL THEN '' ELSE pl.value END) AS planned,
   (CASE WHEN s.value ISNULL THEN '' ELSE s.value END) AS spent,
   (CASE WHEN r.value ISNULL THEN '' ELSE r.value END) AS remaining,
   (CASE WHEN ((s.value + r.value) - pl.value) = "0.0" THEN ' ' ELSE (s.value + r.value) - pl.value END) as accuracy,
   milestone AS customer,
   summary, component, status 

  FROM ticket t,enum p

  LEFT OUTER JOIN ticket_custom pl ON
       (t.id=pl.ticket AND pl.name='tt_estimated')

  LEFT OUTER JOIN ticket_custom s ON
       (t.id=s.ticket AND s.name='tt_spent')

  LEFT OUTER JOIN ticket_custom r ON
       (t.id=r.ticket AND r.name='tt_remaining')


  ORDER BY milestone
}}}

====== Using Postgres with psycho ======

Postgres has a few wrinkles and time intervals are not as hard to use.

{{{
#!sql
SELECT DISTINCT 
   id AS ticket,
   pl.value::interval  AS planned,
   s.value::interval AS spent,
   r.value::interval AS interval,
  ((s.value::interval + r.value::interval) - pl.value::interval) as accuracy,
   milestone AS customer,   summary, component, status 
  FROM enum,ticket t
  LEFT OUTER JOIN ticket_custom pl ON
       (t.id=pl.ticket AND pl.name='tt_estimated')
  LEFT OUTER JOIN ticket_custom s ON
       (t.id=s.ticket AND s.name='tt_spent')
  LEFT OUTER JOIN ticket_custom r ON
       (t.id=r.ticket AND r.name='tt_remaining')
  ORDER BY milestone;

}}}

===== Using a Subversion commit hook =====

The {{{contrib/trac-post-commit-hook}}} script is a very convenient tool to interact with Trac's ticket system on commit. Copy the script to /usr/share/trac/contrib and create a skript '''hooks/post-commit-hook''' in your Subversion repository (do not forget to set the executable bit {{{chmod 755 hooks/post-commit-hook}}} with the following content, modify the TRAC_ENV to point to your local Trac project:

{{{
REPOS="$1"
REV="$2"
LOG=`svnlook log -r $REV $REPOS`
AUTHOR=`svnlook author -r $REV $REPOS`
TRAC_ENV='/var/trac/test'

/usr/bin/python /usr/share/trac/contrib/trac-post-commit-hook \
  -p "$TRAC_ENV"  \
  -r "$REV"       \
  -u "$AUTHOR"    \
  -m "$LOG"
}}}

You can now use special keywords in your commit message to modify Trac tickets. The available commands are:

||refs #id||Adds a reference to the ticket's Changelog||
||closes #id||Closes the ticket||
||spent xxh||Adds the amount to the time spent, decreases the time remaining by amount||
||rem xxh||Sets the time remaining to the amount.||

'''Examples:'''

{{{ 
svn commit -m 'Added time tracking, refs #1, spent 4h, rem 1h'
}}}

This adds an entry to the Changelog of ticket #1, increases the time spent by 4 hours and sets the remaining time to 1 hour.

{{{ 
svn commit -m 'Added time tracking, closes #1, spent 1h'
}}}

This closes ticket #1, increases the time spent by 1 hour and decreases the remaining time by 1 hour.

-----
==== ticket:2442 ====

I tried adding the 0.9 mod today and hit a little static in the above. First, of course the .diff doesn't work for 0.9.1. Rolled back and followed steps above but the roadmap breaks with: 

{{{
Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/web/cgi_frontend.py", line 131, in run
    dispatch_request(req.path_info, req, env)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 139, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 107, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.3/site-packages/trac/ticket/roadmap.py", line 213, in process_request
    [ 'owner', 'tt_estimated', 'tt_remaining', 'tt_spent' ])
  File "/usr/lib/python2.3/site-packages/trac/ticket/roadmap.py", line 66, in get_tickets_for_milestone
    ticket = {
TypeError: tuple indices must be integers
}}}

Using SQLite 3.2.7. I have not added estimates to every existing ticket ... would hope that's not necessary ... anyone else struck this?