1

i need to select and get rows which are related to previous 1 and 24 hours. to handle 24 hour i used this code. what about 1 hour how can i get that?

yesterday = datetime.now() - timedelta(1)
yesterday = datetime.strftime(yesterday, '%Y-%m-%d')

q.sendSync('{[x;z]select from trades where DateTime > x,symbol=z}', np.datetime64(yesterday,'D'),  np.string_('ETH-USDT'))

kdb saved dataframe

                     DateTime            symbol  
0     2022-04-13 12:59:00.171     b'ETH-USDT' 
1     2022-04-13 12:30:00.171     b'ETH-USDT'   
2     2022-04-13 10:55:00.171     b'ETH-USDT'       
3     2022-04-12 10:59:00.171     b'ETH-USDT'  
4     2022-04-10 10:53:00.185     b'ETH-USDT'  
5     2022-04-09 10:50:01.114     b'ETH-USDT'
devmrh
  • 1,171
  • 4
  • 19
  • 46

1 Answers1

5

instead of yesterday you can use this as the time for the query - here we are just subtracting an hour from the current time and formatting it as a date and hour + minute rather than just a day

hourago = datetime.strftime(datetime.now() - timedelta(hours = 1), '%Y-%m-%d %H:%M')

q.sendSync('{[x;z]select from trades where DateTime > x,symbol=z}', np.datetime64(hourago,'ms'),  np.string_('ETH-USDT'))

Its also worth noting you can do this purely in kdb without the need for the python date/time manipulation

q.sendSync('{select from trades where DateTime > .z.p-0D01,symbol=x}', np.string_('ETH-USDT'))

.z.p gives the current timestamp and -0D01 subtracts an hour from this time.

MurrMack
  • 579
  • 3
  • 15
  • if i use %Y-%m-%d %H:%M i whould get this error any idea why? `/python3.9/site-packages/qpython/qwriter.py", line 214, in _write_numpy_temporal raise QWriterException('Unable to serialize type: %s' % data.dtype) qpython.qwriter.QWriterException: Unable to serialize type: datetime64[m]` – devmrh Apr 13 '22 at 17:31
  • for date format (%Y-%m-%d) it is ok but for hours i get error – devmrh Apr 13 '22 at 17:32
  • ok sorry you have to format as second or miliseconds for qpython to parse the time correctly. Change np.datetime64(hourago) to np.datetime64(hourago,'ms') – MurrMack Apr 13 '22 at 17:47
  • second way that using .z.p-0D01 works nicely for me, but first solution return 0 record, why? – devmrh Apr 13 '22 at 17:57
  • check what timezones you are using in each instance. Run hourago = datetime.strftime(datetime.now() - timedelta(hours = 1), '%Y-%m-%d %H:%M') and then np.datetime64(hourago,'ms') and view the output. Then in kdb instance run .z.p-0D01 and see what the output is. I suspect one is using UTC time and the other is using local time? – MurrMack Apr 13 '22 at 17:59
  • can i use .z.p-0D01 for 30min and 1 day too? how can i change `0D01` for other times? – devmrh Apr 13 '22 at 18:00
  • please see the kx documentation here https://code.kx.com/q4m3/2_Basic_Data_Types_Atoms/#252-time-types for 30 mins you can use .z.p-0D00:30 for 1 day you can use .z.d -1 The .z.d returns the date and -1 will subtract a day from it. – MurrMack Apr 13 '22 at 18:07
  • yes you are right, in kdb .z.p-0D01 will return utc, but in python `hourago` give me local time.. to resolve this, should i convert the python time to utc? – devmrh Apr 13 '22 at 18:10
  • Yes if you would like to fix it to utc time use datetime.utcnow() rather than datetime.now() – MurrMack Apr 13 '22 at 19:06