# What are the implementation strategies for K-line data filling?

**URL:** <https://community.questdb.com/t/what-are-the-implementation-strategies-for-k-line-data-filling/258>\
**Category:** Community\
**Tags:** sql, question\
**Created:** [December 3, 2024, 2:35pm UTC](https://community.questdb.com/t/what-are-the-implementation-strategies-for-k-line-data-filling/258 "2024-12-03T14:35:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![laulee5135](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/laulee5135/32/157_2.png) [@laulee5135](https://community.questdb.com/u/laulee5135)\
**Post date:** [December 3, 2024, 2:35pm UTC](https://community.questdb.com/t/what-are-the-implementation-strategies-for-k-line-data-filling/258/1 "2024-12-03T14:35:30Z")

</div>

Hello, everyone. I am researching QuestDB for my company, focusing on handling various query scenarios and storing K-line data. I encountered a problem and would like to ask for help on how to implement it:

The problem is: Within a specific time window, if no trades occur, a K-line should still be generated with the open, high, low, and close prices being the last trade price before that time window. For example, my table `new_trades`:

```auto
CREATE TABLE new_trades (
    symbol symbol,
    side symbol,
    mid long,
    tradeId long,
    amount_unit double,
    price DOUBLE,
    amount DOUBLE,
    ts TIMESTAMP
) TIMESTAMP(ts)
PARTITION BY DAY;

```

There are two test data entries：

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/c5bdc62ffb169bf22475e5ce33cce8824a72bd18.png)

The requirement is to be able to query:  
2024-12-03T09:12:00.000000Z  
2024-12-03T09:11:00.000000Z  
2024-12-03T09:10:00.000000Z  
…  
2024-12-03T09:03:00.000000Z

The requirement is to obtain the 10 K-line data, where there were no transactions from 2024-12-03T00:12:46.000000Z to 2024-12-03T09:12:00.000000Z. Therefore, the expected result is that the open, high, low, and close values for the period from 2024-12-03T09:03 to 2024-12-03T09:12 are filled with `price=2068.94`, and the other `amount` values are filled with 0. How can this be achieved?

This is the SQL I tried to write, but I did not get the expected result. Please guide me, and I would greatly appreciate it. 🙏

```auto
SELECT
    first(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,    
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    ts AS kline_time      
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'
    AND ts <= '2024-12-03T09:12:31.612000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;

```

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [December 3, 2024, 10:18pm UTC](https://community.questdb.com/t/what-are-the-implementation-strategies-for-k-line-data-filling/258/2 "2024-12-03T22:18:46Z")

</div>

what results are you getting? On the demo instance ([https://demo.questdb.io](https://demo.questdb.io)) I see results as you described with your query

```sql
SELECT
    first(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,    
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    timestamp AS kline_time      
FROM
    trades
WHERE
    symbol = 'ETH-USDT'
    AND timestamp > dateadd('m', -10, now()) 
SAMPLE BY 1s 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC;

```

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/24d19e39c3359a8feea87f5015cd64fe630c2023.png)

---

<div class="post-metadata">

**Author:** ![laulee5135](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/laulee5135/32/157_2.png) [@laulee5135](https://community.questdb.com/u/laulee5135)\
**Post date:** [December 4, 2024, 3:19am UTC](https://community.questdb.com/t/what-are-the-implementation-strategies-for-k-line-data-filling/258/3 "2024-12-04T03:19:01Z")

</div>

@javier thank you for response

It seems that on the demo instance, all trading pairs have trades every second, so there is no data matching my query scenario, making it difficult to reproduce. Therefore, I created a `new_trades` table myself, which only contains two rows of data (as shown above).

What I want to verify is that within a specific time window, even if there are no trades, K-lines (candlesticks) should still be generated. The open, high, low, and close prices for these K-lines should be filled with the prices from the last trade before the time window. For example, as shown in the `new_trades` table above, the “ETS-USDT” pair only has two trades at `2024-12-03T00:12:46.000000Z` and `2024-12-02T15:12:46.000000Z`.

Now, I want to query for the 10 K-lines between `2024-12-03T09:03:00.000000Z` and `2024-12-03T09:12:00.000000Z`. Since there were no trades during this time, the most recent trade occurred at `2024-12-03T00:12:46.000000Z`. Therefore, the expected result is that the open, high, low, and close prices for the 10 K-lines between `2024-12-03T09:03:00.000000Z` and `2024-12-03T09:12:00.000000Z` should be filled with the data from the trade at `2024-12-03T00:12:46.000000Z`. However, the SQL query I wrote did not return the expected results:

```auto
SELECTfirst(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,     
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    ts AS kline_time    
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'AND ts <= '2024-12-03T09:12:00.000000Z' and ts >= '2024-12-03T09:03:00.000000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;

```

_(Sorry, I’m limited in the number of images I can upload.) The query result is empty._

Alternatively, using another query method also did not return the expected results.

```auto
SELECT
    first(price) AS open,  
    max(price) AS high,     
    min(price) AS low,        
    last(price) AS close,      
    sum(amount) AS total_volume,
    count(amount) AS total_count,
    ts AS kline_time    
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'
    AND ts <= '2024-12-03T09:12:00.000000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;

```

 ![](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/fb448d09cdaf6d26fa4eba0908df48b4d65193f7.png)

The query results (which are in reverse order) start from 2024-12-03T **00:12** :00.000000Z, but they should start from 2024-12-03T **09:12** :00.000000Z. The complete query results should display data from 2024- **12-03T09:12** :00.000000Z to 2024- **12-03T09:03** :00.000000Z.