timeseries database to use with python

Question

I have an application written in python, which stores some values currently in a text file in the format of "datetime value". This worked fine so far.

The problem is now that I need to start retrieving the data by time intervals. I have converted the files into an sqlite database. However I find that the performance is really poor. I ran queries like:

select min(value) from data where dt > '2013-05-13 15:48:13' and dt < '2013-05-13 15:49:13'

So my lowest time interval is 1m.

But this seems to take abou 0.036s which is really slow when I need to produce graphs for even small time intervals.

What other approach would you suggest to use for this problem.

Answer 1

If you don't have tooooo much data, then load everything into memory into a pandas TimeSeries.

import pandas as pd
ts = pd.TimeSeries(range(86400), index=pd.DatetimeIndex(start='2013-05-14 00:00:00', freq='1S', periods=86400))

creates a TimeSeries with 86400 values for each second of today's day.

The following line needs 2.72ms and returns the value you awaited:

ts.between_time('2013-05-14 15:48:13', '2013-05-14 15:49:13').min()

You can have a different frequency and not equally spaced values as well:

> pd.TimeSeries([1,2,3], index=pd.DatetimeIndex([datetime(2013,5,14,0,0,0,100000), datetime(2013,5,14,0,0,0,200000), datetime(2013,5,14,0,0,0,900000)]))

2013-05-14 00:00:00.100000    1
2013-05-14 00:00:00.200000    2
2013-05-14 00:00:00.900000    3