InfluxDB supports powerful query pagination using offset and limit. Pagination is a common use case in web applications when dealing with large data. Google shows you 10 search results at a time, Amazon may show 20 results per page, and software logs tracking UI may show 20-100 items on the UI. By getting a total count, we can easily get a total page number using the following formula:
Total page = (total result count / page size)
If the page size is n, then the initial page displays records starting from 0-n-1. When you click on Next or scroll to next (using the infinity scroll bar), the next page will display records from n-2n-1, and so on. In InfluxDB, we can use the limit to define how many data points we want to fetch per query, offset moves data point records from 0, to n, to 2n until the end of records.
The following is the syntax:
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]
Consider the upcoming examples.
Assume we will query the ticker information. The UI page will display 10 records at a time. The following queries show how to achieve this goal:
> select count(*) from tickers;
name: tickers
time count_close count_company count_high count_low count_open count_volume
---- ----------- ------------- ---------- --------- ---------- ------------
1970-01-01T00:00:00Z 3507 3507 3507 3507 3507 3507
> SELECT ticker,company, volume, close FROM tickers LIMIT 10 OFFSET 0
name: tickers
time ticker company volume close
---- ------ ------- ------ -----
2017-11-27T05:00:00Z AAPL APPLE INC 34908 174.05
2017-11-27T05:00:00Z JPM JPMorgan Chase & Co 7904 98.42
2017-11-27T05:00:00Z GOOGL Alphabet Inc 100 1065.06
2017-11-27T05:01:00Z JPM JPMorgan Chase & Co 20155 98.365
2017-11-27T05:01:00Z GOOGL Alphabet Inc 1500 1064.93
2017-11-27T05:01:00Z AAPL APPLE INC 27891 174.0652
2017-11-27T05:02:00Z GOOGL Alphabet Inc 1700 1065.12
2017-11-27T05:02:00Z AAPL APPLE INC 20390 174.0957
2017-11-27T05:02:00Z JPM JPMorgan Chase & Co 42901 98.37
2017-11-27T05:03:00Z AAPL APPLE INC 21059 174.05
> SELECT ticker,company, volume, close FROM tickers LIMIT 10 OFFSET 10
name: tickers
time ticker company volume close
---- ------ ------- ------ -----
2017-11-27T05:03:00Z JPM JPMorgan Chase & Co 11095 98.31
2017-11-27T05:03:00Z GOOGL Alphabet Inc 2000 1065.24
2017-11-27T05:04:00Z JPM JPMorgan Chase & Co 14955 98.3
2017-11-27T05:04:00Z GOOGL Alphabet Inc 1600 1063.67
2017-11-27T05:04:00Z AAPL APPLE INC 23365 174.0215
2017-11-27T05:05:00Z GOOGL Alphabet Inc 1302 1063.93
2017-11-27T05:05:00Z AAPL APPLE INC 34056 174.03
2017-11-27T05:05:00Z JPM JPMorgan Chase & Co 14515 98.32
2017-11-27T05:06:00Z AAPL APPLE INC 28192 174.01
2017-11-27T05:06:00Z JPM JPMorgan Chase & Co 24609 98.2501
> SELECT ticker,company, volume, close FROM tickers LIMIT 10 OFFSET 20
name: tickers
time ticker company volume close
---- ------ ------- ------ -----
2017-11-27T05:06:00Z GOOGL Alphabet Inc 2900 1064.06
2017-11-27T05:07:00Z JPM JPMorgan Chase & Co 16332 98.28
2017-11-27T05:07:00Z GOOGL Alphabet Inc 662 1064.93
2017-11-27T05:07:00Z AAPL APPLE INC 11298 174.04
2017-11-27T05:08:00Z GOOGL Alphabet Inc 720 1064.91
2017-11-27T05:08:00Z AAPL APPLE INC 12963 174.0236
2017-11-27T05:08:00Z JPM JPMorgan Chase & Co 12674 98.255
2017-11-27T05:09:00Z AAPL APPLE INC 25547 173.9851
2017-11-27T05:09:00Z JPM JPMorgan Chase & Co 7442 98.2301
2017-11-27T05:10:00Z AAPL APPLE INC 17162 173.99