Title
#users-public
j

Jack

11/18/2022, 4:07 PM
Evening - Bit of an interesting problem, I have a query similar to the below which is intended on getting daily entries from the 22nd June for 150 days. This returns no records, but if I reduce the 150 down to 149 then it returns records...Any ideas?
select * from table where ts in '2022-06-22T23;1m;86400s;150'
4:09 PM
And what's even weirder - it works with 150 on one Quest instance, but does not work on the other. They are using the same version
4:17 PM
Is there a query I can run to see what timestamps this generates?
'2022-06-22T23;1m;86400s;150'
4:37 PM
Just using this basic example, I only seem to get 10 rows back, but would expect 20?
select * from (
SELECT x, timestamp_sequence(
to_timestamp('2022-01-01T23:00:00', 'yyyy-MM-ddTHH:mm:ss'), 86400000000L) as ts
FROM long_sequence(500)) _timestamp_(ts)
) where ts in '2022-06-22T23;1m;1d;20'
5:10 PM
And now my query for 150 randomly works...without a change on my side. Timing maybe somewhere?
5:10 PM
but 153 does not
Nicolas Hourcard

Nicolas Hourcard

11/18/2022, 6:04 PM
hey Jack, thanks we will have a look
Bolek Ziobrowski

Bolek Ziobrowski

11/18/2022, 8:47 PM
@Jack When you specify incomplete timestamp in first field it behaves like time range in 'ts' For example :
in '2022-06' means  '2022-03-01T00:00:00.000' -> '2022-03--31T23:59:59.999'
with intervals it means transformations apply to the time range, so :
'2022-06-22T23;1m;86400s;150'
means take 2022-06-22T23:00:00.000 -> 2022-06-22T23:59:59.999 add 1 minute to end of interval, that is 2022-06-22T23:00:00.000->2022-06-23T00:00:59.999 then produce 150 such intervals separated by 1 day . If you want to capture only 2022-06-22T23:00:00.000 -> 2022-06-22T23:01:00.000 then you've to specify full timestamp , e.g.
ts in '2022-06-22T23:00:00.000000Z;1m;86400s;150'
In qdb random functions are not materialized in subquery they're declared in so if there's something in the plan that needs calling function several time for the same column it may produce surprising results . On the other hand if you create table with data first and then query it you'll get the right result, e.g.
create table intvtest ( x  long, ts timestamp ) timestamp(ts);

insert into intvtest 
SELECT x, timestamp_sequence(
            to_timestamp('2022-01-01T23:00:00', 'yyyy-MM-ddTHH:mm:ss'), 86400000000L) as ts
FROM long_sequence(500) 

select * from intvtest where ts in '2022-06-22T23;1m;1d;20'
j

Jack

11/21/2022, 8:52 AM
Thanks very much @Bolek Ziobrowski - this makes the interval definition much clearer to me and has helped explain a couple of other things. Appreciate it.