CODEWITHSUNDEEP
sqlpostgresql
GKW Agro Tech Solues em Tecnol
GKW Agro Tech Solues em Tecnol

Reputation: 17

Count number of occurrences of a variable

I have a table in my postgres database, where my system records every 5 seconds the state of a variable. I need to count the number of occurrences that a "value = 1" variable occurred during the hours of a day. That is, during the 24 hours of a day, how many hours did my variable stay with "value = 1".

Based on the attached figure, my select should return "2019-10-31 1" and "2019-11-02 01"

I don't know if I was clear enough. Hope so.

enter image description here

Upvotes: 0

Views: 168

Answers (3)

VBoka
VBoka

Reputation: 9083

Is this what you are looking for:

select date(DATE_TRUNC('day', dat)) as "Date we checking"
      , sum(difference) as "Number of occurrences"
from (
select DATE_TRUNC('hour', read_date) dat
       , DATE_TRUNC('hour', LAG(read_date) OVER (ORDER BY id)) prev_date
       , extract(hour from DATE_TRUNC('hour', read_date) - DATE_TRUNC('hour', LAG(read_date) OVER (
      ORDER BY id))) difference
from system) t
where difference <= 1
and dat between '2019-10-31 09:00:00' and '2019-11-01 09:00:00'
group by DATE_TRUNC('day', dat);

With this line you define the 24 hours you want to check: 

and dat between '2019-10-31 09:00:00' and '2019-11-01 09:00:00'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

That is, during the 24 hours of a day, how many hours did my variable stay with "value = 1"

You can use:

select read_date::date,
       count(distinct date_trunc('hour', read_date)) filter (where value = 1) as num_hours
from t
group by read_date::date;

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74660

If you filter the results to only those where value = 1, and get the distinct list of read_dates truncated the nearest hour then it will be a list of all the day-hours where a 1 occurred. If we then cast it to a date so it loses the hour, group by that and count, then you'll get the number of hours in a day where value was 1:

SELECT
  d::date,
  count(*) 
FROM
 (
  SELECT DISTINCT date_trunc('hour', read_date) as d
  FROM table
  WHERE value = 1
 ) x
 GROUP BY d::date

If, as the commenter has asked, you only seek hours where the value was 1, we can perhaps do something like:

SELECT
  d::date,
  count(*) 
FROM
 (
  SELECT date_trunc('hour', read_date) as d
  FROM table
  GROUP BY date_trunc('hour', read_date)
  HAVING MIN(value) = 1 AND MAX(value) = 1
 ) x
 GROUP BY d::date

The inner query produces a list of only those hours where the min and max values were 1. Any deviation from 1 will cause one of these to be false

Upvotes: 2

Related Questions