CODEWITHSUNDEEP
pythonpandasdatetimekdb+
Matthew
Matthew

Reputation: 77

How to convert date and time from kdb server query response using qPython and pandas?

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.

Upvotes: 0

Views: 1137

Answers (1)

Darren McConnell
Darren McConnell

Reputation: 46

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)

Upvotes: 3

Related Questions