j

    Jack

    1 month ago
    Hello - Is it possible to do a "where in" but for the "in" clause provide a select query which generates a list of timestamps?
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    that usually done with join
    j

    Jack

    1 month ago
    assuming an asof right?
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    can be simple inner join i
    j

    Jack

    1 month ago
    ah yes
    let me give that a go - thanks!
    What about if I wanted to use the power of doing a timestamp in '2022-01-01T00:00'?
    rather than the join..
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    that should be constants then
    j

    Jack

    1 month ago
    are you able to generate those constants from a query? similar to the timestamp_sequence...
    (rather than passing in a list at point of query)
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    yes, there is a way to generate N time intervals of given legth
    j

    Jack

    1 month ago
    if you have an example that would be fantastic, I did try using the timestamp_sequence but then you have a cursor not a constant..
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    select * from 
    trades where 
    timestamp in '2022-04-23T10:00:00.000000Z;1s;20s;30'
    it’s <start point>;interval;delay;times
    j

    Jack

    1 month ago
    ah nice
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    in this case take 1 second every 20 seconds
    for 30 times
    select timestamp, first(price) from 
    trades where 
    timestamp in '2022-04-23T10:00:00.000000Z;1s;20s;30'
    sample by 1s
    ALIGN to CALENDAR
    this may have more obvious output
    j

    Jack

    1 month ago
    yep fantastic
    exactly what I needed - works a charm. Thanks very much!
    when using the interval/delay/times - is it possible to add another datetime format into the IN? Example: ts in ('2022-01-31T23;1m;86400s;240', '2022-01-01')
    seems to give me an invalid date
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    try OR instead
    ts in ‘2022-01-31T23;1m;86400s;240’ or ts in ‘2022-01-01’
    j

    Jack

    1 month ago
    so ts in ('...') or ts in ('...')?
    kk,. think I tried that and the performance took a big hit
    yeh that seems to take a hit, even vs a UNION with another select query with the extra in
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    shouldn’t be different what’s full query
    j

    Jack

    1 month ago
    select * from table where (ts in '2022-07-31T23;1m;86400s;20' or ts in '2022-01-31')
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    ah, true, or are not good
    j

    Jack

    1 month ago
    vs something like select * from table where ts in '2022-07-31T23;1m;86400s;20' UNION ALL select * from table where ts in '2022-01-31'
    thats fine I can do the union if no other way
    but of a weird one - but would you expect this to work? where ts in ('2022-08-10', '2022-08-11')
    seems to give me no results back, but running them independently works..
    ah - does 2022-08-10 imply 00:00:00.000000Z?