Search code examples
pythondatetimepandasepoch

HDFStore with index in epoch time

I have pandas dataframe in HDFStore with index stored as epoch time. I want to read the data and query based on specific index.

For example - if i have index in datetime64[ns] instead of epoc, i can obtain the result as:

starttime = datetime.datetime(2008,12,22,00,19,55,150000)

start = pd.Timestamp(stoptime) + pd.Timedelta(1)

stoptime = datetime.datetime(2008,12,22,00,55,55,180000)

stop = pd.Timestamp(starttime) + pd.Timedelta(1)

pd.read_hdf('file.h5',columns=['Data','Qty'],where='index > start & index < stop']

How can i achieve the same result if index in HDFStore is stored as epoch time ?

Solution

  • IIUC, you should convert your start and stop times to epoch to be able to perform the query. To do this you can:

    start_epoch = (start - datetime.datetime(1970,1,1)).total_seconds()
stop_epoch = (stop - datetime.datetime(1970,1,1)).total_seconds()

    Then you should be able to perform the query with them. Using your data:

    In [24]: (start - datetime.datetime(1970,1,1)).total_seconds()
Out[24]: 1229905195.15

In [27]: (stop - datetime.datetime(1970,1,1)).total_seconds()
Out[27]: 1229907355.18

    EDIT: more general issues. In case of a numpy datetime64 object you can first convert it to plain datetime:

    In [16]: abc = np.datetime64('2005-12-27 20:10:10.500400300', 'ns')

In [17]: a = pd.to_datetime(abc)

In [18]: a
Out[18]: Timestamp('2005-12-27 20:10:10.500400300')

    Then you can use the above method.