[RESOLVED] Some doubts about DateDifference - calculating time

[akhileshbc]

Hi...

I need to use DateDiff() to calculate the difference in Time.

Private Sub Form_Load()
    Dim myDate1 As Date
    Dim myDate2 As Date
    
    Dim lngMin  As Long
    
    'mydate1 will hold another value
    myDate2 = Now
    
    lngMin = DateDiff("n", myDate1, myDate2)
    Me.Caption = lngMin

End Sub

Here, myDate1 will hold a date value stored some days before.(using Now() function).

Will there be any problems with this calculation. I heard that there's an issue with midnight time. What's it ? Can anybody explain it ?

Thanks in advance...

[LaVolpe]

I am not aware of any midnight issue with DateDiff. However, midnight is an issue if using VB's Timer function, since the Timer value rolls over after midnight.

[Spoo]

Akhil

Likewise, I'm not aware of a midnight issue with DateDiff.

Could you possibly be thinking of the midnight issue
assocaited with the Timer function?

It is sometimes used as a quickie way to establish
elapsed time, say in a long loop, as in:

beginTime = Timer
< long loop >
endTime = Timer
elapsedTime = endTime - beginTime

Normally, that code would be fine.

However, since Timer returns the number of seconds
from midnight, use of that code for an interval that
straddled midnight would give faulty results.

EDIT:

I'd say that the reason your code frag would NOT
suffer from the midnight issue is that your vars are both
-- set by the Now() function, and
-- actually hold a date and the time.

Spoo

[akhileshbc]

Thanks guys....

While I was going through some posts, I read those midnight issue in a post. But I don't remember much.
That's why I asked.

Another question:
One of my old program uses the following code:

Private Sub Timer1_Timer()
    lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Time), "0:0:0"), "hh:mm:ss")
End Sub

Which calculates the time elapsed. Timer1 has an interval of 1000.
Start_Time is a string variable.

Start_Time = Time

Will this cause any issues ? Do you see any flaws in this one ?

[Spoo]

Akhil

Yes, I see the possibility of a flaw with that approach.

To wit:

    Dim sTime As Date
    Dim eTime1 As Date
    Dim eTime2 As Date
    '
    sTime = #11:58:55 PM#
    eTime1 = #11:59:55 PM#
    eTime2 = #12:01:00 AM#
    '
    v1 = DateDiff("s", sTime, eTime1)    ' v1 = 60
    v2 = DateDiff("s", sTime, eTime2)    ' v2 = -86275

Does that constitute a flaw in your opinion?

EDIT:

The key here is the difference between Time and Now

    eTime1 = Time    ' 8:58:46 AM
    eTime2 = Now     ' 5/20/2010 8:58:46 AM

Now includes the date.
Time does not, and hence is susceptible to the midnight issue.

Spoo

[akhileshbc]

Oh Yeah... ! Thanks for pointing that one...

I think, it can be solved, if we use Now(). Am I right ?

[Spoo]

Akhil

I'd say yes.

Spoo

[akhileshbc]

Thanks..

Another question:

Dim strTemp As String
    Dim tSec As Byte, tMin As Byte, tHour As Byte
    
    For i = LBound(myArray) To UBound(myArray)
        strTemp = Split(myArray(i), ":")
        
        tHour = tHour + Val(strTemp(0))
        tMin = tMin + Val(strTemp(1))
        tSec = tSec + Val(strTemp(2))
        If tSec >= 60 Then
            tMin = tMin + 1
            tSec = tSec - 60
        End If
        
        If tMin >= 60 Then
            tHour = tHour + 1
            tMin = tMin - 60
        End If
    Next
    lblTotalTime.Caption = CStr(tHour) & " hrs " & CStr(tMin) & " mins " & CStr(tSec) & " secs"

This is the code that I use for finding the total of all elapsed time, which's stored in a String Array. Do you have any ideas/suggestions on improving this one ?

Example data in the string array:

00:25:01
05:13:00
01:56:19
01:30:33

etc...

These elapsed time in this array was inserted from the data obtained using the code in post#4.

[Spoo]

Akhil

That all seems fine to me.

I initially had the thought that you could move the
If tSec >= 60 and If tMin >= 60 outside the loop
and do the "updating" just once, but ..

- the performance improvement would be virtually nil
- it would require a more complex set of logic

.. so .. don't change anything !

Spoo

[akhileshbc]

Thanks Spoo...

I have some other questions too. But right now, I'm somewhat busy. I'll post the rest of the questions soon..

[LaVolpe]

This is the code that I use for finding the total of all elapsed time, which's stored in a String Array. Do you have any ideas/suggestions on improving this one ?

Example data in the string array:
00:25:01
05:13:00
01:56:19
01:30:33

Suggestions? Consider using a Date array vs string array. Not only will you be able to use the DateDiff function more easily, you are actually saving memory too: Date variable = 8 bytes each; your 8-char string = 22 bytes each

[akhileshbc]

Thanks for the info..

But they are previously calculated Elapsed times. In post#8, the code will first grab all previously calculated data from a file/database into a string array. And then, the total elapsed time is found.

Any ideas to simplify the process ?

[LaVolpe]

I still think using date arrays could be much easier and possibly more accurate. Here's an example using your 4 time elapsed totals as a date vs string

Private Sub Command1_Click()

    Dim dtElapsed(0 To 3) As Date
    Dim dtTotal As Date, X As Long
    
    dtElapsed(0) = TimeSerial(0, 25, 1)
    dtElapsed(1) = TimeSerial(5, 13, 0)
    dtElapsed(2) = TimeSerial(1, 56, 19)
    dtElapsed(3) = TimeSerial(1, 30, 33)
    
    For X = 0 To UBound(dtElapsed)
        dtTotal = dtTotal + dtElapsed(X)
    Next
    
    Debug.Print "days: "; DateDiff("d", Date, Date + dtTotal); 
    Debug.Print " hrs: "; Hour(dtTotal); " mins: "; Minute(dtTotal); 
    Debug.Print " secs: "; Second(dtTotal)

End Sub

[LaVolpe]

Reference previous reply. To get number of total days elapsed, another option:
Replace: Debug.Print "days: "; DateDiff("d", Date, Date + dtTotal);
With: Debug.Print "days: "; DateDiff("d", 0#, dtTotal);

[akhileshbc]

Thanks..
I think your idea is to store the elapsed time in a date variable(for eg: datetime field in database). Right ? And then add these datetime records to find the total.

If I want to use DATE variables, what are the things necessary to change in this code:

 lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Time), "0:0:0"), "hh:mm:ss")

Are these the only changes needed ?

Dim Start_Time As Date
Start_Time = Now

Private Sub Timer1_Timer()
    lblElapsedTime.Caption = Format(DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0"), "hh:mm:ss")
End Sub

[LaVolpe]

lblElapsedTime.Caption = Format(DateDiff("s", Now(), Start_Time), "hh:nn:ss")

P.S. If dates are stored in database as datetime datatypes, then you very well may be able to return a SUM() query vs looping thru a recordset and adding them up manually.

[akhileshbc]

Ah! got the idea...

I will do the updation and will post the result...

Thanks LaVolpe and Spoo...

[akhileshbc]

I have another doubt:

I had tested this code:

Option Explicit
Dim Start_Time As Date

Private Sub Command1_Click()
    Me.Caption = DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0")  '~~~ Calculate the difference
End Sub

Private Sub Form_Load()
    Start_Time = Now    '~~~ Set the starting time
End Sub

and the result on clicking the button after 5 seconds is 12:00:05 AM
Is this what I have to store into the db ?
And why there's no date ?

After running the program, I have changed the date of the system from 21st May to 22nd May. The result obtained is: 12/31/1899 12:00:11 AM

I have tried your code in post#16. But I think, the format function is not getting it correctly. It shows only 00:00:00

Sorry for these dump questions..

[Spoo]

Akhil

When you say...

result on clicking the button after 5 seconds is 12:00:05 AM

... are you referring to Me.Caption, as in

Private Sub Command1_Click()
    Me.Caption = DateAdd("s", DateDiff("s", Start_Time, Now), "0:0:0")  
End Sub

If so, perhaps you could, for troubleshooting purposes, break apart
that line of code, as in:

Private Sub Command1_Click()
    v1 = DateDiff("s", Start_Time, Now)
    v2 = DateAdd("s", v1, "0:0:0")  
    v3 = Now
    v4 = DateAdd("s", v1, v3) 
End Sub

If I understand what is happening, you'd get these results:

1. v1 = 5 .... calculates the difference
2. v2 = 12:00:05 AM .... adds said difference to a preset time, namely, "0:0:0"
3. v3 = 5/21/2010 9:22:41 AM
4. v4 = 5/21/2010 9:22:46 AM

Comments:

1. No issue here
2. The "problem" here is you are doing trying to add 5 seconds to "0:0:0"
   (which is being interpreted as 12:00:00 AM). This seems to be the
   where the breakdown in your logic flow is occurring.
3. Hypothetical value of NOW
4. No DateAdd problem here, because you are adding 5 seconds to a time
   (which also contains the date).

Does that help?

Spoo

[LaVolpe]

I have another doubt:

I have tried your code in post#16. But I think, the format function is not getting it correctly. It shows only 00:00:00

Sorry for these dump questions..

Oops, I think this is more in line with your thoughts:

 lblElapsedTime.Caption =  Format(TimeSerial(0, 0, DateDiff("s", Now(), Start_Time)), "hh:nn:ss")

Edited: You may want to use something similar to anhn's suggestion below. TimeSerial has a major limitation: the number of seconds passed to it must be an Integer, therefore, any time lapse greater than 32767 seconds (a little over 9 hours) will generate an overflow error.

[anhn]

Try this:

Private Sub Command1_Click()
    Dim dt As Date
    
    dt = Now() - Start_Time
    Me.Caption = CLng(Int(dt)) & "d " & Format(dt, "hh:nn:ss")
End Sub

[akhileshbc]

Thanks guys..

But I'm a bit confused with the dateandtime !

I will explain my program's function:

Day1: Finds 5 elapsed times and store it to db.
Day2: Finds 3 elapsed times and store it to db.
Day3: Finds 2 elapsed times and store it to db.
Day4: Finds the total from 10 entries in db and displays it.

@Spoo: Thanks.. So, 0:0:0 is considered as 12:00:00 AM ?

@LaVolpe: Thanks... That's working too. But how/what should be stored to the db as elapsed time ? (I'm a bit confused with the data types. In my MS Access db, do I have to store it in DateTime field ?) Do I need to remove the formatting, before saving it to db ?

@anhn: Thanks.. When I checked the value of dt, it is similar to the result: 12:00:00 AM. If I store that dt value in my db (MS Access - DateandTime field), and then using the SUM() sql function, will it work correctly or any flaws ?

[Spoo]

I will explain my program's function:

Day1: Finds 5 elapsed times and store it to db.
Day2: Finds 3 elapsed times and store it to db.
Day3: Finds 2 elapsed times and store it to db.

Day4: Finds the total from 10 entries in db and displays it.

Akhil

Perhaps the easiest is to just store elapsed times (in seconds)
So, for example, if your DB looked like...

Rec  Day  NN    ET
 1    1   1    100
 2    1   2   2000
 3    1   3    100
 4    1   4    100
 5    1   5   2000
 6    2   1    100
 7    2   2    100
 8    2   3   3000
 9    3   1    300
10    3   2     20

Then, when needed (on Day4), just sum the ET's

totSecs = 7820  ' obtained by using a loop, or SQL statement

and do a 1-time conversion from seconds to hrs, mins, secs

intHRS = Int(totSecs / 3600)           '  2 hrs
remainSecs = totSec - intHRS * 3600
intMINS = Int(remainSecs / 60)         ' 10 mins
intSECS = remainSecs - intMINS * 60    ' 20 secs

Just a thought.
Spoo

[anhn]

@anhn: Thanks.. When I checked the value of dt, it is similar to the result: 12:00:00 AM. If I store that dt value in my db (MS Access - DateandTime field), and then using the SUM() sql function, will it work correctly or any flaws ?

Date data type is stored internally as a Double value (but much smaller range) with the integer part as number of days (from 1/1/1900?) and the decimal part is time value.
eg. 22/05/2010 12:44:03 AM is stored as 40320.0305902778
So you can add or subtract DateTime values the same way as with Double values.

Dim dt1 As Date
Dim dt2 As Date
Dim dt As Date

dt1 = #5/21/2010 9:12:25 PM#
dt2 = #5/22/2010 12:44:03 AM#
dt = dt2 - dt1
Debug.Print CLng(Int(dt)) & "d " & Format(dt, "hh:nn:ss")

0d 03:31:38

[LaVolpe]

And to piggyback, I don't foresee any issues. Using 2 elapsed times of 0:5:0 & 0:15:10, stored in the database would look like this inside your db: 12:05:00 AM & 12:15:10 AM. Sum() should return value of 12:20:10 AM, but that value in a "hh:nn:ss" formatted string = 0:20:10, not 12:20:10. Additionally Hour() returns 0, Minute() returns 20: Second() returns 10.

[akhileshbc]

@Spoo: That's nice. Thanks for the idea...
@anhn: Thanks for providing more details...
@LaVolpe: Thanks... But what happens if the elapsed time is more than a day (I mean 24+ hours). Will it work correctly ?

And what is the correct format of parameters in DateDiff() function ?
Is it like this: DateDiff("s", presentTime, previousTime)
or, like this: DateDiff("s", previousTime, presentTime)
...???

Thanks again guys...

[Spoo]

And what is the correct format of parameters in DateDiff() function ?

Is it like this: DateDiff("s", presentTime, previousTime)
or, like this: DateDiff("s", previousTime, presentTime)

Akhil

Short answer .. either way.

Long answer:

v1 = DateDiff("s", previousTime, presentTime) -- returns a positive number
v2 = DateDiff("s", presentTime, previousTime) -- returns a negative number

Spoo

[akhileshbc]

Akhil

Short answer .. either way.

Long answer:

v1 = DateDiff("s", previousTime, presentTime) -- returns a positive number
v2 = DateDiff("s", presentTime, previousTime) -- returns a negative number

Spoo

Thanks Spoo...

[LaVolpe]

@LaVolpe: Thanks... But what happens if the elapsed time is more than a day (I mean 24+ hours). Will it work correctly ?

DateDiff("d", 0#, CDate(SumOfElapsedTimes))

A query like Select Sum(Table1.ElapsedTimes) As SumOfElapsedTimes may return something like 0.01753472222, so you will want to use CDate as described above... and here:

Dim dt As Date
dt = CDate(SumOfElapsedTimes)
Debug.Print  DateDiff("d", 0#, dt) & "  " & Format(dt, "hh:nn:ss")

[anhn]

Why need to use DateDiff()?
Elapsed times can be declared as Double instead of Date.

Dim dt1 As Date
Dim dt2 As Date
Dim e1  As Double
Dim e2  As Double
Dim TotalElapsed As Double

dt1 = #5/21/2010 3:12:25 AM#
dt2 = #5/22/2010 7:44:53 AM#
e1 = dt2 - dt1
Debug.Print "e1 = " & Int(e1) & "d " & Format(e1, "hh:nn:ss")

dt1 = #5/22/2010 1:32:27 PM#
dt2 = #5/23/2010 10:12:05 AM#
e2 = dt2 - dt1
Debug.Print "e2 = " & Int(e2) & "d " & Format(e2, "hh:nn:ss")

TotalElapsed = e1 + e2
Debug.Print "TotalElapsed = " & Int(TotalElapsed) & "d " & Format(TotalElapsed, "hh:nn:ss")

Debug.Print "Days : " & Int(TotalElapsed)
Debug.Print "Hours: " & Hour(TotalElapsed)
Debug.Print "Mins : " & Minute(TotalElapsed)
Debug.Print "Secs : " & Second(TotalElapsed)

e1 = 1d 04:32:28
e2 = 0d 20:39:38
TotalElapsed = 2d 01:12:06
Days : 2
Hours: 1
Mins : 12
Secs : 6

[akhileshbc]

Thanks guys...

Yeah, I will use the Double idea ....

Thank you so much...