6

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.

Michael Stern
  • 4,695
  • 1
  • 21
  • 37

2 Answers2

7

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.

Mike Honeychurch
  • 37,541
  • 3
  • 85
  • 158
2

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}
 *)
kglr
  • 394,356
  • 18
  • 477
  • 896