title: Gap Filling OHLCV (Open High Low Close Volume) in TimescaleDB
body: <p>I have some OHLCV data stored in TimescaleDB which contains missing data during certain time ranges. This data needs to be resampled to a different time period (i.e. 1 day) and contain contiguous, ordered time buckets. </p>
  
  <p>TimescaleDB provides the function <code>time_bucket_gapfill</code> to do this. My current query is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      time_bucket_gapfill(
          '1 day', 
          "timestamp",
          '2017-07-25 00:00', 
          '2018-01-01 00:00'
      ) as date,
      FIRST(open, "timestamp") as open,
      MAX(high) as high,
      MIN(low) as low,
      LAST(close, "timestamp") as close,
      SUM(volume) as volume
  FROM ohlcv
  WHERE "timestamp" &gt; '2017-07-25'
  GROUP BY date ORDER BY date ASC LIMIT 10
  </code></pre>
  
  <p>which gives as the results</p>
  
  <pre><code>date                    open        high        low         close       volume
  2017-07-25 00:00:00+00                  
  2017-07-26 00:00:00+00                  
  2017-07-27 00:00:00+00  0.00992     0.010184    0.009679    0.010039    65553.5299999999
  2017-07-28 00:00:00+00  0.00999     0.010059    0.009225    0.009248    43049.93
  2017-07-29 00:00:00+00  
  2017-07-30 00:00:00+00  0.009518    0.0098      0.009286    0.009457    40510.0599999999
  
  ...
  </code></pre>
  
  <p><strong>Question:</strong> It looks like only the <code>date</code> column has been gapfilled. By modifying the SQL statement, is it possible to also gap-fill the columns <code>open</code>, <code>high</code>, <code>low</code>, <code>close</code> and <code>volume</code> such that we obtain the results:</p>
  
  <pre><code>date                    open        high        low         close       volume
  2017-07-25 00:00:00+00  0           0           0           0           0               
  2017-07-26 00:00:00+00  0           0           0           0           0               
  2017-07-27 00:00:00+00  0.00992     0.010184    0.009679    0.010039    65553.5299999999
  2017-07-28 00:00:00+00  0.00999     0.010059    0.009225    0.009248    43049.93
  2017-07-29 00:00:00+00  0.009248    0.009248    0.009248    0.009248    0   
  2017-07-30 00:00:00+00  0.009518    0.0098      0.009286    0.009457    40510.0599999999
  
  ...
  </code></pre>
  
  <p>Or is it recommended to perform this data inputation after receiving the query result, such as in Python/Nodejs?</p>
  
  <hr>
  
  <h2>Example of how it can be done using Python/pandas</h2>
  
  <p><em>Prefer to perform this gapfilling/inputation using TimescaleDB instead of using my Nodejs app because... using Nodejs to do this will be much slower and I do not want to introduce Python into the app just to do this processing</em></p>
  
  <pre class="lang-py prettyprint-override"><code>import pandas as pd
  
  # Building the test dataset simulating missing values after time_bucket
  data = [
      (pd.Timestamp('2020-01-01'), None, None, None, None, None),
      (pd.Timestamp('2020-01-02'), 100, 110, 90, 95, 3),
      (pd.Timestamp('2020-01-03'), None, None, None, None, None),
      (pd.Timestamp('2020-01-04'), 98, 150, 100, 100, 4),
  ]
  df = pd.DataFrame(data, columns=['date', 'open' , 'high', 'low', 'close', 'volume']).set_index('date')
  
  #              open   high    low  close  volume
  # date                                          
  # 2020-01-01    NaN    NaN    NaN    NaN     NaN
  # 2020-01-02  100.0  110.0   90.0   95.0     3.0
  # 2020-01-03    NaN    NaN    NaN    NaN     NaN
  # 2020-01-04   98.0  150.0  100.0  100.0     4.0
  
  
  # Perform gap filling
  df.close = df.close.fillna(method='ffill')
  df.volume = df.volume.fillna(0)                 # fill missing volume with 0
  df['open'] = df['open'].fillna(df['close'])     # fill missing open by forward-filling close
  df['high'] = df['high'].fillna(df['close'])     # fill missing high by forward-filling close
  df['low'] = df['low'].fillna(df['close'])       # fill missing low by forward-filling close
  df = df.fillna(0)                               # fill missing OHLC with 0 if no previous values available
  
  #               open   high    low  close  volume
  # date                                          
  # 2020-01-01    0.0    0.0    0.0    0.0     0.0
  # 2020-01-02  100.0  110.0   90.0   95.0     3.0
  # 2020-01-03   95.0   95.0   95.0   95.0     0.0
  # 2020-01-04   98.0  150.0  100.0  100.0     4.0
  </code></pre>