Title
t
theseusyang
03/09/2023, 11:48 AMwhich function is a replaced INTERVAL function in questdb?
i
Imre
03/09/2023, 1:22 PMhi @theseusyang, how would you use INTERVAL() if it was present in the database?
QuestDB uses time ranges instead.
https://questdb.io/docs/reference/sql/where/#time-range
https://questdb.io/docs/reference/sql/where/#time-range-with-modifier
is it possible to formulate your query using time ranges?
t
theseusyang
03/09/2023, 1:38 PMI already try this IN function, but it is invalid, can't query any data back.
insert into t12 values(500000133,7987090,987091,1,40.00,47068.00,0.05,0.04,'A','F',to_date('1994-11-28','yyyy-MM-dd') , to_date('1994-11-24','yyyy-MM-dd') ,to_date('1994-12-11','yyyy-MM-dd') ,'DELIVER IN PERSON', 'TRUCK', 's haggle above the ironic courts. blithe');
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
t12
where l_shipdate IN ('1994-11-29', '1994-11-20');
image.png
i
Imre
03/09/2023, 1:50 PMtested it quickly:
which version of QuestDB are you running?
seems to work with aggregation too:
t
theseusyang
03/09/2023, 1:53 PMI find a problem, if you write IN('1994-11-19','1994-11-30') , can't query back anything,
7.0.1
i
Imre
03/09/2023, 1:55 PMthis
'1994-11.19'to_timestamp()it would say something like
invalid datet
theseusyang
03/09/2023, 1:57 PMIN('1994-11-19','1994-11-30')
image.png
image.png
image.png
i
Imre
03/09/2023, 2:00 PMtried the same, without spaces.
still works for me:
not sure what is the difference between yours and mine?
what is the schema of the table?
actually your screenshot show data for 1994-11-28, and your query is for 1994-11-29.
probably a stupid question but are you sure there is data for 1994-11-29?
t
theseusyang
03/09/2023, 2:27 PMthis is my schema:
CREATE TABLE 't12' (
l_orderkey LONG,
l_partkey INT,
l_suppkey INT,
l_linenumber INT,
l_quantity DOUBLE,
l_extendedprice DOUBLE,
l_discount DOUBLE,
l_tax DOUBLE,
l_returnflag CHAR,
l_linestatus CHAR,
l_shipdate DATE,
l_commitdate DATE,
l_receiptdate DATE,
l_shipinstruct STRING,
l_shipmode STRING,
l_comment STRING
);
as a time-range query for 1994-11-28, or for 1994-11-29. should be back same results in principle.
i
Imre
03/09/2023, 2:37 PMok, now i see where the confusion comes from
IN ('1994-11-20', '1994-11-29')each argument in the list is a separate time range
that is because you could use a list of more complex time range expressions, such as:
IN ('2018-01-01', '2018-01-01T12:00', '2018-01-01;1d;1y;2')i think what you want is BETWEEN, instead of IN
SELECT * FROM scores
WHERE ts BETWEEN '2018-01-01T00:00:23.000000Z' AND '2018-01-01T00:00:23.500000Z';t
theseusyang
03/09/2023, 2:44 PMok, I try this, thank you!
i
Imre
03/09/2023, 2:46 PMno worries.