[PowerShell 5.1] [Function] - Issue with Date Comparison

icepoc · February 21, 2020, 12:00pm

Hi,

My data is very simple, 4 datetimes.

Start 1 time
End 1 time

Start 2 time
End 2 time

What i am trying to do is work out what type of overlap it is. I have the query working out if it is an overlap working OK.

Code:

function OverlapType {
    param ($Time1Start, $Time1End, $Time2Start, $Time2End)
    if ($Time2Start -lt $Time1End -and $Time2Start -gt $Time1Start -and $Time2End -gt $Time1End ) {
        $OverlapType = "Start2 Over End1"
    }
    elseif ($Time2Start -lt $Time1Start -and $Time2End -lt $Time1End -and $Time2Start -gt $Time1Start) {
        $OverlapType = "End2 Over Start1"
    }
    elseif ($Time1Start -lt $Time2End -and $Time2Start -lt $Time1Start -and $Time1End -gt $Time2End ) {
        $OverlapType = "Start1 Over End2"
    }
    elseif ($Time1Start -lt $Time2Start -and ($Time1End -lt $Time2End -and $Time1Start -gt $Time2Start)  ) {
        $OverlapType = "End1 Over Start2"
    }
    elseif ($Time1Start -gt $Time2Start -and $Time1End -lt $Time2End) {
        $OverlapType = "1 In Between 2" 
    }
    elseif ($Time2Start -gt $Time1Start -and $Time2End -lt $Time1End) {
        $OverlapType = "2 In Between 1"
    }
    else {
        $OverlapType = "Unknown From Function"
    }
    return $OverlapType
}

Can anyone point me in the right direction to fix this issue?

If you take this time range as an example, it outputs incorrect as 2 between 1 when it should be 1 between 2:

Time1 start and end: 21/02/2020 09:36:00 - 21/02/2020 10:19:00 Time2 start and end: 21/02/2020 09:30:00 - 21/02/2020 18:00:00

Many Thanks

POC

icepoc · February 21, 2020, 12:31pm

Also tried this and it outputs as Unknown From Function:

function OverlapType {
    param ([datetime]$Time1Start, [datetime]$Time1End, [datetime]$Time2Start, [datetime]$Time2End)
    $OverlapType = $null
    if ((([datetime]$Time2Start) -lt ([datetime]$Time1End)) -and (([datetime]$Time2Start) -gt ([datetime]$Time1Start)) -and ([datetime]$Time2End )-gt ([datetime]$Time1End) ) {
        $OverlapType = "Start2 Over End1"
    }
    elseif (([datetime]$Time2Start) -lt ([datetime]$Time1Start) -AND (([datetime]$Time2End )-lt ([datetime]$Time1End) -AND ([datetime]$Time2Start) -gt ([datetime]$Time1Start))) {
        $OverlapType = "End2 Over Start1"
    }
    elseif ((([datetime]$Time1Start) -lt ([datetime]$Time2End )-and ([datetime]$Time2Start) -lt ([datetime]$Time1Start)) -AND (([datetime]$Time1End) -gt [datetime]$Time2End) ) {
        $OverlapType = "Start1 Over End2"
    }
    elseif (([datetime]$Time1Start) -lt ([datetime]$Time2Start) -AND (([datetime]$Time1End) -lt ([datetime]$Time2End )-AND ([datetime]$Time1Start) -gt ([datetime]$Time2Start))  ) {
        $OverlapType = "End1 Over Start2"
    }
    elseif (([datetime]$Time1Start) -gt ([datetime]$Time2Start) -AND ([datetime]$Time1End) -lt [datetime]$Time2End) {
        $OverlapType = "1 In Between 2" 
    }
    elseif (([datetime]$Time2Start) -gt ([datetime]$Time1Start) -AND ([datetime]$Time2End )-lt ([datetime]$Time1Start)) {
        $OverlapType = "2 In Between 1"
    }
    else {
        $OverlapType = "Unknown From Function"
    }
    
    return $OverlapType
}

Olaf · February 21, 2020, 3:09pm

Maybe you should start a little smaller and extend your code case by case …

function OverlapType {
    param (
        [datetime]$Time1Start, 
        [datetime]$Time1End, 
        [datetime]$Time2Start, 
        [datetime]$Time2End
    )

    if ($Time2Start -ge $Time1Start -and $Time2Start -le $Time1End ) {
        "Start2 between Start1 and End1"
    }
    if ($Time2End -ge $Time1Start -and $Time2End -le $Time1End ) {
        "End2 between Start1 and End1"
    }
    if ($Time1Start -ge $Time2Start -and $Time1Start -le $Time2End ) {
        "Start1 between Start2 and End2"
    }
    if ($Time1End -ge $Time2Start -and $Time1End -le $Time2End ) {
        "End1 between Start2 and End2"
    }
}

OverlapType -Time1Start 'Jan 1 2020' -Time1End 'Jan 31 2020' -Time2Start 'Jan 15 2020' -Time2End 'Feb 15 2020'

When you post code please use the code tag button named “PRE”. Thanks

icepoc · February 24, 2020, 5:18am

[quote quote=205251]Maybe you should start a little smaller and extend your code case by case …

PowerShell

23 lines

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

function OverlapType {

param (

[datetime]$Time1Start,

[datetime]$Time1End,

[datetime]$Time2Start,

[datetime]$Time2End

)

if ($Time2Start -ge $Time1Start -and $Time2Start -le $Time1End ) {

"Start2 between Start1 and End1"

}

if ($Time2End -ge $Time1Start -and $Time2End -le $Time1End ) {

"End2 between Start1 and End1"

}

if ($Time1Start -ge $Time2Start -and $Time1Start -le $Time2End ) {

"Start1 between Start2 and End2"

}

if ($Time1End -ge $Time2Start -and $Time1End -le $Time2End ) {

"End1 between Start2 and End2"

}

OverlapType -Time1Start 'Jan 1 2020' -Time1End 'Jan 31 2020' -Time2Start 'Jan 15 2020' -Time2End 'Feb 15 2020'

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

When you post code please use the code tag button named “PRE”. Thanks

[/quote]

Hi Olaf,

Thanks for the advice. I did do that but i will try again and come back unless you spotted something?

Also, i thought i used the code tags and currently appears to me the same as yours so i am a little confused? Unless someone fixed it?

Many Thanks

POC

Olaf · February 24, 2020, 6:31am

The mod kvprasoon did it for you. You can see on the little note below your posts.

icepoc · February 24, 2020, 7:58am

So i found the problems, copy paste errors and small complexities from our company

For completeness, here is my script to look at finding overlapping time entries and classifying them to work out the correct overlapping duration time. Constructive feedback and discussion is of course welcome. If this can be done better, i am all ears

Notes:

$SQLPull in my scenario is the same data being compared with each other.
Does not yet understand exactly matching entries. Can be added quite simply, i just dont really have a need for it in my scenario.
$EntriesArray and $EntriesCount are not required. these are there for debugging.
It DOES find the entry conflicting twice each time. This is where entry 1 conflicts with entry 2, and vice versa. Working on this.

Code:


#Populate 2 variable objects with the same data
$Time1 = Invoke-Sqlcmd -Query $SQLPull -ServerInstance "xxxxxxxxx" -Username "xxxxxxx" -Password "xxxxxxxxxxxxx" -Database "xxxxxxxxxxx"
$Time2 = Invoke-Sqlcmd -Query $SQLPull -ServerInstance "xxxxxxxxxxxx" -Username "xxxxxxxxxxxxx" -Password "xxxxxxxxxxxx" -Database "xxxxxxxx"
  
  
  
#endregion
  


#region overlap type function
function OverlapType {
    param ([datetime]$Time1Start, [datetime]$Time1End, [datetime]$Time2Start, [datetime]$Time2End)

    if ($Time1Start -ge $Time2Start -AND $Time1End -le $Time2End) {
        $OverlapType = "1 In Between 2" 
    }
    elseif ($Time2Start -ge $Time1Start -AND $Time2End -le $Time1End) {
        $OverlapType = "2 In Between 1"
    }
    elseif ((($Time2Start) -lt ($Time1End)) -and (($Time2Start) -gt ($Time1Start)) -and ($Time2End ) -gt ($Time1End) ) {
        $OverlapType = "Start2 Over End1"
    }
    elseif (($Time2Start) -lt ($Time1Start) -AND (($Time2End ) -lt ($Time1End) -AND ($Time2Start) -gt ($Time1Start))) {
        $OverlapType = "End2 Over Start1"
    }
    elseif ((($Time1Start) -lt ($Time2End ) -and ($Time2Start) -lt ($Time1Start)) -AND (($Time1End) -gt $Time2End) ) {
        $OverlapType = "Start1 Over End2"
    }
    elseif (($Time1Start) -lt ($Time2Start) -AND (($Time1End) -lt ($Time2End ) -AND ($Time1Start) -gt ($Time2Start))  ) {
        $OverlapType = "End1 Over Start2"
    }
    else {
        $OverlapType = "Unknown From Function"
    }

    return $OverlapType
}
#endregion

$EntriesArray = @()
$EntriesCount = $null
#region process and output
foreach ($Entry in $Time1) {
    foreach ($SecondEntry in $Time2 ) {
        if ($SecondEntry.Time_Start -lt $Entry.Time_End -and ($null -eq $SecondEntry.Time_End -OR $SecondEntry.Time_End -gt $Entry.Time_Start) -and $Entry.Time_Recid -ne $SecondEntry.Time_Recid -and $Entry.member_id -eq $SecondEntry.member_id) {
            #Set time entry start and end times and send to function to work out the overlap type
            ($Time1Start) = $Entry.Time_Start
            ($Time1End) = $Entry.Time_End
            ($Time2Start) = $SecondEntry.Time_Start
            ($Time2End ) = $SecondEntry.Time_End  
            $OverlapConfirm = OverlapType -Time1Start $Time1Start -Time1End $Time1End -Time2Start $Time2Start -Time2End $Time2End
            #Find correct duration time based on overlap type
            switch ($OverlapConfirm) {
                "Start2 Over End1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_Start -End $Entry.Time_End }
                "2 In Between 1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_Start -End $SecondEntry.Time_End }
                "End2 Over Start1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_End -End $Entry.Time_Start }
                "Start1 Over End2" { $Duration = New-TimeSpan -Start $Entry.Time_Start -End $SecondEntry.Time_End }
                "1 In Between 2" { $Duration = New-TimeSpan -Start $Entry.Time_Start -End $Entry.Time_End }
                "End1 Over Start2" { $Duration = New-TimeSpan -Start $Entry.Time_End -End $SecondEntry.Time_Start }
                "Unknown From Function" { "Function Didnt Pass A Correct Response" }
                Default { "Unknown From Switch" }
            }
            #Entry Found Output
            Write-Host "Overlap Type: " $OverlapConfirm -ForegroundColor Red
            Write-Host ([datetime]$Time1Start) " - " ([datetime]$Time1End)   " - " $Entry.Time_RecID
            Write-Host ([datetime]$Time2Start) " - " ([datetime]$Time2End )  " - " $SecondEntry.Time_RecID
            Write-Host "Duration "  $Duration.TotalMinutes
            $EntriesArray += $Entry.Time_RecID 
            $EntriesCount++
        }
    }
}
$EntriesArray
$EntriesCount
#endregion

Added code tages i could see, cant see the “PRE” one? where is that one?

Many Thanks POC

Olaf · February 24, 2020, 9:47am

Added code tages i could see, cant see the “PRE” one? where is that one?

It's right above the editor window. It's the 3rd button from the right hand side when you select the "Text"-View on the right hand side.

Topic		Replies	Views
compare time PowerShell Help	0	190	December 31, 2011
Date Comparison - Strangely failing PowerShell Help	6	403	November 30, 2018
Help Understanding Comparison of Date/Time Objects PowerShell Help	3	276	August 13, 2015
Trying to understand what is going on in this bit of code. PowerShell Help	2	229	November 18, 2020
time Between Dates.. PowerShell Help	0	208	December 31, 2011

[PowerShell 5.1] [Function] - Issue with Date Comparison

Related topics