KDB Select from partitioned table where date is less than a given date - 1 day

Question

I would like to select from a partitioned table where the date is the highest date strictly below a given date d.

I can do the following:

d:2019.10.02;
{select from x where date = max date} select from t where date < d

where t is my partitioned table.

The issue with the above query is that it is very slow as it has to first load all the dates strictly older than d, and then taking the max date out of it.

Answer 1

select from table where date=(last .Q.pv where .Q.pv < d)

Answer 2

If you just want to select from the max date in a date partitioned hdb

Lets assume that the max populated date partition less than 2019.08.20 is 2019.08.07

q)d:2019.08.20
q)select from t where date=max date where date<d

This is because the partition type is available as a variable once you load into a DB, (i.e,. date, month, int etc). This will be the .Q.pf variable.

Answer 3

To select all the dates that are earlier than your specified date you can use the select statement below:

select from t where date=max date where date<d

Where t is your partitioned table and d is your specified date.

Answer 4

kdb+ stores a variable in memory which contains all the dates within your db.

select from telemetry where date=desc[date]1

Above where clause will sort this by largest ->smallest

Selecting index 1 will filter the max date out of your query (without first querying the entire dataset).