Weekdays / weekend filter on a table

Question

Let's say I have a 'trade' table that lists all trades taken on a given exchange with these columns:

• date
• trading pair (e.g. BTCUSDT)
• ts, rt (timestamp and receiving time)
• price
• trade size
• buy/sell

Now I want to be able to to look at weekends and weekdays separately.

//Exclude weekends:

firstdate:2023.01.01

lastdate:2023.01.10

daterange:firstdate + til (lastdate - firstdate) + 1

daterange where 1<daterange mod 7

// or: daterange where not (daterange mod 7) in 0 1

(taken from this: How to generate a date range in kdb excluding weekend days?)

So the following code runs fine:

t1hr: select avg tv by 1 xbar ts.hh from trade where date within (firstdate;lastdate), sym=`BTCUSDT

whenever I add the daterange variable, I get errors:

t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1<daterange mod 7, sym=`BTCUSDT

Error reads:

length [6] (.Q.ps)

[5] t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1<daterange mod 7, sym=BTCUSDT ^ [4] {( (1b;`) ;value x)}

Answer 1

By the looks of it you have a second where statement that shouldn't be in there.

On filtering by weekday, the best way to do it is something like:

q)/- Week days
q)5#select from trade where not (date mod 7)in 0 1
date       pair price size side
-------------------------------
2007.01.19 ABC  86    0    S   
2009.09.17 DEF  25    5    S   
2016.12.27 ABC  6     11   B   
2021.03.16 GHI  81    28   S   
2001.01.22 DEF  19    9    S   
q)/- Week ends
q)5#select from trade where (date mod 7)in 0 1
date       pair price size side
-------------------------------
2003.12.07 GHI  41    71   B   
2019.07.14 GHI  37    15   S   
2006.04.01 ABC  57    94   S   
2005.09.11 DEF  99    95   S   
2003.02.23 DEF  26    69   B 

If its something that you are querying often its probably best to create a "isweekday" column like:

q)update isweekday:(date mod 7)>1 from `trade
`trade
q)5#select from trade where isweekday
date       pair price size side isweekday
-----------------------------------------
2007.01.19 ABC  86    0    S    1        
2009.09.17 DEF  25    5    S    1        
2016.12.27 ABC  6     11   B    1        
2021.03.16 GHI  81    28   S    1        
2001.01.22 DEF  19    9    S    1 

q)5#select from trade where date within (2020.01.01;2023.01.01),isweekday
date       pair price size side isweekday
-----------------------------------------
2021.03.16 GHI  81    28   S    1        
2021.05.18 DEF  66    65   B    1        
2021.07.09 ABC  9     83   S    1        
2021.02.11 ABC  23    41   S    1        
2022.01.07 DEF  87    45   B    1