Aggregating timestamp for uptime number

Grafana
,
1

with athena data source is there a way to sum all timestamp entries (field name “timestamp”) to one number which is a percentage of entries per minutes, meaning if


2024-03-11 01:10:00
2024-03-11 01:09:00
2024-03-11 01:08:00
2024-03-11 01:07:00
2024-03-11 01:06:00 
2024-03-11 01:05:00 
2024-03-11 01:04:00 
2024-03-11 01:03:00 
2024-03-11 01:01:00

the 2 minute is absent, and I want a query that will return a 90%

2

Hello,

I’m not sure of what you are asking. But as I understand it, you could just count the number of field you have:

SELECT COUNT(DISTINCT timestamp) FROM your_table_name

and then perform your percentage calculation?

Regards

3

not quite, Let’s suppose I always should have 1-2 entry per minute, but if I don’t - it means something went wrong. And I want to know the ratio of success/failure here

I already make use of DISTINCT DATE_TRUNC('minute', "timestamp") as timestamp to make sure I only select 1 message per minute, but not sure how to:

  • detect if there is a gap and how
  • account for it in the end percentage number

Also, I do not know if there a way to take each 5 min or take each 5th row?

4

not sure if this is possible in athena, but in ms sql server you can dynamically create a calendar table which you can use to find the gap date. then based on that you can do the percentage. for example

;with sample_data
as
(
select '2024-03-11 01:10:00' as date_time union
	select '2024-03-11 01:09:00' union
	select '2024-03-11 01:08:00' union
	select '2024-03-11 01:07:00' union
	select '2024-03-11 01:06:00' union 
	select '2024-03-11 01:05:00' union 
	select '2024-03-11 01:04:00' union 
	select '2024-03-11 01:03:00' union 
	select '2024-03-11 01:01:00'
), minmax as --the start and end datetime
(
	select min(date_time) as min_date_time,
	       max(date_time) as max_date_time
	 from sample_data
), calendar(n) AS --
(
  SELECT 0 UNION ALL SELECT n + 1 
   FROM calendar
   cross apply minmax mm
  WHERE n < DATEDIFF(mi, mm.min_date_time, mm.max_date_time)
),d(d) AS 
(
  SELECT DATEADD(mi, n, mm.min_date_time) 
  FROM calendar
  cross apply minmax mm
)
select * 
  from d
  left join sample_data src on d.d = src.date_time

will generate

image

So your question is more athena than grafana really. Asking on an athena forum might get you a quicker answer

1 Like
5

Ok, will try, I had no idea grafana can be so different depending on datasource.

1 Like