dailyAlignedBar

dailyAlignedBar(X, timeOffset, n, [timeEnd], [mergeSessionEnd=false])

X is a temporal vector of type SECOND, TIME, NANOTIME, DATETIME, TIMESTAMP or NANOTIMESTAMP.

timeOffset is a scalar/vector of type SECOND, TIME or NANOTIME with the same accuracy of X indicating the left boundary of session(s). If it is a vector, it must be increasing.

n is a positive integer or DURATION type data indicating the window length. If n is a positive integer, its unit is the minimum accuracy of timeOffset. If n is a DURATION type data, its unit cannot be y, M, w, d, B.

timeEnd (optional) is of the same type and length of timeOffset indicating the right boundary of session(s).

mergeSessionEnd (optional) is a Boolean value. When the right boundary of a session (as specified in timeEnd) is also the right boundary of a window, if mergeSessionEnd=true, the right boundary of the session is merged into the previous window.

Determine windows based on the starting time (specified by timeOffset), window length (specified by n), and possibly ending time (specified by timeEnd). For each element of X, return the starting time of the window it belongs to. Specifically, return X-((X-timeOffset)%n) for each element of X and return a vector with the same length as X.

Generally, a window includes the left boundary but not the right boundary. If mergeSessionEnd=true and the right boundary of a session is also the right boundary of a window, the right boundary of the session is merged into the previous window.

The function supports overnight sessions.

Please note that the examples below use randomly generated data for the column of price. The result is different each time you execute it.

Example 1. The Chinese stock market has 2 sessions each day: from 9:30AM to 11:30AM and from 1PM to 3PM. The script below calculates rolling 1-hour average prices for these sessions.

sessionsBegin = 09:30:00 13:00:00
ts = 2019.11.01T09:30:00..2019.11.01T11:30:00 join 2019.11.01T13:00:00..2019.11.01T15:00:00
t = table(ts, rand(10.0, size(ts)) as price);

select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessionsBegin, 60*60) as k60;

As a window includes the left boundary but not the right boundary, if the right boundary of a session is also the right boundary of a window as in the example above, the right boundary of the session belongs to a window that has no other records if timeEnd and mergeSessionEnd are not specified. In most cases we would like to merge the right boundary of a session to the previous window. Please refer to the example below.

sessionsEnd = 11:30:00 15:00:00;
select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessionsBegin, 60*60, sessionsEnd, true) as k60;

Example 2. The futures market has 2 sessions each day: from 1:30PM to 4:30PM and from 10:30PM to 2:30AM the next day. dailyAlignedBar is used to calculate 7-minute average prices for these sessions. Please note that we simulate 2 days' data in the example.

sessions = 13:30:00 22:30:00
ts = 2019.11.01T13:30:00..2019.11.01T16:30:00 join 2019.11.01T22:30:00..2019.11.02T02:30:00
ts = ts join (ts+60*60*24)
t = table(ts, rand(10.0, size(ts)) as price)
select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessions, 7m) as k7;

Ex 3. When calculating 1-minute OHLC bars, the data type needs to be converted to LONG if n needs to be converted to NANOTIMESTAMP, otherwise an integer overflow will occur.

n = 1000000
nano=(09:30:00.000000000 + rand(long(6.5*60*60*1000000000), n)).sort!()
sessionStartNano=09:30:00.000000000
price = 100+cumsum(rand(0.02, n)-0.01)
volume = rand(1000, n)
symbol = rand(`600519`000001`600000`601766, n)
tradeNano=table(symbol, nano, price, volume).sortBy!(`symbol`nano)
undef(`nano`price`volume`symbol)
barMinutes=7
itv = barMinutes*60*long(1000000000)

OHLC_nano=select first(price) as open, max(price) as high, min(price) as low, last(price) as close, sum(volume) as volume from tradeNano group by symbol, dailyAlignedBar(nano, sessionStartNano, itv) as barStart

Related function: bar