How to extract a Date from an SQLDateTime object in Mathematica

Question

I am trying to do a plot of a time series with DateListPlot. I want to feed it a time series I obtain from an SQL database. When I retrieve the time series the list is composed of SQLDateTime entries that DateListPlot doesn't understand.

In[24]:= t=SQLExecute[conn, "select timestamp,value from timeseries order by timestamp asc"]

Out[24]={{SQLDateTime[{2010,1,1}],12.3},{SQLDateTime[{2010,1,2}],12.51}}

Doesn't work: In[25]:= DateListPlot[t]

DateListPlot requires a Date tuple and doesn't understand SQLDateTime. What can I do?

Yankee

Answer 1

The answer:

In[1]:= SQLDateTime[{2001, 5, 7}][[1]]
Out[1]:= {2001,5,7}

Mathematica thinks of everything very similarly internally. What you see as {1, 2, 3} is actually List[1,2,3]. The Part function (denoted by [[...]]) works just as well on any function, not just List.

The quick and dirty way to apply this in your case:

{#[[1,1]],#[[2]]}& /@ SQLExecute[...]

Fattal

It might be more efficient and clear to do something like SQLExecute[...] /. SQLDateTime[l_]:>l or SQLExecute[...] /. SQLDateTime -> Identity when you have multiple columns of SQLDateTime objects. Also, DateListPlot understands integer Unix-style timestamps, so you could replace SQLDateTime with AbsoluteTime to convert to those, since AbsoluteTime[{timespec...}] gives you a timestamp. – Archivist

Yeah, I did call it quick and dirty. Using a rule is definitely more clear, and more efficient in terms of amount of code. I'm not sure if a rule is in general faster, though, because it has to search through everything, while if you know the structure of your data, you can use something like this which doesn't have to examine anything except the places you know SQLDateTime occurs. – Fattal