I want to find out the total amount of time the queries are running in Redshift.
Is there any query with which I can get this information?.
I tried to get this data using stl_query but because there would be many queries running at the same point there could be overlapping execution times as well. So I cannot just aggregate based on date and sum up the execution time, So I have written the following query
WITH query_times AS (
SELECT
DATE_TRUNC('day', starttime) AS query_date,
starttime,
endtime
FROM
stl_query
WHERE
userid > 1 -- Exclude system queries
AND starttime >= DATEADD(day, -7, CURRENT_DATE)
),
distinct_intervals AS (
SELECT
query_date,
starttime AS period_start,
LEAD(starttime) OVER (PARTITION BY query_date ORDER BY starttime) AS next_starttime,
endtime
FROM
query_times
),
non_overlapping_intervals AS (
SELECT
query_date,
period_start,
CASE
WHEN next_starttime IS NULL OR next_starttime > endtime THEN endtime
ELSE next_starttime
END AS period_end
FROM
distinct_intervals
)
SELECT
query_date,
SUM(DATEDIFF(seconds, period_start, period_end))/3600 AS total_running_time_hours
FROM
non_overlapping_intervals
GROUP BY
query_date
ORDER BY
query_date;
This query seems to give me somewhat accurate data but I am not sure if this is the correct approach
If anyone can help me with this it would be much appreciated. Surprisingly there is not much data online about this kind of stats.
Again thank you before hand!
