Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2012
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Best way to work out elapsed between two timestamps when including an on-hold time pe

    I am creating a sort of ticketing system where cases are created and have a set time period to be answered in.

    The start-time is stored in the database as a timestamp e.g. 2013-10-16 01:39:00, datetime field in mysql. There is also a resolve-by timestamp stored which could be set to 8 hours after the start-time.

    When a case is either marked as resolved or put on hold a stop-time timestamp is stored in the database as well as a clock_stopped flag. This means that the 8 hour time countdown is stopped. When a case is no longer on hold or is re-opened then the time countdown is restarted. But its not to include the period of time which the clock was stopped. So the total time to resolve case remains 8 hours even if it was on hold for a day. Make sense?

    What is the best way to work out elapsed time when including periods of time when the elapsed time is not counting down as above? Do I need to add in more fields to store in the database e.g. How would you do it?

  • #2
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts
    Post what you got so far.
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,958
    Thanks
    2
    Thanked 304 Times in 296 Posts
    You would store the ticket information in one table and use a separate related table to hold records with the actual start/stop times that have been spent working on the tickets, related back to the ticket table using the ticket id.

    To get the total/elapsed time spent, you would sum the time differences between the start/stop times.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #4
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts
    Where's the code?
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:

  • #5
    New Coder
    Join Date
    Apr 2012
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ill post what I have tomorrow, it was more a general idea of how to do it using php date functions I was looking for.

  • #6
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts
    Quote Originally Posted by recci View Post
    Ill post what I have tomorrow, it was more a general idea of how to do it using php date functions I was looking for.
    The 1st 3 are useful, enjoy!
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •