Get count of timestamp entries from array newer than some specified timestamp-CodePudding

I have an array in Postgres that contains timestamps. I'd like to count the number of entries in the array newer than a timestamp specified in a query. As a bonus, I'd like to delete entries in the array older than another specified timestamp. Can this be done just using SQL in PostgreSQL?

CodePudding user response：

I would use a scalar sub-query:

select ... other columns ...,
       (select count(*)
        from unnest(the_array_column) as x(ts)
        where x.ts >= timestamp '2022-11-09 08:00:00') as element_count
from the_table;

CodePudding user response：

To answer your first question,

You can use UNNEST function to expand an array into rows and then apply WHERE clause to filter the array elements.

SELECT 
  id, 
  COUNT(*) 
FROM 
  (
    select 
      id, 
      UNNEST(mytsarr) AS ts 
    FROM 
      mytable
  ) t 
WHERE 
  ts > '1900-01-01' 
GROUP BY 
  id;

Replace id column with any unique column in your table.

For the bonus question, the below query will update the array column removing timestamps older than '1900-01-01'.

UPDATE 
  mytable 
SET
  myarrcol = (
    SELECT 
      array_agg(t) 
    FROM 
      unnest(myarrcol) AS t 
    where 
      t > '1900-01-01'
  );