CODEWITHSUNDEEP
sqlpostgresql
absolute333
absolute333

Reputation: 81

Different results in queries

I have two similar queries:

select COUNT(*), to_char(start_time,'YYYYMMDD') from EXECUTIONS where 
to_char(start_time,'YYYYMMDD') >= '20200101' and
to_char(start_time,'YYYYMMDD') <= '20200131' 
AND SERVER IN ('PRO','PRE','DE')
group by to_char(start_time,'YYYYMMDD') ORDER BY to_char(start_time,'YYYYMMDD')  ASC ;
---------
select
    SERVER,
    APP,
    sum(case when ended_status = 16 then 1 else 0 end) OK,
    sum(case when ended_status = 32 then 1 else 0 end) BAD,
        count(ended_status) TOTAL
from EXECUTIONS
where start_time >= '20200101' and start_time <= '20200131'
and SERVER in ('PRO','PRE','DE')
group by
    SERVER,
    APP
order by SERVER,APP;

I've saw that in the last query the last day of the month (2020131) is not included in the query.

I can't understand why. Could you help me please?

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

It has a time component. The correct logic is:

where start_time >= '20200101' and
      start_time <  '20200201'

This is safe for both dates and date/times. I strongly recommend using inequalities like this for all date comparisons, so you don't have to worry about whether or not a time component is part of the value. And the above is index safe.

Upvotes: 3

Related Questions