Time_bucket_gapfill for a data defined range

TimescaleDB and PostgreSQL
1

Just getting started with timescaledb. I have a sensor data log that has a “session_id” to group experiments. I’d like to run a query that finds the start and end of the “session” experiment, then provides a continuous time output of count,min,max of samples, etc.

I hoped that putting the start and stop times into a subquery would work for time_bucket_gapfill(),
ERROR: invalid time_bucket_gapfill argument: start must be a simple expression

Is there any way around this limitation in SQL without using a procedural language?

SELECT
  time_bucket_gapfill('1 hour', ts,session_start,session_end) AS ts_hour,
  count(ts) AS count
FROM rawdata ,
(
select session_id as max_session, min(ts) as session_start, max(ts) as session_end from rawdata where 
session_id=(select max(session_id) from sessions)
GROUP BY SESSION_ID
) as this_session
where session_id=this_session.max_session
-- Also does not work
--and ts>this_session.session_start and ts<=this_session.session_end and 
group by ts_hour
2

Hi @Panometric ,

Did you use “EXPLAIN” with this query?

Regards,

3

Yes unfortunately, you just get the error:
ERROR: invalid time_bucket_gapfill argument: start must be a simple expression
SQL state: 22023

4

@Panometric
This is a great article on hyperfunctions design. It could be useful. Next days I’ll try to use this hyperfunction in a similar manner and I’ll share here my experience.

Regards,

5

The issue seems to me that your are grouping by SESSION_ID so, you obtain a set of rows instead of an scalar.

A little tricky and dirty but the “||” ( or the format function) + \gexec should do the task in this case.

Regards,