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$
10
  • $\begingroup$ It's unclear what you mean by "day 25 should be contained". $\endgroup$ Jan 23, 2015 at 16:47
  • $\begingroup$ Somewhat related: (11746) $\endgroup$
    – Mr.Wizard
    Jan 23, 2015 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, 2015 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, 2015 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, 2015 at 19:05

3 Answers 3

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$
7
  • $\begingroup$ Very nice. +1 . $\endgroup$
    – Mr.Wizard
    Jan 23, 2015 at 19:49
  • 1
    $\begingroup$ Reminds me of this: (42660) $\endgroup$
    – Mr.Wizard
    Jan 23, 2015 at 19:58
  • $\begingroup$ @Kuba, "it is impossible to remain consistent " - even using V10 TimeObject? $\endgroup$ Jan 24, 2015 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, 2015 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$ Jan 24, 2015 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$
3
  • $\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$ Jan 23, 2015 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, 2015 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$ Jan 23, 2015 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.