Resampling/Group By Time Series Data

3

@nikhil.makan,

General formula is: SELECT TO_TIMESTAMP(UNIX_TIMESTAMP(your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP(your_timestamp_column,'YYYY-MM-DD HH24:MI:SS.FFF')%your_granularity_in_seconds)

So in your case, the query will for 5 minutes (300 seconds) will be:

SELECT tag, TO_TIMESTAMP(UNIX_TIMESTAMP(ts, 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP(ts,'YYYY-MM-DD HH24:MI:SS.FFF')%300), avg(val)
FROM nikm
GROUP BY 1, 2
ORDER BY 2

Here’s a full example with some sample data:

WITH nikm AS
  (
      select TO_TIMESTAMP('2023-01-31 10:00:02', 'YYYY-MM-DD HH24:MI:SS')  as ts, 'A' as tag, 10 as val
      UNION ALL
      select TO_TIMESTAMP('2023-01-31 10:08:02', 'YYYY-MM-DD HH24:MI:SS')  as ts, 'A' as tag, 5 as val
      UNION ALL
      select TO_TIMESTAMP('2023-01-31 10:09:02', 'YYYY-MM-DD HH24:MI:SS')  as ts, 'A' as tag, 20 as val
      UNION ALL
      select TO_TIMESTAMP('2023-01-31 10:20:02', 'YYYY-MM-DD HH24:MI:SS')  as ts, 'B' as tag, 6 as val
  )
SELECT tag, TO_TIMESTAMP(UNIX_TIMESTAMP(ts, 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP(ts,'YYYY-MM-DD HH24:MI:SS.FFF')%300) as ts_round, avg(val) as avg_val
FROM nikm
GROUP BY 1, 2
ORDER BY 2

image
image1490×106 8.8 KB

Hope that’s what you’re looking for. Based on my previous answer here.