Title
#users-public
Hà Hữu

Hà Hữu

11/11/2022, 7:22 AM
Hi QuestDB team as i known for now SAMPLE BY can be used with LIMIT but when i'm using FILL it's still not working Example:
j

javier ramirez

11/11/2022, 8:43 AM
Hi. Two ways to make this work. The first one is adding a time zone and offset, as per the error you are getting
8:43 AM
SELECT
  FIRST(price) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price) AS close,
  sum(amount) AS volume,
  timestamp as timestamp
FROM
  trades
where timestamp in '2022-11'  
SAMPLE BY 1s
FILL(PREV, PREV, PREV, PREV, 0) ALIGN TO CALENDAR TIME ZONE 'Europe/Berlin' WITH OFFSET '00:00'
limit 0,15
8:43 AM
The second is to wrap the query in a subquery
8:43 AM
with sampled AS (
SELECT
  FIRST(price) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price) AS close,
  sum(amount) AS volume,
  timestamp as timestamp
FROM
  trades
where timestamp in '2022-11'  
SAMPLE BY 1s
FILL(PREV, PREV, PREV, PREV, 0) ALIGN TO CALENDAR )
select * from sampled limit 0,15
8:44 AM
However, you are right that this should work and I actually reported an issue on this a while ago that was marked as fixed https://github.com/questdb/questdb/issues/2390. Will try to repoen it
9:22 AM
Just noticed you actually don’t need a time zone, just offset as in
9:23 AM
SELECT
  FIRST(price) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price) AS close,
  sum(amount) AS volume,
  timestamp as timestamp
FROM
  trades
where timestamp in '2022-11'  
SAMPLE BY 1s
FILL(PREV, PREV, PREV, PREV, 0) ALIGN TO CALENDAR WITH OFFSET '00:00'
limit 0,15
Hà Hữu

Hà Hữu

11/11/2022, 9:23 AM
i'll it very soon
9:23 AM
thanks
j

javier ramirez

11/11/2022, 9:23 AM
We will very likely fix it in any case, so in the future offset won’t be needed. But for now with this it should work for you 🙂