1

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?

2
  • Better in which sense? Why aren't you satisfied with it? Jan 26, 2017 at 20:03
  • @dezso Maybe their is a better way (using interval, not using date_trunc, better naming GROUP BY column..?). If not then that's cool. I just try improve my SQL. I junior in SQL and don't feel confident.
    – Mio
    Jan 27, 2017 at 8:17

1 Answer 1

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.

4
  • The difference between the two is that an index on starttime wont work for the second version (and an index on starttime::date on the first one). Jan 26, 2017 at 20:03
  • @dezso Could you explain why? Thanks
    – Mio
    Jan 27, 2017 at 8:18
  • 1
    @BeniMio to keep it simple, those are two different data types, to start with. And (currently) there is no optimization in the planner to match them (AFAIK). Jan 27, 2017 at 9:16
  • If he needs and index, maybe it would make sense to create index with starttime converted. Something like: CREATE INDEX ON stl_query (starttime::date);
    – maicher
    Jan 27, 2017 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.