revonatu

Calculating the average of two UTC times

8 posts in this topic

#1 ·  Posted (edited)

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 this post


Link to post
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    = FileReadLine($metaPath, 2)
$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 this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

It's so simple. :)

Anyway, thank you for your help. Your suggestions might be helpful in an other case.

Edited by revonatu

Share this post


Link to post
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.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
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 this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now