I have issue converting date and times in the kdb server reply.
I have searched in Internet and here, in StackOverflow, but I could not find a suitable solution. Therefore I need help.
from qpython import qconnection
import pandas as pd
from datetime import datetime
query = 'select from trade where date = 2007.02.28, sym = `XXXX'
q = qconnection.QConnection(host=server, port=server_port, username=user, password=server_password, timeout=server_timeout)
q.open()
df = pd.DataFrame(q.sendSync(query))
print(df.to_string())
q.close()
The reply is (only header row and the first line of it):
date time ex sym cond size price stop corr seq src trpt participantTime1 rrn participantTime2 id exempt
0 2615 15517877000000 b'P' b'XXXX' b'T ' 500 27.670000 b' ' 0 1095 b'N' b' ' -9223372036854775808 b'' -9223372036854775808 b'' -32768
How to convert date and time? Right now I have 2615 for date, which has to be 2007.02.28. The same goes with the time. It is 15517877000000, while it should be 0D04:18:37.877000000. (Most probably 0D in time comes from the encoding - a problem which I shall solve later, if needed.). The same goes for participantTime1 and participantTime2
Thank you in advance for anyone's time.
Pandas integration and automatic temporal-type casting is supported by the qPython package, which would simplify your issue. You can find the relevant documentation here.
The only change you would need to make from your code snippet is to set the pandas flag to True when instantiating the QConnection object:
q = qconnection.QConnection(host=server, port=server_port, username=user,
password=server_password, timeout=server_timeout, pandas=True)
In this case, you also wouldn't need to convert the return value from sendSync as q tables will now be represented as pandas dataframes by default:
df = q.sendSync(query)