we are using worker analytics engine to provide some info for our saas customers. Currently we found a problem that the same query will give us different result when we give different time ranges even for same time period. And it could be fully 100% reproduced.
url:
https://api.cloudflare.com/client/v4/accounts/111/analytics_engine/sql
query1: we try to group the count and sum for every minute within the timerange: 1694274600 and 1694274720
select toStartOfInterval(timestamp, INTERVAL '1' MINUTE) AS minute, count() as count, sum(double1) as bytes from 'xxx' where toUnixTimestamp(timestamp)>=1694274600 and toUnixTimestamp(timestamp)<1694274720 GROUP BY minute ORDER BY minute DESC
it will output data like
{
"minute": "2023-09-09 15:51:00",
"count": "154531",
"bytes": 92266946205
},
{
"minute": "2023-09-09 15:50:00",
"count": "152733",
"bytes": 90740043682
}
but when we try to query the data for the whole day and group by minute with query, the only difference with last one is time start and end in the query.
select toStartOfInterval(timestamp, INTERVAL '1' MINUTE) AS minute, count() as count, sum(double1) as bytes from 'xxx' where toUnixTimestamp(timestamp)>=1694188800 and toUnixTimestamp(timestamp)<1694275200 GROUP BY minute ORDER BY minute DESC
for the same time, it would return:
{
"minute": "2023-09-09 15:51:00",
"count": "15373",
"bytes": 9216793111
},
{
"minute": "2023-09-09 15:50:00",
"count": "15223",
"bytes": 9105747280
}
Its strange that the count and bytes are very different, anyone knows why?