Can't aggregate or subsample data; $_interval_ms has no effect, throws error

[ericarnoldy]

I can't seem to group by / subsample the data. I've got 90 days of data to query, at 30 second resolution and its pulling the whole thing, taking a long time, relative to InfluxDB.

This error is simply invalid syntax: SELECT time, max(measure_value::double) as "Sample Line" FROM $__database.$__table where $__timeFilter and device_id = '${serialNumber}' and measure_name = 'sample_line' GROUP BY time($__interval_ms)

This query return a more interesting error:

ValidationException: line 1:250: Right side of logical expression must evaluate to a boolean (actual: interval day to second):

SELECT time, max(measure_value::double) as "Sample Line" FROM $__database.$__table where $__timeFilter and device_id = '${serialNumber}' and measure_name = 'sample_line' and $__interval_ms

Quite possible its me, I might be querying incorrectly, any advice would be much appreciated.

[ryantxu]

Can you click on the "Query inspector" button on the editor and tell us what query is actually run?

[tiratatp]

Is it because $__interval_ms returns an interval?

If you want to cast to another datatype, you have to use cast() https://docs.aws.amazon.com/timestream/latest/developerguide/conversion-functions.html#conversion-functions.cast

For the second query, the error was because you use an interval type in a boolean statement (and).

[ericarnoldy]

Hey, that's handy Ryan @ryantxu ... Looks like $__interval_ms is returning a time, as in 300000ms ... so maybe I just need to figure out what to do with that.

This is what the query is looking like: SELECT time, measure_value::double as "PM 1.0", device_id, measure_name FROM "AirMonitors"."sensors" where time BETWEEN from_milliseconds(1617290547796) AND from_milliseconds(1617895347796) and device_id = 'AM-0008' and measure_name = 'pm1.0' group by time(300000ms) order by time

Which isn't right obviously.

Thanks for the hint @tiratatp ... I think what I need is actually the bin function in the select ... and tada!

SELECT bin(time, $__interval_ms), measure_value::double as "PM 10.0", device_id, measure_name FROM $__database.$__table where $__timeFilter and device_id = '${serialNumber}' and measure_name = 'pm10.0' order by time

So thank you both for your comments, working well.

[ryantxu]

Happy that worked

[farzadpanahi]

@ericarnoldy, just wondering, should you not add GROUP BY for the downsampling to work?

SELECT bin(time, $__interval_ms), AVG(measure_value::double) as "PM 10.0", device_id, measure_name FROM $__database.$__table where $__timeFilter and device_id = '${serialNumber}' and measure_name = 'pm10.0' 
GROUP BY bin(time, $__interval_ms),  device_id, measure_name