Python: SpatiaLite table to numpy recarray with datetime format?

Question

I posted this question at the QGIS-user forum but it might be too technical, thus I take a chance and try here as well.

My plan is to have a QGIS plugin that reads and plots time series data corresponding to selected points in a vector layer(the points represents different measuring stations and I need to quickly view the time series from each and one of them, sometimes several stations in same plot). Consequently I need Python to read time series data from a Spatialite table and then plot it with Matplotlib.plot:

from pyspatialite import dbapi2 as sqlite 
import numpy as np
import matplotlib.pyplot as plt  
import datetime

MyPath = r"C:\DATA\DBTEST\MyTestDb.sqlite"
conn = sqlite.connect(MyPath,detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
curs = conn.cursor()

sql = r"""SELECT Date as 'date [datetime]', Level_masl FROM MyTable"""
rs = curs.execute(sql)
recs = rs.fetchall()

My_format = [('date_time', datetime.datetime), ('values', float)]
table = np.array(recs, dtype=My_format)
table2=table.view(np.recarray)

fig = plt.figure()
ax = fig.add_subplot(111)
ax.plot(table2.date_time, table2.values, 'o-')
....

But it seems like I do not get the correct data types since matplotlib don't accept my 'date_time'. A print table2 is shown below, I guess the small u indicates that it is unicode and not datetime?

print table2

recarray([(u'2011-04-20 09:42:00', 703.46000000000004), (u'2011-04-20 09:43:00', 705.35000000000002), ... dtype=[('date_time', '|O4'), ('values', '

I am no programmer and absolutely a complete Python and Spatialite beginner so any help would be very much appreciated!

/JK

Answer 1

Basically, you're trying to pass in a string.

Matplotlib has several convenience functions for converting date strings to it's (internal) datetime representation.

Try something more like:

...
from matplotlib.dates import datestr2num
...

...
time = datestr2num(table2.date_time)
ax.plot_date(time, table2.values, 'o-')
...

plot_date is the same as plot, it just automatically formats the x-axis to have time (e.g. hour, day, month, etc) labels.

If you'd rather use plot (or bar, etc) instead, just call ax.plot(time, y, ...) or ax.bar as usual, and then call ax.xaxis_date() afterwards to set up the date labels.

Answer 2

Solved with the best help from Joe Kington, thanks a lot!

Insert to original code in question:

myTimestring = []  
i = 0
for row in table2: 
    myTimestring.append(table2.date_time[i])
    i = i + 1
numtime=datestr2num(myTimestring)
...
ax.plot_date(numtime, table2.values, 'o-')

There are most certainly more elegant solutions than 'for row in...' but this works!