I have two tables with identical schema

CREATE TABLE 'btc_trades3' ( 
    symbol INT,
    side SYMBOL CAPACITY 256 CACHE,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(symbol,timestamp);

I want to do a SAMPLE BY of the data for both tables, so I am doing a UNION and a SAMPLE BY on top

SELECT timestamp, symbol, side, avg(price) FROM
(
select * from btc_trades3
UNION 
select * from btc_trades333
) SAMPLE BY 15m;

But then I get this base query does not provide ASC order over dedicated TIMESTAMP column. All good, I read in the docs I have to use timestamp column to specify the designated timestamp column, and make sure I am using ORDER BY timestamp ASC so I am going with this:

SELECT timestamp(timestamp), symbol, side, avg(price) FROM
(
select * from btc_trades3
UNION 
select * from btc_trades333
) 
SAMPLE BY 15m
ORDER BY timestamp ASC;

And now I get unknown function name: timestamp(TIMESTAMP)

I have two tables with identical schema

CREATE TABLE 'btc_trades3' ( 
    symbol INT,
    side SYMBOL CAPACITY 256 CACHE,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(symbol,timestamp);

I want to do a SAMPLE BY of the data for both tables, so I am doing a UNION and a SAMPLE BY on top

SELECT timestamp, symbol, side, avg(price) FROM
(
select * from btc_trades3
UNION 
select * from btc_trades333
) SAMPLE BY 15m;

But then I get this base query does not provide ASC order over dedicated TIMESTAMP column. All good, I read in the docs I have to use timestamp column to specify the designated timestamp column, and make sure I am using ORDER BY timestamp ASC so I am going with this:

SELECT timestamp(timestamp), symbol, side, avg(price) FROM
(
select * from btc_trades3
UNION 
select * from btc_trades333
) 
SAMPLE BY 15m
ORDER BY timestamp ASC;

And now I get unknown function name: timestamp(TIMESTAMP)

• database
• time-series
• questdb

1 Answer 1

First of all, the reason why SAMPLE BY complains at first is because, even if both tables have a designated timestamp, after the UNION it is not guaranteed that data is still in order, which means results would be wrong, as SAMPLE BY expects data to be sorted by ascending designated timestamp.

The timestamp function needs to be applied either to the whole query, but it is not used as in your example. To make it simple, if you have a query like this SELECT ts1, ts2 FROM tb where the designated timestamp is ts1 but you want to use ts2 as designated timestamp in a query, you would need to do:

SELECT * FROM ( (SELECT ts1, ts2 FROM tb ORDER BY ts2 ASC) timestamp(ts2) )

This uses ORDER BY to make sure the data is sorted by the ts2 column, and then tells the outer query that this query will use ts2 as the designated timestamp. Now if you do any SAMPLE BY or ASOF JOIN, the reference column will be ts2.

So, if we translate this to the UNION query, this is what we get

SELECT timestamp, symbol, side, avg(price) FROM
( ( (  
select * from btc_trades3
UNION 
select * from btc_trades333
)
ORDER BY timestamp ASC
) timestamp(timestamp) )
SAMPLE BY 15m
;

We first do the UNION, then we force ORDER BY the timestamp column, and then we hint the SQL engine to use the column named timestamp as the timestamp. With this, the SAMPLE BY will already work.