3
$\begingroup$

Suppose that I have a date range list

drlist = {{"2015-01-01", "2015-01-05"}, 
          {"2015-01-07", "2015-01-07"}, 
          {"2015-01-12", "2015-01-23"}}

and a range of date

sdate = "2015-01-04";
edate = "2015-01-14";
r = {sdate, edate};

to make things easy to understand, {"2015-01-04", "2015-01-14"} in reality represent something like {"2015-01-04-00-00","2015-01-14-12-00"}, i.e., the date start from 0:00 but end at some time of the day, say "12:00" is enough for me.

Then my question is how can I get the range of date that in r but not in drlist, e.g., it should be output something like:

cdrlist = {{"2015-01-05", "2015-01-06"}, {"2015-01-07", "2015-01-11"}}

and if we set endate = "2015-01-25", then the output should be

cdrlist = {{"2015-01-05", "2015-01-06"}, 
           {"2015-01-07", "2015-01-11"}, 
           {"2015-01-23", "2015-01-25"}}

It should be noted that in this case the day "25" should be contained included.

UPDATE

To clarify the confusing results I were demonstrated, let us just consider the day, the day range we have is union of closed intervals: $$ drlist=[1.0,5.5]\sqcup[7.0,7.5]\sqcup[12.0,23.5] $$ and the day range we set is just a closed interval: $$ r=[4.0,25.5] $$ thus the set $r\setminus drlist$ equals to $$ r\setminus drlist=(5.5,7)\sqcup(7.5,12)\sqcup(23.5,25.5], $$ and translated this back to day range shoulde be $$ 5-6,7-11,23-25. $$ Let me explain why $(5.5,7)$ should be $5-6$? Note that in our notation, $5-6$ represent $[5.0,6.5]$ which is exactly the interval contains $(5.5,7)$, since the $0.5$ should be understand very close to $1.0$. Also $(23.5,25.5]=(24,26)=[23,25]$ in above sense.

$\endgroup$
  • $\begingroup$ It's unclear what you mean by "day 25 should be contained". $\endgroup$ – Chris Degnen Jan 23 '15 at 16:47
  • $\begingroup$ Somewhat related: (11746) $\endgroup$ – Mr.Wizard Jan 23 '15 at 18:16
  • $\begingroup$ I don't understand why dates "2015-01-06" and "2015-01-07" in the output are not considered contiguous, yielding a single range cdrlist = {{"2015-01-05", "2015-01-11"}}. Would you clarify this please? $\endgroup$ – Mr.Wizard Jan 23 '15 at 18:32
  • 1
    $\begingroup$ @Mr.Wizard "2015-01-06" is `"2015-01-06" at 12:00. so there is 12h to "2015-01-07". $\endgroup$ – Kuba Jan 23 '15 at 19:00
  • 1
    $\begingroup$ @Mr.Wizard Ok, now I know what was not ok for me. Let's switch to only days for simplicity. And let's drlist = {{1,2}} which schould be intepreted as {1, 2.5}. But if we want to take away {0, 1} (*{0,1.5}*) then the exact result should be {1.5, 2.5} which in "base form" is {1,2} so even though the intersection is not empty, the drlist is the same. $\endgroup$ – Kuba Jan 23 '15 at 19:05
4
$\begingroup$

I claim it is impossible to remain consistent with your convention of adding hours to second elements. Either we keep track of hours everythwere or we are in trouble.

So now I'm going to ignore that :)

drlist = {{"2015-01-01", "2015-01-05"}, 
          {"2015-01-07", "2015-01-08"}, 
          {"2015-01-12", "2015-01-23"}};
r = {"2015-01-04", "2015-01-14"};

I'm going to work on seconds:

restTime = Interval @@ Partition[Flatten[{0, Map[AbsoluteTime, drlist, {2}], 10^10}],
                                 2]

main = Interval[AbsoluteTime /@ r]
Interval[{0, 3629059200}, {3629404800, 3629577600}, 
         {3629664000, 3630009600}, {3630960000, 10000000000}]

Interval[{3629318400, 3630182400}]
List @@ Map[DateList[#][[;; 3]] &, 
            IntervalIntersection[main, restTime],
            {2}]
{{{2015, 1, 5}, {2015, 1, 7}}, {{2015, 1, 8}, {2015, 1, 12}}}

I'm living formatting to you. Also, if you want to add DatePlus for those second elements, feel free to do so :)

$\endgroup$
  • $\begingroup$ Very nice. +1 . $\endgroup$ – Mr.Wizard Jan 23 '15 at 19:49
  • 1
    $\begingroup$ Reminds me of this: (42660) $\endgroup$ – Mr.Wizard Jan 23 '15 at 19:58
  • $\begingroup$ @Kuba, "it is impossible to remain consistent " - even using V10 TimeObject? $\endgroup$ – alancalvitti Jan 24 '15 at 14:46
  • $\begingroup$ @alancalvitti If you keep all information sure, but OP wants to store dates with "day" precisison only. Also, maybe I'm wrong and it will not be a problem but I don't like it so let me ignore it :) $\endgroup$ – Kuba Jan 24 '15 at 15:10
  • $\begingroup$ @Kuba, this approach using AbsoluteTime normal form to leverage Interval arithmetic has appeal. Have you thought about generalizing to dates and times? Temporal logic is critical in databases nowadays. $\endgroup$ – alancalvitti Jan 24 '15 at 17:20
2
$\begingroup$

I do not follow your logic for why {{"2015-01-05", "2015-01-06"}, {"2015-01-07", "2015-01-11"}} is split into two ranges since "2015-01-06" and "2015-01-07" are adjacent, however overlooking that here is a fairly clean approach.

Helper functions:

toList[x : {_String, _String}, os_: {12, "Hour"}] := 
  MapAt[DatePlus[#, os] &, DateRange @@ DateList /@ x, -1]

toString = DateString[#, {"Year", "-", "Month", "-", "Day"}] &;

And with data:

drlist = {{"2015-01-01", "2015-01-05"},
          {"2015-01-07", "2015-01-07"},
          {"2015-01-12", "2015-01-23"}};

r = {"2015-01-04", "2015-01-25"};

Process:

Complement[toList[r], Join @@ toList /@ drlist];

#[[{1, -1}]] & /@ Split[%, #[[3]] + 1 == #2[[3]] &];

Map[toString, %, {2}]
{{"2015-01-05", "2015-01-11"}, {"2015-01-23", "2015-01-25"}}

You may also find inspiration from answers to my own question back on Stack Overflow:

$\endgroup$
  • $\begingroup$ The OP says he wants "the range of date that in r but not in drlist", but you have returned "2015-01-05" which is in drlist. $\endgroup$ – Chris Degnen Jan 23 '15 at 21:31
  • $\begingroup$ @Chris the OP includes "2015-01-05" in his own output example! The format is not clear. $\endgroup$ – Mr.Wizard Jan 23 '15 at 21:34
  • $\begingroup$ I think he just made that up. He also has "2015-01-06" and "2015-01-07" which should be merged into the same range. $\endgroup$ – Chris Degnen Jan 23 '15 at 21:37
2
$\begingroup$

This first part finds the complementary dates.

sdate = "2015-01-04";
edate = "2015-01-14";
r = {sdate, edate};

drlist = {
   {"2015-01-01", "2015-01-05"},
   {"2015-01-07", "2015-01-07"},
   {"2015-01-12", "2015-01-23"}};

If[$VersionNumber == 10,
 fromDate[datelist_List] :=
  QuantityMagnitude@DateDifference[{1899, 12, 30}, datelist],
 fromDate[datelist_List] := DateDifference[{1899, 12, 30}, datelist]] (* Note 1 *)

undoDate[int_Integer] := StringJoin@Riffle[
   ToString /@ DatePlus[{1899, 12, 30}, int] , "-"]

r2 = r /. a_String :> fromDate[Take[DateList[{a, {"Year", "Month", "Day"}}], 3]];
drlist2 = drlist /. a_String :> fromDate[Take[DateList[{a, {"Year", "Month", "Day"}}], 3]];

r3 = Range[0, #2 - #1] + #1 & @@ r2;
drlist3 = Union@Flatten[Range[0, #2 - #1] + #1 & @@@ drlist2];

cdrlist1 = Complement[r3, drlist3];
undoDate /@ cdrlist1

{"2015-1-6", "2015-1-8", "2015-1-9", "2015-1-10", "2015-1-11"}

Now finding the date ranges. (It would be interesting to see better ways of doing this.)

cdrlist2 = Transpose[{cdrlist1,
    Append[Differences@cdrlist1 /. x_ /; x > 1 -> 0, 0]}];

If[cdrlist2[[1, 2]] == 1, output = {},
  output = List@Take[cdrlist2, 1];
  cdrlist2 = Rest@cdrlist2];

While[Length[cdrlist2] > 0,
 a = TakeWhile[cdrlist2, Last[#] == 1 &];
 b = Drop[cdrlist2, Length[a]];
 If[b == {}, AppendTo[output, a],
  AppendTo[a, First[b]];
  AppendTo[output, a];
  cdrlist2 = Rest[b]]]

cdrlist3 = Replace[output, x_List /; Length[x] > 1 :> x[[{1, -1}]], {1}];

cdrlist = cdrlist3 /. {a_Integer, _Integer} :> undoDate[a]

{{"2015-1-6"}, {"2015-1-8", "2015-1-11"}}

Note 1. fromDate[] actually converts a date to an Excel serial date, but valid only from 1st March 1990 due to Excel's compatibility carry-over of the Lotus 1-2-3 leap-year bug. (1990 is not a leap-year.)

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.