weekOfMonth

weekOfMonth(X, [week=0], [weekday=0], [offset], [n=1])

In the calendar month of X, suppose the "week"-th "weekday" is d.

• If X<d: return the week-th "weekday" in the previous calendar month.

• If X>=d: return the week-th "weekday" in the current calendar month.

When both offset and n are specified and n > 1, the function calculates dates based on periods consisting of n months. In this case, offset determines how the multi-month periods are aligned. Specifically, the system first calculates the target date in the month containing offset according to week and weekday, and uses that date as the reference point. The result is then updated every n months, and the function returns the target date corresponding to the period to which X belongs.

X is a scalar/vector of type DATE, DATETIME, TIMESTAMP or NANOTIMESTAMP.

week (optional) is an integer from 0 to 3 indicating the i-th week of a month. The default value is 0.

weekday (optional) is an integer from 0 to 6. 0 means Monday, 1 means Tuesday, ..., and 6 means Sunday. The default value is 0.

offset (optional) is a scalar of the same data type as X. It must be no greater than the minimum value of X. The default value is the minimum value of X.

n (optional) is a positive integer. The default value is 1.

A scalar or vector of the DATE type.

weekOfMonth(2019.11.01,2,4);
// output: 2019.10.18

weekOfMonth(2019.11.20,2,4);
// output: 2019.11.15

date=2012.01.02 2012.02.03 2012.03.07 2012.04.08 2012.05.12 2012.06.16 2012.07.18 2012.08.20 2012.09.25 2012.10.28
time = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12,09:38:13]
sym = take(`MSFT,10)
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29 52.38
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800 4500
t1 = table(date, time, sym, qty, price);

select avg(price),sum(qty) from t1 group by weekOfMonth(date,3,4,2012.01.01,2);