Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
Very nice; I can't imagine doing any better than that. –  Michael Stern Feb 8 '12 at 2:03
add comment

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}
 *)
share|improve this answer
    
For me, that's a fair amount slower (.828 seconds for a given sample set versus .328 seconds for the original code). –  Michael Stern Feb 8 '12 at 1:00
    
@Michael, you are right; Map is more than twice faster. But, a a simpler version of ReplaceAll is still faster. –  kguler Feb 8 '12 at 1:29
    
Yes, the new version does speed the code by about 50%. Thank you. –  Michael Stern Feb 8 '12 at 1:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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