Downsampling to (eg) "last result on Sunday night"

Question

We have a largish PostgreSQL table that records user's disk usage.

id      | bigint                      |           | not null | (unique constraint)
time    | timestamp without time zone |           | not null | 
quota   | bigint                      |           | not null | 0
usage   | bigint                      |           | not null | 0
zuser   | character varying(20)       |           | not null | 

We present data usage to users when they login, and we report on data usage up the org.

In order for the data to be reliable for users, we poll our storage every five minutes. But for our reporting we only need a smaller sample size. We would like to keep some of the data for internal use as well.

Our plan is to keep the month of data, and the last reading for each Sunday night for everything older than that. Because it's data usage, we don't want an average, we just want their last reading before midnight Sunday turns into Monday.

I have discovered date_trunc and have even an idea for an algorithm that includes something along the lines of

SELECT date_trunc('week', time), zuser, quota, usage GROUP BY zuser ORDER BY date_trunc('week', time) DESC

But I'm struggling to work out how to just get a single entry, and how to loop over all data back in time. I could do it in Python, but it seems a little clumsy and then it's something that needs to be run regularly.

In my head I had some sort of database trigger - a PostgreSQL event hitting a function every night at midnight that cleans up (NOW()-1 month)? - that would do it all.

But at the moment I'm looking for help on how to down sample each week into the single reading that happens at the end of that week.

----- edit -----

I ended up coming to

SELECT *
FROM storage.home 
WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
EXCEPT
SELECT * 
FROM storage.home 
WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes' 
AND extract (dow FROM time) = 0;

But when I change that to a DELETE, it fails on the EXCEPT clause because (I presume) it's no longer a SELECT...EXCEPT SELECT but rather a DELETE...EXCEPT SELECT which is non grammatical. Searching for DELETE EXCEPT is not simple due to language.

Is what I should be doing something more like this:

DELETE FROM storage.home 
WHERE id IN
  (SELECT id
  FROM storage.home 
  WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
  EXCEPT SELECT id
  FROM storage.home 
  WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes' 
  AND extract (dow FROM time) = 0);

Yes. I took a backup and ran the above. Worked exactly as expected. I considered the below for the left side of that EXCEPT because it's more true to needs, but I thought I'd start with the less accurate.

WHERE date_trunc('day', time) = CURRENT_DATE - interval '35 days')

Answer 1

You can select only those rows that are less than five minutes before midnight:

... WHERE
      -- less than 5 nimutes before midnight
      TIME '24:00:00' - CAST(time AS time) < INTERVAL '5 minutes'
      -- on a Sunday
      AND extract (dow FROM time) = 0