Select Range of SQLDateTimes

Question

table = {{ID1, SQLDateTime[{1978, 1, 10, 0, 0, 0.`}]}, 
{ID2, SQLDateTime[{1999, 1, 10, 0, 0, 0.`}]}, 
{ID3, SQLDateTime[{2010, 9, 10, 0, 0, 0.`}]}, 
{ID4, SQLDateTime[{2011, 1, 10, 0, 0, 0.`}]}}

I'd like to return all cases in table in which the SQLDateTime is within the last year (DatePlus[{-1, "Year"}]). How do I specify a search for those cases?

score 6 · Answer 1 · answered Aug 08 '11 at 18:50

6

You could also use DateDifference:

Cases[table, {a_, SQLDateTime[b_]} /; 
  DateDifference[b, DateList[], "Year"][[1]] <= 1]

answered Aug 08 '11 at 18:50

Heike

24,102
2
31
45

Sjoerd C. de Vries · Accepted Answer · 2011-08-08T19:27:00.157

4

Select[table, (AbsoluteTime[ DatePlus[{-1, "Year"}]] <= 
    AbsoluteTime[ #[[2, 1]]] <= AbsoluteTime[ ] &)]

(* ==> {{ID3, SQLDateTime[{2010, 9, 10, 0, 0, 0.}]}, 
        {ID4,SQLDateTime[{2011, 1, 10, 0, 0, 0.}]}
        } 
*)

Small update (pre-caching of Date[], based on Leonid's comments):

With[
 {date = Date[]},
 Select[table, 
    (AbsoluteTime[ DatePlus[date, {-1, "Year"}]] <= 
     AbsoluteTime[ #[[2, 1]]] <= AbsoluteTime[date ] &)]
 ]

This also removes a problem with the original DatePlus[{-1, "Year"}] which only takes today's date into account and not the current time.

edited Aug 08 '11 at 19:27

answered Aug 08 '11 at 18:54

Sjoerd C. de Vries

16,122
3
42
94

@Leonid ;-D A toast on future dates – Sjoerd C. de Vries Aug 08 '11 at 19:13
@Sjoerd Yeah, I'll drink to that :) – Leonid Shifrin Aug 08 '11 at 19:18

Select Range of SQLDateTimes

2 Answers2