Calculate time duration from existing date time and current data time

Posted on 2011-05-13
Last Modified: 2012-05-11
I have a database that will be logging calls from a PBX.  The pbx will be sending call info, and the script will first insert a record with ID, call info, and the current date/time.  Once the call is hung up, the script should update the record by pulling the starting time, calculating the difference between then and now, and inserting the duration back into the same record (using the id as a reference).  I think I have it all except the duration calculation; what would the best way to handle this be?  It must be done within the PHP, this isn't something we can create a SP for or anything.

Question by:lorsungcu
    LVL 7

    Expert Comment

    update tablename
    set durations = DATEDIFF(ss, '2011-05-13 12:00', '2011-05-13 13:00')
    where id = @id

    ss will give datediff results in Seconde.
    mi will give datediff results in minute

    LVL 2

    Author Comment

    How would I use the current smalldatetime as the later timestamp?  use PHP's Date to make a time variable, then insert it?
    LVL 41

    Accepted Solution

    I would suggest you make duration a computed column. So basically you update the table with the start and end time for the call and the duration gets calculated automatically. Something like this:

    Alter table yourtable add duration as (datediff(ss, starttimecolumn, endtimecolumn))

    Then when the call is initiated

    insert yourtable values(..., getdate(), ....)

    when the call is finished

    update yourtable set endtimecolumn = getdate() where callid = ....

    LVL 2

    Author Comment

    That does sound like a better solution, especially as I am not looking forward to matching date formats, etc.  I will try it without that, and if I have issues, I'll give that a go.  Thanks for the thought.
    LVL 5

    Expert Comment

    Ralmada is correct. You should have 3 times. Start, End and Duration time.
    In addition you should look into setting all your times to UTC time.
    The reason being that sometimes server times and and sql times can be different. Especially if your app and sql server are hosted in different locations.

    Conversion to UTC time can save you some headaches down the line.

    And datediff is your way to go
    LVL 41

    Expert Comment

    Accept my last comment posted on 05/13/2011 - 01:35PM EDT
    LVL 53

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This is about my first experience with programming Arduino.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now