# dailyAlignedBar

## Syntax

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

## Arguments

**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.

## Details

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.

## Examples

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;
```

k60 | price | count |
---|---|---|

2019.11.01T09:30:00 | 5.031685383252463 | 3600 |

2019.11.01T10:30:00 | 5.022667285786399 | 3600 |

2019.11.01T11:30:00 | 4.930270051117987 | 1 |

2019.11.01T13:00:00 | 4.931854071494632 | 3600 |

2019.11.01T14:00:00 | 4.979529541734115 | 3600 |

2019.11.01T15:00:00 | 0.961996954865754 | 1 |

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;
```

k60 | price | count |
---|---|---|

2019.11.01T09:30:00 | 5.031685383252463 | 3600 |

2019.11.01T10:30:00 | 5.022641627015316 | 3601 |

2019.11.01T13:00:00 | 4.931854071494632 | 3600 |

2019.11.01T14:00:00 | 4.978413870368697 | 3601 |

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