Developer - Calculate time spent on ticket

[Thaenor]

I hope this is the right place to ask this, but I need help,

I want to elaborate an SQL statement or a php function that can calculate how much time a user has spent solving a certain ticket.
I wanted to know how can I do this? There is a timeout field on the tickets table (I have access to the DB) but I don't know what it means.
What is the best way to say "User X spent Y hours solving ticket W"?

Can someone please help?
Thanks in advance?

[crythias]

Unless an agent self reports his time, what would make sense to you? What *exactly* would count to indicate start and stop time by an agent on a ticket? Most of the time the agent submits after the session is complete, but what would indicate the start of session?

viewtopic.php?t=12546
Also, I use this:
http://sourceforge.net/projects/tinytimetracker/

[eandrex]

how much time a user has spent solving a certain ticket.
I wanted to know how can I do this?

Dont the stats do this already? Column header SolutionTimeInMin i think
or am i wrong

[Thaenor]

how much time a user has spent solving a certain ticket.
I wanted to know how can I do this?

Dont the stats do this already? Column header SolutionTimeInMin i think
or am i wrong

And where can I find that column?

[Thaenor]

Unless an agent self reports his time, what would make sense to you? What *exactly* would count to indicate start and stop time by an agent on a ticket? Most of the time the agent submits after the session is complete, but what would indicate the start of session?

viewtopic.php?t=12546
Also, I use this:
http://sourceforge.net/projects/tinytimetracker/

Those SQL queries are turning up errors on my DB. I'm using pgAdmin (postgres)

[crythias]

Dont the stats do this already? Column header SolutionTimeInMin i think

This accounts for the ticket's elapsed time from create to close. It doesn't necessarily represent

how much time a user has spent solving a certain ticket.

Those SQL queries are turning up errors on my DB. I'm using pgAdmin (postgres)

I'm not enough familiar with PGSQL to provide assistance in a timely manner on this.

[eandrex]

Yeah, you are right, crythias.

The only problem querying "time_accounting" table, is that Agents can(and will) inflate their time.. so there is no real way to control it

[EXG133]

Yeah, you are right, crythias.

The only problem querying "time_accounting" table, is that Agents can(and will) inflate their time.. so there is no real way to control it

I had this same discussion but about HP OpenView & BMC Remedy instead of OTRS. Even with a fat client and more ways to check what agents are actually doing it all boils down to "but I have to trust the agent to report their time at some point". The only exception are highly integrated IP phone systems where agents only provide support over the phone. Even if your agents are completely honest it's so easy to forget to update a ticket or leave a popup open or make a typo or open the ticket of a colleague ... and I'm sure you can name more possibilities

[Thaenor]

Thanks for the feedback.

Is there any documentation I can read about how the OTRS tables are structured?
I wanted to see things like, how much time left before an sla is met or how do fields look like if that sla has been exceded.

Essencially I'm trying to figure out metrics in the system. What does the "timeout" collumn mean in tickets? Are those values unix timestamps? And what about "solution time" in "sla" table? They have an integer, but is it the time in hours? Minutes? Seconds?

[crythias]

https://github.com/OTRS/otrs/blob/rel-4 ... gresql.sql

If you see an integer used for time, that's epoch seconds.