lorsungcu
asked on
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.
ASKER
How would I use the current smalldatetime as the later timestamp? use PHP's Date to make a time variable, then insert it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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
http://msdn.microsoft.com/en-us/library/ms189794.aspx