compare dates on day and month number

[cretaceous]

I need to compare two dates in php based on the day and month and if greater than or equal to 6 months show an alert

the comparison is non logical to me, as I need to compare the day number and month number, while ignoring the fact that February and 30 day months are short

so
23 Jan 2011 :: 23 June 2011 is true
31 October 2011 :: 1 April 2012 is true
(ie one day over 6 months)

but
23 Jan 2011 :: 22 June 2011 is false
31 October 2011 :: 31 March 2012 is false
(exactly 6 months)

so there's no absolute number of days to check
I can think of messy ways to do it but is there anything clean?
Or am I missing something obvious ?

[bradgrafelman]

Any chance these dates are coming from a database? If so, it would be simplest to do the comparison in the SQL query itself.

Otherwise, you might try:

1. Use [man]strtotime/man to add 6 months to the earlier date (and simultaneously converting that new date into a Unix timestamp).

2. Use [man]strtotime/man to convert the later date into a Unix timestamp as well.

3. Compare the newly generated date from #1 with the date from #2 (using simple integer comparison).

[cretaceous]

ah, no hiding place..
yes it comes from db..

but the whole task is to check if a sequence of bookings made by one person, when added together, add to more than 6 months

so there could be many bookings (of one week for example) and if it is all contiguous and adds to more than 6 months...

also bookings could be added out of date order so I have to base the search by looking back and looking forwards

so I figured the best way was get all the bookings into a php array, then based off the central one that is being examined, see which ones had contiguous (or overlapping) dates [actually there can't be any overlapping dates as that is checked in another code block but it seemed safer to leave it in]

then if a match is found check that item's dates

so now I've got the date range extremes and I then look to see if it's over the (illogical) 6 month specification

which led me to make the first post..

I bet there is a really cool single query bit of Sql but it's beyond me!

[bradgrafelman]

Just so I understand.. given some ID value for a "person," you want to check if there are any combinations of rows containing that person's bookings that cover a contiguous span of time equal to or greater than 6 months?

Can you show us the schema of the relevant table(s)? Sounds like it should be rather straightforward to do this in a single SQL query.

[cretaceous]

bradgrafelman;10990169 wrote:

Just so I understand.. given some ID value for a "person," you want to check if there are any combinations of rows containing that person's bookings that cover a contiguous span of time equal to or greater than 6 months?

yes, that's correct
under 6 months is permitted - longer than that triggers a warning

the relevant fields are:

bk_id :: the booking id
start_d date :: book start
end_d date :: book end
cust_id :: the customer
unit_ref_id :: the location but we can ignore that as it's just one more 'AND' selecter

of course contiguous dates means start day of booking B would equal end day of booking A +1

and with the proviso the 6 month definition is 3 Jan to 2 June
..might be easier if 6 months was defined as a set number of days or weeks

[Derokorian]

well as far as doing it in php goes you could do something along these lines:

<?php

function sixMonths($date1,$date2) {
	$m1 = date('m',strtotime($date1));
	$m2 = date('m',strtotime($date2));

$d1 = date('d',strtotime($date1));
$d2 = date('d',strtotime($date2));

if( $m2 <=6 && $m1 > $m2 ) {
	$m2 += 12;
}

if( ($m2-$m1) > 6 ) {
	return 'Warning: Time elapsed greater than 6months';
} elseif( ($m2-$m1) == 6 ) {
	if( $d2 > $d1 ) {
		return 'Warning: Time elapsed greater than 6months';
	} else {
		return 'Time elapsed it less than or equal to 6months';
	}
} else {
	return 'Time elapsed is less than or equal to 6months';
}
}

$dates = array('1 Jan 2011'=>'1 July 2011','3 Mar 2010'=>'4 Sept 2010','10 Oct 2010'=>'11 Apr 2011');

foreach( $dates as $k => $v ) {
	echo sixMonths($k,$v) . '<br>';
}

/**
 * Expected output:
 * Less than
 * greater than
 * greater than
 */

[Derokorian]

And if for some reason, the year becomes an issue (time elapsed is 19 months or some wicked) I added that:

function sixMonths($date1,$date2) {
	$y1 = date('y',strtotime($date1));
	$y2 = date('y',strtotime($date2));

$m1 = date('m',strtotime($date1));
$m2 = date('m',strtotime($date2));

$d1 = date('d',strtotime($date1));
$d2 = date('d',strtotime($date2));

if( ($y2-$y1) > 1 )
	return 'Warning: Time elapsed greater than 6 months';

if( $m2 <=6 && $m1 > $m2 )
	$m2 += 12;

if( ($m2-$m1) > 6 ) {
	return 'Warning: Time elapsed greater than 6months';
} elseif( ($m2-$m1) == 6 ) {
	if( $d2 > $d1 ) {
		return 'Warning: Time elapsed greater than 6months';
	} else {
		return 'Time elapsed it less than or equal to 6months';
	}
} else {
	return 'Time elapsed is less than or equal to 6months';
}
}

[Weedpacket]

I agree that it would probably be doable in SQL (though I expect it would involve more than a mere WHERE clause on existing columns). But I've got some PHP code sitting here right now that would simplify the problem if it were done there.

Dates are represented as Unix timestamps. An interval is represented as an array of two dates (begin, end). This function takes an array of such intervals and returns a new array of intervals that (a) between them cover exactly the same periods, but (b) are nonoverlapping. So if there is any period that is more than six months long, it will be covered by a single interval.

function normalise_intervals($intervals)
{
	$n = count($intervals);
	if($n == 0 || $n == 1)
	{
		return $intervals;
	}
	usort($intervals, function($a, $b)
	{
		return $b[0] - $a[0];
	});

$ends_before = function($interval, $when)
{
	return $interval[1] < $when;
};
$overlaps = function($a, $b)use($ends_before)
{
	return !($ends_before($a, $b[0]) || $ends_before($b, $a[0]));
};
$covering_interval = function($a, $b)use($intervals)
{
	return array(min($a[0], $b[0]), max($a[1], $b[1]));
};

$i = 0;
for($j = 1; $j < $n; ++$j)
{
	if($overlaps($intervals[$i], $intervals[$j]))
	{
		$intervals[$i] = $covering_interval($intervals[$i], $intervals[$j]);
		unset($intervals[$j]);
	}
	else
	{
		$i = $j;
	}
}
return array_values($intervals);
}

[cretaceous]

thanks weed, brad and derokorian I couldn't see the wood for the trees on this one..

I'll try that function of yours Weed. though I'm not sure if it will work as you say " if an interval is over 6 months it will be covered by a single interval" but I need "if sum of intervals is over 6 months"
but I need to figure out what your function does first

[Weedpacket]

Ah, but I said "if a period is more than six months long", not "interval" - where I should have said that a period was a contiguous span of time.