m

    Max Niederman

    1 month ago
    Is there a way that I could query a timestamp field as a delta from the last timestamp?
    Bolek Ziobrowski

    Bolek Ziobrowski

    1 month ago
    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