weekEnd
Syntax
weekEnd(X, [weekday=6], [offset], [n=1])
alias: week
Arguments
X is a scalar/vector of type DATE, DATETIME, DATEHOUR, TIMESTAMP or NANOTIMESTAMP.
weekday is an integer from 0 to 6. 0 means Monday, 1 means Tuesday, …, and 6 means Sunday. The default value is 6.
offset 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 is a positive integer. The default value is 1.
Details
For each element of X, return the last date of the week that it belongs to and that ends on the day as specified by parameter weekday.
If parameter weekday>weekday(X, false): for each element of X, return a date that corresponds to the specified “weekday” parameter in the same calendar week.
If parameter weekday<=weekday(X, false): for each element of X, return a date that corresponds to the specified “weekday” parameter in the next calendar week.
If parameter offset is specified, the result is updated every n weeks. Parameter offset works only if parameter n>1.
Examples
Example 1
$ t = table(2017.12.01..2017.12.14 as date)
$ update t set weekday=weekday(date, false), weekEnd=weekEnd(date), weekEnd4=weekEnd(date,4)
$ t;
date |
weekday |
weekEnd |
weekEnd4 |
|---|---|---|---|
2017.12.01 |
4 |
2017.12.04 |
2017.12.01 |
2017.12.02 |
5 |
2017.12.04 |
2017.12.08 |
2017.12.03 |
6 |
2017.12.04 |
2017.12.08 |
2017.12.04 |
0 |
2017.12.04 |
2017.12.08 |
2017.12.05 |
1 |
2017.12.11 |
2017.12.08 |
2017.12.06 |
2 |
2017.12.11 |
2017.12.08 |
2017.12.07 |
3 |
2017.12.11 |
2017.12.08 |
2017.12.08 |
4 |
2017.12.11 |
2017.12.08 |
2017.12.09 |
5 |
2017.12.11 |
2017.12.15 |
2017.12.10 |
6 |
2017.12.11 |
2017.12.15 |
2017.12.11 |
0 |
2017.12.11 |
2017.12.15 |
2017.12.12 |
1 |
2017.12.18 |
2017.12.15 |
2017.12.13 |
2 |
2017.12.18 |
2017.12.15 |
2017.12.14 |
3 |
2017.12.18 |
2017.12.15 |
Example 2
$ t = table(2018.01.03+0..10*3 as date, 0..10 as x)
$ update t set weekday=weekday(date, false), weekEnd2=weekEnd(date,,2018.01.02,2)
$ t;
date |
x |
weekday |
weekEnd2 |
|---|---|---|---|
2018.01.03 |
0 |
2 |
2018.01.08 |
2018.01.06 |
1 |
5 |
2018.01.08 |
2018.01.09 |
2 |
1 |
2018.01.22 |
2018.01.12 |
3 |
4 |
2018.01.22 |
2018.01.15 |
4 |
0 |
2018.01.22 |
2018.01.18 |
5 |
3 |
2018.01.22 |
2018.01.21 |
6 |
6 |
2018.01.22 |
2018.01.24 |
7 |
2 |
2018.02.05 |
2018.01.27 |
8 |
5 |
2018.02.05 |
2018.01.30 |
9 |
1 |
2018.02.05 |
2018.02.02 |
10 |
4 |
2018.02.05 |
Example 3
$ date=2012.10.02 2012.10.03 2012.10.07 2012.10.08 2012.10.12 2012.10.16 2012.10.18 2012.10.20 2012.10.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);
$ t1;
date |
time |
sym |
qty |
price |
|---|---|---|---|---|
2012.10.02 |
09:34:07 |
MSFT |
2200 |
49.6 |
2012.10.03 |
09:36:42 |
MSFT |
1900 |
29.46 |
2012.10.07 |
09:36:51 |
MSFT |
2100 |
29.52 |
2012.10.08 |
09:36:59 |
MSFT |
3200 |
30.02 |
2012.10.12 |
09:32:47 |
MSFT |
6800 |
174.97 |
2012.10.16 |
09:35:26 |
MSFT |
5400 |
175.23 |
2012.10.18 |
09:34:16 |
MSFT |
1300 |
50.76 |
2012.10.20 |
09:34:26 |
MSFT |
2500 |
50.32 |
2012.10.25 |
09:38:12 |
MSFT |
8800 |
51.29 |
2012.10.28 |
09:38:13 |
MSFT |
4500 |
52.38 |
select avg(price),sum(qty) from t1 group by weekEnd(date, 4, 2012.10.01, 2);
weekEnd_date |
avg_price |
sum_qty |
|---|---|---|
2012.10.05 |
39.53 |
4100 |
2012.10.19 |
92.1 |
18800 |
2012.11.02 |
51.33 |
15800 |
Related function: weekBegin