# Calculating the average of two UTC times

## Recommended Posts

Hi there,

I would like to calculate the average of two UTC times, start and end each with three variables (H:M:S).

Excel has a nice function which considers the special format:

=AVERAGE(TIME(C2,D2,E2),TIME(F2,G2,H2))

I'd like to avoid the long way into Excel and back. Does AutoIT has a similar function I didn't find yet?

(X+Y)/2 is imprecise as hours and minutes consist of 60 not 100 entities.

Regards

Edited by revonatu

##### Share on other sites

This seems as accurate as any rounded return would be.  And is pretty much (X+Y)/2.  But i may have overlooked the cases that break it.

```\$Time1 = "02:14:05"
\$Time2 = "12:16:15"

\$aAvg = _AvgTime(\$Time1 , \$Time2)

\$hour = \$aAvg[0]
\$min = \$aAvg[1]
\$sec = \$aAvg[2]

msgbox(0, '' , \$hour & ":" & \$min & ":" & \$sec)

Func _AvgTime(\$sTime1 , \$sTime2)

\$sTime1 = stringreplace(\$sTime1 , ":" , "")
\$sTime2 = stringreplace(\$sTime2 , ":" , "")

\$sAvgTime = stringformat("%06s" , round((\$sTime1 + \$sTime2) / 2))

Local \$aOut[3]

\$aOut[0] =  stringleft(\$sAvgTime , 2)
\$aOut[1] =  stringmid(\$sAvgTime , 3 , 2)
\$aOut[2] =  stringright(\$sAvgTime , 2)

return \$aOut

EndFunc```

```,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)```

##### Share on other sites

Hi boththose,

Thanks for your try. Could you explain, what exactly this term does: "%06s" ? This seems to be the core of your code.

##### Share on other sites

Something like this?  The input parameters may be different than what you need but you could use StringFormat such as I used in the MsgBox to format hours, minutes and seconds into the required format.

```Global \$g_sTime1 = "00:00:00"
Global \$g_sTime2 = "23:59:59"

Global \$g_aAvgTime = AvgTime(\$g_sTime1, \$g_sTime2)

MsgBox(0x40000, "Average Time" , StringFormat("%02i:%02i:%02i", \$g_aAvgTime[0], \$g_aAvgTime[1], \$g_aAvgTime[2]))

Func AvgTime(\$sTime1, \$sTime2)
Local \$aTimeA = StringRegExp(\$sTime1, "^([0-1]\d|[2][0-3]):([0-5]\d):([0-5]\d)\$", 3) ;~ Split hh:mm:ss into an array.
If (@error) Then Return SetError(1) ;~ ^ Format must be hh:mm:ss, no spaces and 00:00:00 ~ 23:59:59.

Local \$aTimeB = StringRegExp(\$sTime2, "^([0-1]\d|[2][0-3]):([0-5]\d):([0-5]\d)\$", 3)
If (@error) Then Return SetError(2)

Local \$nAvgSeconds = ((((\$aTimeA[0] * 60) * 60) + (\$aTimeA[1] * 60) + \$aTimeA[2])    _ ;~ Convert to seconds for accuracy.
+  (((\$aTimeB[0] * 60) * 60) + (\$aTimeB[1] * 60) + \$aTimeB[2])) / 2

Return StringSplit(StringFormat("%02i:%02i:%02i", _ ;~ Convert seconds back to hh:mm:ss and format the output into an array.
Int(\$nAvgSeconds / 3600)                , _ ;~ \$aArray[0] = hh
Int(Mod(\$nAvgSeconds, 3600) / 60)       , _ ;~ \$aArray[1] = mm
Mod(Mod(\$nAvgSeconds, 3600), 60)), ":", 2 ) ;~ \$aArray[2] = ss
EndFunc```
Edited by Tekk

##### Share on other sites

Hi Tekk,

Thank you for your answer. The point is I spent quite a lot of code to get from GPS seconds to hours, minutes and seconds, that I need as separate variables later on

```\$StartTime  = FileReadLine(\$metaPath, 1)
\$StartTime  = StringMid(\$StartTime, 19, StringLen(\$StartTime)-20) ; GPS Time (Seconds) from meta file
\$SHour      = (\$StartTime/3600)
\$SMinute    = (\$SHour - Floor(\$SHour))*60
\$SSecond    = (\$SMinute - Floor(\$SMinute))*60
\$EndTime    = StringMid(\$EndTime, 17, StringLen(\$EndTime)-18) ; GPS Time (Seconds) from meta file
\$EHour      = (\$EndTime/3600)
\$EMinute    = (\$EHour - Floor(\$EHour))*60
\$ESecond    = (\$EMinute - Floor(\$EMinute))*60

\$Hour       = (\$SHour+\$EHour)/2
\$Minute     = (\$SMinute+\$EMinute)/2
\$Second     = (\$SSecond+\$ESecond)/2```

I hoped there would be a final function in AutoIT as in Excel. Before I define an own function, it's faster to source this step out to Excel.

So please no more suggestions for user defined functions.

##### Share on other sites

For my special problem I just found the solution: Avaraging the GPS seconds before calculating hours minutes and seconds!

It's so simple.

Edited by revonatu

##### Share on other sites

You can do it with a pretty short function:

```ConsoleWrite(_AvgTime("09:51:43", "10:04:38") & @LF)

Func _AvgTime(\$t1, \$t2)
Local Static \$t0 = "2015-01-01 "
Return StringRight(_DateAdd("s", Int(_DateDiff("s", \$t0 & \$t1, \$t0 & \$t2) / 2), \$t0 & \$t1), 8)
EndFunc```

\$t0 is not critical and could be inlined as well. Also it would be easy to check parameters correctness or to exchange \$t1 and \$t2 if \$t1 > \$t2.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

##### Share on other sites

what exactly this term does: "%06s"

stringformat in the help file probably explains it better, but it makes sure that the string is 6 characters and 0 pads the front if it is less.  And its not the core, it is purely formatting it for hhmmss so I dont have to worry about 5 and 6 character strings when putting colons back.

Edited by boththose

```,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)```

## Create an account

Register a new account

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...