Question 113844 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get count of timestamp entries from array newer than some specified timestamp

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?

Tags:

2

Answers


  1. 0

    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'
  );
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search