6
$\begingroup$

I often run database queries that return sets of {date, datum} objects. A hypothetical query looks like

testData = SQLExecute[commodDB, "select date,price from commoddb.tbcommodprices
where futurePrefix='XB'"];

and the results look like

{{SQLDateTime[{2005, 10, 3}], 188.88}, {SQLDateTime[{2005, 10, 4}], 
184.53}, {SQLDateTime[{2005, 10, 5}], 
176.5}, {SQLDateTime[{2005, 10, 6}], 
172.}, {SQLDateTime[{2005, 10, 7}] . . . etc}

To do anything useful with these, I need to convert the SQLDateTime[] objects into Mathematica datelists. With the example above, this can be done with

Map[{#[[1]][[1]], #[[2]]} &, testData]

But this takes longer than I would like; I feel as though this conversion is trivial and should be basically instantaneous, but for typical queries (which admittedly return thousands of results), the conversion takes half a second or more.

Can anybody suggest a quicker way to convert my SQLDateTime[]s into DateLists?

Thanks.

$\endgroup$
7
$\begingroup$

The fastest way should be to use Part I think, particularly as your list gets larger.

data={{SQLDateTime[{2005, 10, 3}], 188.88}, {SQLDateTime[{2005, 10, 4}], 
184.53}, {SQLDateTime[{2005, 10, 5}], 
176.5}, {SQLDateTime[{2005, 10, 6}], 
172.}, {SQLDateTime[{2005, 10, 7}] . . . etc}

For example

data[[All, 1]] = data[[All, 1, 1]]

leaves you with

data

{{{2005, 10, 3}, 188.88}, {{2005, 10, 4},184.53}, {{2005, 10, 5}, 
    176.5}, {{2005, 10, 6},172.}, {{2005, 10, 7} . . . etc}

This is another way but probably slower:

data /. SQLDateTime -> Identity

Timings

Using kgulers data generator with a list of 10000 elements:

ClearSystemCache[];
Timing[tmp1 = data /. SQLDateTime[x_] :> DateList[x];]
{0.469574, Null}

ClearSystemCache[];
Timing[tmp2 = data /. SQLDateTime[x_] :> x;]
{0.024052, Null}

ClearSystemCache[];
Timing[tmp3 = Map[{#[[1]][[1]], #[[2]]} &, data];]
{0.043545, Null}

ClearSystemCache[];
Timing[tmp4 = data /. SQLDateTime -> Identity;]
{0.060517, Null}

ClearSystemCache[];
Timing[data[[All, 1]] = data[[All, 1, 1]];]
{0.006412, Null}

tmp1 == tmp2 == tmp3 == tmp4 == data
True

Part is the fastest.

Edit

I'm not sure why you would actually do SQLDateTime[x_] :> DateList[x], the "x" is already a date list. SQLDateTime[x_]->x is sufficient. ...but since Part is faster I guess the discussion is redundant.

$\endgroup$
  • $\begingroup$ Very nice; I can't imagine doing any better than that. $\endgroup$ – Michael Stern Feb 8 '12 at 2:03
2
$\begingroup$

Maybe

 sqlToDatelist:=# /. SQLDateTime[x_] :> DateList[x]&

Or

 sqlToDatelist2:=# /. SQLDateTime[x_] :> x  &

Applied to

      {{SQLDateTime[{2005, 10, 3}], 188.88}, {SQLDateTime[{2005, 10, 4}], 
  184.53}, {SQLDateTime[{2005, 10, 5}], 
  176.5}, {SQLDateTime[{2005, 10, 6}], 
  172.}, {SQLDateTime[{2005, 10, 7}], 176.5}} 

They both give

  (*
  ==>
  {{{2005,10,3,0,0,0.`},188.88`},{{2005,10,4,0,0,0.`},184.53`},{{2005,
   10,5,0,0,0.`},176.5`},{{2005,10,6,0,0,0.`},172.`},{{2005,10,7,0,0,0.`},176.5`}}
  *)

But, Map is much faster:

Random dataset generator:

  randdata = {SQLDateTime[#], RandomReal[]} &@DateList[#] & /@ 
  RandomInteger[{AbsoluteTime["2001-1-1"], 
   AbsoluteTime["2012-1-1"]}, #] &

random date set:

  dt = randdata@1000000;

Timings:

 ClearSystemCache[]; dt2= sqlToDatelist@dt; // Timing 
 (*
  ==> {6.74, Null}
 *)

whereas with Map

 ClearSystemCache[]; dt2=Map[{#[[1]][[1]], #[[2]]} &, dt]; // Timing
 (*
 {2.745, Null}
 *)

sqlToDateList2 avoids the DateList overhead in the replacement rule, so it is much faster:

 ClearSystemCache[]; dt2=sqlToDatelist2@dt; // Timing
 (*
  ==>{1.139, Null}
 *)
$\endgroup$
  • $\begingroup$ For me, that's a fair amount slower (.828 seconds for a given sample set versus .328 seconds for the original code). $\endgroup$ – Michael Stern Feb 8 '12 at 1:00
  • $\begingroup$ @Michael, you are right; Map is more than twice faster. But, a a simpler version of ReplaceAll is still faster. $\endgroup$ – kglr Feb 8 '12 at 1:29
  • $\begingroup$ Yes, the new version does speed the code by about 50%. Thank you. $\endgroup$ – Michael Stern Feb 8 '12 at 1:43

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.