[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Calculate time duration from existing date time and current data time

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.

1 Solution
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


lorsungcuAuthor Commented:
How would I use the current smalldatetime as the later timestamp?  use PHP's Date to make a time variable, then insert it?
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 = ....

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

lorsungcuAuthor Commented:
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.
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
Accept my last comment posted on 05/13/2011 - 01:35PM EDT
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now