date and time calculation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chadillac
Forum Newbie
Posts: 12
Joined: Tue Feb 28, 2006 3:30 pm
Location: Fort Lauderdale

date and time calculation

Post by chadillac »

I have been trying to wrap my head around this for the past couple hours and can't seem to come up with a query/method to properly get the numbers I need!

heres the deal.

We need to calculate time spent on "tickets" by our customer service people, average times for responses, closings, etc.

this is easy... no problem... BUT the numbers aren't accurate... they reflect a 24 hour day, we work 8. Therefore a ticket that comes in at 5:55PM wouldn't get tended to till 8AM the next day... this causes a 14 hour spike in user inactivity, thus making their averages ungodly inaccurate, even worse would be a ticket that comes in at 5:55PM on a Friday... this would give it close to 36 hours of calculated time when workers couldn't tend to the ticket but its still being tallied against their totals. Now .. I need to find a way to calculate time while ignoring or subtracting the hours we know we aren't not in the office.

I was thinking do it within the PHP because I can't even begin to think of how I would work that into a query.


Anyone have any suggestions on a plan of attack?


---- some useful information ----
times/dates are stored in unix_time
creation date and last activitiy date are the primary dates used to calculate average, but all activities are stored in a log that can be accessed
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Wouldn't this just be a simple math computation?

$total_time_worked/$total_time_spent_on_tickets
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

how are you identifying the starting point of the ticket being worked?

you should do that as soon as the tech 'opens' the ticket, not as soon as the ticket is put into the system.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I would recommend building an interim table to do your reporting on. The tickets that are in the same day are easy, but you will need to subtract 16 hours/day for multi-day tickets.
(#10850)
chadillac
Forum Newbie
Posts: 12
Joined: Tue Feb 28, 2006 3:30 pm
Location: Fort Lauderdale

Post by chadillac »

scottayy wrote:Wouldn't this just be a simple math computation?

$total_time_worked/$total_time_spent_on_tickets
nope, because I have to use a pre-set schedule and then only calculate hours that were "on the clock".


as for identifying the starting point, we are going from ticket assignment/creation. Our staff are instantly notified via e-mail if they have a new ticket assigned. So technically they begin working on it within a couple minutes of it hitting the system.


hmmm I hadn't thought about just subtracting 16 hours per day, I was to caught up in trying to figure out the right hours to calculate.

that may just work ;)


so scott.... I guess you were kinda right ;)
Post Reply