30-second interval between cells in PostgreSQL

Question

I have been trying for a few days to run a query in PostgreeSQL where I need to collect the following data: "Machine ID, Timestamp, Capsule ID". Each row has an index (primary key) and corresponds to a request, I have to get a list of machines from country X that make at least 2 coffees in less than half a minute. I can't calculate the time difference between one row and another. For example

 index        machineID           TimeStamp            Capsule ID
    1               A1            2021-08-02 14:18:00         6
    2               A1            2021-08-02 14:18:25         6
    3               A1            2021-08-02 14:18:31         6

I need to show only index 1 and 2 because they are within the 30 second interval but I can't do it.

I'm trying with this code

select brand.consumption.index, brand.consumption.recipeid, brand.consumption.machineid,  brand.consumption.rcv_timestamp, brand.consumption.volume_brewed, brand.recipecode_list.cupsizeid, brand.consumption.capsule_productid

from brand.consumption, brand.recipecode_list

where brand.consumption.recipeid = brand.recipecode_list.recipeid
and brand.consumption.capsule_productid = brand.recipecode_list.productid
and brand.consumption.capsule_recipetypeid = brand.recipecode_list.recipetype
and brand.consumption.index 
in 
(SELECT distinct tt1
  
FROM (
SELECT t1.index tt1,
               t2.index tt2,
               t1.machineid,
               (t2.utc_timestamp - t1.utc_timestamp) * (60 * 60 * 24)  result 
          
FROM brand.consumption t1
          
LEFT JOIN brand.consumption t2
           
ON (t1.machineid = t2.machineid)
         
where t2.rcv_timestamp >= sysdate -2) t1

where tt1 != tt2 and result between -30 and  30)

and brand.consumption.countryid = 'X'

order by brand.consumption.machineid, brand.consumption.utc_timestamp

I have a database in which there are several machines that produce coffee, and I have to filter which of these machines in country X produced more than 2 coffees in less than 30 sec. For example:

Machine A 
ID 1 - coffee at 19:00:00 
ID 2 - coffee at 19:00:05
ID 3 - coffee at 19:00:18 
ID 4 - coffee at 19:00:28 
ID 5 - coffee at 19:00:31 

Machine B 
ID 6 - coffee at 19:00:08 
ID 7 - coffee at 19:00:22 
ID 8 - coffee at 19:00:29 
ID 9 - coffee at 19:00:32 
ID 10 - coffee at 19:00:38 
ID 11 - coffee at 19:00:40

Expected Result: Machine A: 4 coffees in 30 seconds Machine B: 6 coffees in 30 seconds because from second 32 to 40 it produced 30 more coffees within 30 seconds.

user_2275388 · Answer

If you're looking for a way to display only the records that are less than 30 seconds apart from the first record of a given machine_id and capsule_id you can use the window function FIRST_VALUE. Something like this:

WITH j AS (
  SELECT *,utc_timestamp-FIRST_VALUE(utc_timestamp) OVER w AS calctime
  FROM t
  WINDOW w AS (PARTITION BY machine_id,capsule_id ORDER BY utc_timestamp
               RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
)
SELECT * FROM j
WHERE calctime <= interval '30 seconds'

Demo: db<>fiddle

30-second interval between cells in PostgreSQL

1 Answers