Max Niederman

08/20/2022, 9:38 PM
Is there a way that I could query a timestamp field as a delta from the last timestamp?
Bolek Ziobrowski

Bolek Ziobrowski

08/22/2022, 10:30 AM
Hi @Max Niederman. Not sure what you mean but you could select from table, lt join and use datediff to get the delta . Something like :
create table delta ( ts timestamp, val double) timestamp(ts) partition by year;
insert into delta select cast(x*3600000 as timestamp), x from long_sequence(1000000);

selecT min(ts) ,max(ts), count(*) from delta;

select d1.*, d2.*, datediff( 's', d1.ts, d2.ts )
from delta d1
lt join delta d2 ;

1970-01-01T00:00:03.600000Z 1 null null null
1970-01-01T00:00:07.200000Z 2 1970-01-01T00:00:03.600000Z 1  3
1970-01-01T00:00:10.800000Z 3 1970-01-01T00:00:07.200000Z 2 3
https://questdb.io/docs/reference/sql/join/#lt-join https://questdb.io/docs/reference/function/date-time#datediff