[rt-users] TimeTaken shows 0 minutes instead of 15 mins

[Stuart Browne]

Asif,

> mysql> select TimeTaken from Transactions where id = 7391416;
<snip>
> 
> mysql> select TimeWorked from Tickets where id = 1188802;
<snip>
> 
> Transaction 7391416 is part of the Ticket 1188802 where the time
> worked was 15 mins.
> 
> What gives?
> 
> Using RT  3.8.2 with mysql 5.0.75

It depends on what actions were taken during the transaction.

If time was entered during the ticket creation, the TimeTaken value is filled in.

If a comment or such was entered and time was entered against the action, it is created as TimeWorked with an NewValue / TimeWorked.

To work out how much time a given transaction took, you need to do some math, (MySQL SQL follows):

SELECT
    SUM(Transactions.TimeTaken) + SUM(
        IF(Transactions.OldValue != Transactions.NewValue,
            Transactions.NewValue - Transactions.OldValue,
            0)
       ) AS TimeWorked
FROM
    Transactions
WHERE
        Field      = 'TimeWorked'
    AND ObjectType = 'RT::Ticket
    AND ObjectId   = 1188802;

Adds all the TimeTaken values to the differences in OldValue and NewValue on TimeWorked for a given ticket.

Stuart