Postgres query that ignores the first 2 entries of day per group

I want to make a postgres select query that ignores the first 2 entries of day per queue, where a "day" is defined as starting at 18:00

For example, If my data is like:

The select results should be ids 3 7 8 11 and 12:

I've tried using select row_number over a partition, but having issues getting the filtering correct.

// of course this doesn't work, since it only ignores first 2 of all time, not per day starting at 18:00

SELECT *
FROM (
  SELECT row_number() over (PARTITION by queue ORDER by timestamp) as row_n,
                                                                      *
  FROM mytable
) results
WHERE results.row_n > 2

// gives hourly time of timestamp

CAST((to_timestamp("timestamp"/1000000000)) AS time)

Any help is appreciated. Thanks!

If ts is your timestampyou should partition on the following expression

 partition by queue, (ts - interval'18' hour)::date

Note that you subtract the 18 hours (for the day start) and cast to date to get a single value.

Sample Query

To get the row_number of the first and second row per day starting at 18 hour. (leaving the rest out as not important)

with dt as (
select 'q1' queue, date'2023-01-01' + n * interval '3' hour ts from generate_series(1,20) t(n) union all
select 'q2' queue, date'2023-01-01' + n * interval '5' hour ts from generate_series(1,20) t(n)
)
select 
  queue, ts,
  row_number() over (partition by queue, (ts - interval'18' hour)::date  order by ts) as rn,
  (ts - interval'18' hour)::date 
from dt 

queue|ts                 |rn|date      |
-----+-------------------+--+----------+
q1   |2023-01-01 03:00:00| 1|2022-12-31|
q1   |2023-01-01 06:00:00| 2|2022-12-31|
q1   |2023-01-01 09:00:00| 3|2022-12-31|
...
q1   |2023-01-01 18:00:00| 1|2023-01-01|
q1   |2023-01-01 21:00:00| 2|2023-01-01|
q1   |2023-01-02 00:00:00| 3|2023-01-01|
...
q2   |2023-01-01 05:00:00| 1|2022-12-31|
q2   |2023-01-01 10:00:00| 2|2022-12-31|
q2   |2023-01-01 15:00:00| 3|2022-12-31|
q2   |2023-01-01 20:00:00| 1|2023-01-01|
q2   |2023-01-02 01:00:00| 2|2023-01-01|
q2   |2023-01-02 06:00:00| 3|2023-01-01|
...
q2   |2023-01-02 21:00:00| 1|2023-01-02|
q2   |2023-01-03 02:00:00| 2|2023-01-02|
q2   |2023-01-03 07:00:00| 3|2023-01-02|
...