Search code examples
databasekdbq-lang

How to select first record prior/after a given timestamp in KDB?

I am currently just pulling in all records 1min leading up to the timestamp (e.g. if the timestamp I'm interested in is 2014.04.14T09:30):

select from Prices where timestamp within 2014.04.14T09:29 2014.04.14T09:30, stock=`GOOG

However, this is clearly not very robust. Sometimes the previous record may be at 09:25am and then the query returns nothing. Sometimes the query may return hundreds of records if there have been a lot of price changes, even though all I need is the last record returned.

I know this can be done with an asof join, but want to avoid it for the time being as Prices is simply too big at present.

I am also interested in doing the same, but in finding the first record after a given timestamp.

Note also that Prices is a splayed table

Solution

  • Select last record before the given timestamp:

    q)select from Price where stock=`GOOG,i=last i,timestamp<2014.04.14T09:30

    Select first record after the given timestamp:

    q)select from Price where stock=`GOOG,i=first i,timestamp>2014.04.14T09:30