--------------------------------------------------------------------------------
I have the same issue here. It's not a feature, its a inconsistency in the number displayed. On the ticket detail page, the already spent times on the sub-tickets gets added together (see screenshot). On the ticket list view, the column for the spent times are just for the single ticket.

To be consistent, the number in the ticket list view should be the same as in the ticket detail view.
--------------------------------------------------------------------------------
I use the following query to obtain the information.

<pre>
select story.id,
story.subject,
max(story.estimated_hours) story__estimated,
sum(subtask.estimated_hours) subtask__estimated,
sum(te.hours) subtask__spent_time

from issues story

join issues subtask
on subtask.parent_id = story.id

left
join (select project_id,
issue_id,
sum(hours) hours
from time_entries
where project_id = 1
group
by project_id,
issue_id
) te
on te.issue_id = subtask.id
and te.project_id = subtask.project_id

where story.project_id = 1
and story.tracker_id = 4

group
by story.id,
story.subject
</pre>
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
Just in case it can help, a quick patch for v1.4.4. Just add the following

<pre><code class="ruby">
@available_columns.insert index, QueryColumn.new(:total_spent_hours,
:sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
:default_order => 'desc',
:caption => :label_overall_spent_time
)
</code></pre>

to the app/models/query.rb file, at line 382 (just after the same code for the spent_hours column). Restart apache2 and it should add the desired column

*Warning* : it really is just a quick hack, if you look at the :sortable entry, the SQL code isn't what it should be. It works for issue list, but it may fails miserably for some other part of Redmine... --Update-- : well, I should have guessed, when you order the list by this overall spent time, it fails as it sorts by the non-overall spent time...

HTH.
--------------------------------------------------------------------------------
As a side note : that's weird, with my redmine.org's account, I can change this issue status and its assignation ! Have I missed something ??
--------------------------------------------------------------------------------
+1

i have this problem in redmine 2.0.3
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
i use idea of Sylvain Langlade with query of Victor Hugo Bilouro and worked
--------------------------------------------------------------------------------
Whats the current status of this task?
Is there a chance to get this into the 2.2.0 release?
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
+All our company
--------------------------------------------------------------------------------
Is anything going to be done about this anytime soon? This "feature" is really blocking useful reporting within my organisation. A fix so that the time estimated and time spent is calculated consistently would be greatly appreciated. I would also far prefer to update my Redmine instance from an official source rather than hack/configure my existing production system.
--------------------------------------------------------------------------------
+1

Based on Sylvain's suggestion, I've managed to fix this issue on our v2.1.4 installation.

* app/models/query.rb line 406:
<pre><code class="ruby">
@available_columns.insert index, QueryColumn.new(:total_spent_hours,
:sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id OR #{TimeEntry.table_name}.issue_id IN (SELECT child_issue.id FROM #{Issue.table_name} child_issue WHERE child_issue.parent_id = #{Issue.table_name}.id))",
:default_order => 'desc',
:caption => :label_spent_time
)
</code></pre>

* app/helpers/queries_helper.rb line 59 (format the decimal value):
<pre><code class="ruby">
elsif column.name == :spent_hours || column.name == :total_spent_hours
sprintf "%.2f", value
</code></pre>

The total spent time per issue (including time spent on child issues) is nicely show in the issues list, sorting (*taking into account only time spent on first level children*) and exporting also work.

There is a smarter way to apply this fix rather than directly modifying Redmine scripts, but this is beyond me since I have almost zero experience with Ruby and RoR.
--------------------------------------------------------------------------------
Ovidiu Stanciu wrote:
> * app/models/query.rb line 406:
> [...]

Hi,

this should be also achieved with this:
<pre><code class="sql">SELECT COALESCE(SUM(hours), 0)
FROM #{TimeEntry.table_name} time_entry
LEFT JOIN #{Issue.table_name} child_issue
ON time_entry.issue_id = child_issue.id
WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id
OR child_issue.parent_id IS NOT NULL</code></pre>

IN could be very slow in some cases with many subissues.
--------------------------------------------------------------------------------
I have edited the code on our 2.1.2 system as a workaround for this problem. Many thanks to Victor, Sylvain, Ovidiu and Daniel (authors of notes 3, 5, 14 and 15 respectively) for providing the information to do this.

I am surprised to see that this issue (which is a definite hindrance to accurate reporting) has not yet become a release candidate after seven months of being initially reported.

--------------------------------------------------------------------------------
@Ben
Can you provide a combined patch for this?
--------------------------------------------------------------------------------
@Daniel,

I've never patched Redmine (apart from my previous hack) and I know next to nothing about Ruby, so if this is wrong, please let me know.

For this patch I downloaded the latest stable version (2.2.1), applied and tested the changes again. I then generated the attached patch file by following the guide at http://www.redmine.org/projects/redmine/wiki/Patch.

One side effect of this change is that any custom queries that referred to "Spent time" need to have the column re-added. If anybody can figure out a way around this, please let me know.
--------------------------------------------------------------------------------
Hi,

What else needs to be done with this feature to make it go into a scheduled release? It would be good to have this incorportated into a production version of Redmine ASAP.

Best wishes,

Ben
--------------------------------------------------------------------------------
Please take into account that both fixes in query.rb (mine and Daniel's) *only take into account time spent on direct descendants when sorting*. In case there are multiple levels, the order won't be consistent with the values displayed.

Example :

|*ISSUE_ID*|*PARENT_ID*|*TIME SPENT*|
|1|||
|2|1|10|
|3|||
|4|3|5|
|5|4|10|

If displaying only top level issues and sorting ascending by total time spent:

|*ISSUE_ID*|*TOTAL TIME SPENT*|
|3|15|
|1|10|

15 comes before 10 because the query fails to take into account the 10 hours spent on issue 5, which is a third level child.

I imagine this is pretty hard to achieve in a single query and I think there should be some recursion involved, as when calculating :total_spent_hours on an issue.

This should be addressed in order to achieve a complete fix.

Best regards,
Ovidiu Stanciu
--------------------------------------------------------------------------------
+1
running 2.3.0, was really hoping for this fix.
--------------------------------------------------------------------------------
My first attempt on coding in redmine (and RoR), I'm not trained in software. Patch is same as Ben's above, but changed to work on 2.3.0.
--------------------------------------------------------------------------------
+1
I suppose that it will be the best solution to have two issue attributes for "Spent Time" that should be visible at Issues List and Issue details.
One of those two attributes might be "Spent Time" that should display time spent on single issue (without Time logs from child issues).
Another attribute might be "Cumulative Spent Time" that should contain all time logs from selected issue and all childs/grandchilds/grand...grandchilds.
--------------------------------------------------------------------------------
I've just implemented Henrik's patch on 2.3.1 and it works. It also meets Ovidiu's criteria in that a change of spent time in the sub-task of a sub-task is reflected in the parent.
--------------------------------------------------------------------------------
I'm using Redmine 2.3.1. The fix from Bed Dalling (note-24) seams to work for the Web User Interface (HTML). Thank youGreat work!

Anyway there still seams to be a minor problem within the REST API (JSON) regarding the same or a similar problem:
* Currently the @estimated_hours@ of the parent issue is the sum of the child issues (in REST API as well as in Web Interface). --> *good*
* But the @spent_hours@ are accumulated in the Web Interface but not in the REST API --> *could be improved*

Fixing that minor inconsistency for @estimated_hours@ and @spent_hours@ would simplify matters, when generating project reports using the REST API (JSON) - if the project makes use of parent issues to model a Work Breakdown Structure.

In addition "VD DV"s proposal in note-23 could further simpliy matters.

See #5303#note-10 for another issue regarding REST API and @spent_hours@.

Best Regards,
David
--------------------------------------------------------------------------------
I'm using Redmine 2.3.3 and tried Henrik's patch. But it didn't work and the spend time column was disappeared.
Please help me....

UPDATED: It's working
I forgot add spent time in option view of issues list.
Be lucky.
--------------------------------------------------------------------------------
But when I sorted a spent time column, It displayed Error 500.
How can I fix it??
Thanks.
--------------------------------------------------------------------------------
To solve such a bug, we need more info. Error 500 is nothing which helps us. Please provide corresponding log entries.
--------------------------------------------------------------------------------
This issue is also actual for spent time reports.
--------------------------------------------------------------------------------
+1 please fix this inconsistency
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
I'm using Redmine 2.5.1 and used "Henrik Leon's":http://www.redmine.org/users/39014 patch. I've uploaded my diff file as the line numbers are different (code is the same, just different line numbers).

For me this patch is useful because I use sub tasks to clearly define the steps to be done for small jobs which occur often and require specific steps per our ISO9001 procedures without creating a separate project every time. This way, a main request can be generated by a user with reporter roles and I can copy sub tasks to the request. Time logged now shows on the main request and I can filter out the tiny tasks to clearly show what requests have been handled and at what cost in time.

If you don't already know:
* Keep a copy of your original issue_query.rb file.
* Restart your servers - if redmine servers keep going down, then there is an error in the file.
* After installing the patch, the _*Spent Time*_ column will be gone. You must go back into Admin -> Settings -> Task Tracking and add the column back to your table column preference.
* If you log time on the parent task, it will also add that time into the total.

Patch: attachment:spent_time_on_parents_hack_redmine251.patch

Screenshot: attachment:total_spent_time_view_all_issues.png
Screenshot: attachment:total_spent_time_issue.png
Screenshot: attachment:total_spent_time_details.png
Screenshot: attachment:total_spent_time_report.png

Please note I have not heavily tested this patch. It could certainly have side effects, but for me, it looks like it's working as I wish.
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
+1
--------------------------------------------------------------------------------
We've just installed Redmine 3.0.3 and we're having the same problem - is there a patch for this version?
--------------------------------------------------------------------------------
Feature added for 3.1.0. There are now 2 columns available: "Spent time" and "Total spent time". The second one aggregates time spent on the issue and its descendants.
--------------------------------------------------------------------------------
Thanks for this great feature, Jean-Philippe!

it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.
Let me add: Having applied this part by myself, I was able to see "total spent time" column in the query list. This is a great feature in deed.

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Ryosuke Hirai wrote:
> it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.

Good catch ;) Redmine version:3.1.0 is indeed missing this feature due to the fact that the related commits weren't merged in source:/branches/3.1-stable when 3.1.0 got tagged. They actually still aren't. I've reported these issues in #20456.

Thanks for reporting this omission. Mischa.

--------------------------------------------------------------------------------
I have merged trunk r14406 to r14410 to 3.1-stable.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

relates,Closed,11087,Calculate Total Time Spent on Parent Issue
relates,Closed,20456,3.1-stable/3.1.0: missing commits (omitted from being merged from trunk)
relates,Closed,17550,Spent time in exported CSV is wrong
relates,Closed,16159,Wrong calculation of spent time field on Issues report
relates,Closed,10527,time_spent does not sort properly for this case
duplicates,Closed,11566,Spent Time isn't added on Issue List page
duplicates,Closed,12610,sub tasks sent time is not counted on the issues list
duplicates,Closed,12572,Wrong calculation of the spent time for issue with descendants
duplicates,Closed,16363,Spent time in list view is not the same as in single ticket view
duplicates,Closed,14483,Add column "cumulated spent time" to issue filters
duplicates,Closed,14384,Add column "spent time" on list issues