Count specific query occurred in Redshift

Question

I wanted to count the number queries executed over one day. I have this working version :

SELECT DATE_TRUNC('day', starttime) AS date,
       COUNT(*) AS COUNT
FROM stl_query 
WHERE querytxt LIKE 'COPY sensor_%_temp FROM%'
  AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'
GROUP BY 1;

Is there a better way of doing it?

Answer 1

IMO your query looks good. I have one idea.

Instead of:

    AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'

You can write:

    AND starttime::date = '2017-01-24'

The double column operator (::) converts DateTime type to Date, resulting shorter query.