CODEWITHSUNDEEP
databasetime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 3988

QuestDB wrapping results every 1000 rows?

I have this very simple query to generate a compact list of 2100 days (a timestamp every 8400 seconds) starting from January 2021.

SELECT x, timestamp_sequence(
            to_timestamp('2021-01-02T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
            86400000000L)
FROM long_sequence(2100);

I execute this on the web console, and to my surprise it all goes well until I reach the row 1001, when the date resets to 2021-01-02T00:00:00.000000Z. And this happens again at row 2001!

Just to be extra sure I tried on three different versions and they all behave the same. Screenshot of the results with latest QuestDB (8.2.2) below:

results with the x column starting at 983 and ending at 1006. Timestamp starts at 2023-09-11 and increments daily, but on row 1001 we can see it is again 2021-01-02

Upvotes: 0

Views: 5

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 3988

timestamp_sequence() is a stateless pseudo-function. It is used for generating persistent test data sets, but it is not mean to be used for real-time data generation. The value of the function changes on every value pull.

It can be seen more clearly with this query

with times as (
SELECT x, timestamp_sequence(
            to_timestamp('2021-01-02T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
            86400000000L) ts
FROM long_sequence(3) 
) select x, ts, ts as ts2 from times;

Notice how the days, which we would expect to be equal for each row, are increased across rows and columns.

"x","ts","ts2"
1,"2021-01-02T00:00:00.000000Z","2021-01-03T00:00:00.000000Z"
2,"2021-01-04T00:00:00.000000Z","2021-01-05T00:00:00.000000Z"
3,"2021-01-06T00:00:00.000000Z","2021-01-07T00:00:00.000000Z"

In your case you see the results wrapping every 1000 rows because the web console paginates results in 1000 rows increments, so the next time it is just calling the same query with different LIMIT values.

If you are running this query from an external client with no pagination, or just using the REST API or downloading the CSV results from the web console, you should see the values you expect.

What I do sometimes when I need to generate synthetic data is to execute an INSERT INTO ... SELECT FROM using the row generator functions, so I materialise results on a table without any of the issues.

Upvotes: 0

Related Questions