Time Difference Between Records from the same field

[Kevyn1980]

The web site my company uses date and time stamps any journal entry a user
will place in the web site. This data is kept in same field, how can I
calculate the difference in times from one record to the next?

This is how is shows when I pull the data from a query (it is in same field):

journal_date_time
10/5/2009 4:21:20 PM
10/5/2009 4:21:22 PM
10/5/2009 4:21:22 PM
10/5/2009 4:33:19 PM
10/5/2009 4:33:31 PM
10/5/2009 4:48:49 PM
10/5/2009 4:49:40 PM
10/5/2009 4:50:19 PM
10/5/2009 5:23:00 PM
10/5/2009 5:23:49 PM
10/5/2009 5:23:54 PM
10/5/2009 5:41:53 PM
10/5/2009 5:41:53 PM
10/5/2009 5:42:29 PM
10/5/2009 5:42:29 PM

If these were to different fields I would have no problems with it, I'd just
use the DateDiff function, but not sure if you can even do it if the data is
in the same field.

Thank you a head of time
Kevyn

 

[vanderghast]

SELECT a.journal_date_time AS theTime, b.journal_date_time AS timeBefore
FROM (table AS a LEFT JOIN table AS b
ON a.journal_date_time > b.journal_date_time)
LEFT JOIN table AS c
ON a.journal_date_time > c.journal_date_time
GROUP BY a.journal_date_time, b.journal_date_time
HAVING b.journal_date_time = MAX( c.journal_date_time)



That could be slow, though, since Jet has problem to optimize efficiently
non-equi joins.
You may prefer a subquery, more direct (and maybe faster, in this case, than
joins) :


SELECT a.journal_date_time, (SELECT MAX(b.journal_date_time)
FROM table AS b
WHERE b.journal_date_time < a.journal_date_time )
FROM table AS a





Vanderghast, Access MVP

 

[JBW]

I asked a similar question recently and received a useful reply from a couple
of folks. I was able to use the query from the first response to get date as
a decimal value and the function from the second response to convert the
decimal value. I was trying to get the elapsed time between detections of
individual animals so you may not need the 'by group' part. See below:

First response
What you need is a "running difference" query. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"DaysBetweenQuery.mdb" which illustrates how to do this. You can find it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445. See
the "BY GROUP" examples.

That will get you the difference between the two dates, but it will be a
decimal value.

To convert that to days, hours, and minutes, you need a different sample:
"ConvertDateTimeToFormattedString.mdb", which you can find here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=373. Also
"ConvertStringStuff.mdb"
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=372

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Second response
I'm not familiar with Roger's query, but the data type of the AnimalID
shouldn't normally make any difference. The following query should give you
the results you want, again with the elapsed time in days as a decimal number:


SELECT [AnimalID], [DateDetected]+[TimeDetected],
(SELECT MIN([DateDetected]+[TimeDetected])
FROM [YourTable] As T2
WHERE T2.[AnimalID] = T1.[AnimalID]
AND T2.[DateDetected]+[TimeDetected] > T1.[DateDetected]+[TimeDetected])
As NextDetection,
NextDetection - [DateDetected]+[TimeDetected] AS ElapsedTime
FROM [YourTable] As T1
ORDER BY [AnimalID], [DateDetected]+[TimeDetected];

If you want to convert the decimal values into a days:hours:minutes:seconds
the following function will do it:

Public Function TimeElapsed(dblTotalTime As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngDays As Long
Dim lngHours As Long
Dim strMinutesSeconds As String

' get number of days
lngDays = Int(dblTotalTime)

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' return total elapsed time either as total hours etc
' or as days:hours etc
If blnShowDays Then
lngHours = lngHours - (lngDays * HOURSINDAY)
TimeElapsed = lngDays & ":" & Format(lngHours, "00") &
strMinutesSeconds
Else
TimeElapsed = Format(lngHours, "00") & strMinutesSeconds
End If

End Function

By default the function shows the total hours, e.g.

TimeElapsed([ElapsedTime]) might return:

57:53:17

but by passing True as the optional blnShowDays argument:

TimeElapsed([ElapsedTime], True) it would show the days and return:

2:09:53:17

Ken Sheridan
Stafford, England "