How to make a group query to select multiple rows?

Question

I have a DateTime column (timestamp 2022-05-22 10:10:12) with a batch of stamps per each day.

I need to filter the rows where stamp is before 9am (here is no problem) and I'm using this code:

SELECT * FROM tickets
  WHERE date_part('hour'::text, tickets.date_in) < 9::double precision;

The output is the list of the rows where the time in timestamp is less than 9 am (50 rows from 2000).

date_in
2022-05-22 08:10:12
2022-04-23 07:11:13
2022-06-15 08:45:26

Then I need to find all the days where at least one row has a stamp before 9 am - and here I'm stuck. Any idea how to select all the days where at least one stamp was before 9 am?

The code I'm trying:

SELECT * into temp1 FROM tickets
WHERE date_part('hour'::text, tickets.date_in) < 9::double precision
ORDER BY date_part('day'::text, date_in);

Select * into temp2
from tickets, temp1
where date_part('day'::text, tickets.date_in) = date_part('day'::text, temp1.date_in);

Update temp2 set distorted_route = 1;

But this is giving me nothing.

Expected output is to get all the days where at least one route was done before 9am:

date_in
2022-05-22 08:10:12
2022-05-22 10:11:45
2022-05-22 12:14:59
2022-04-23 07:11:13
2022-04-23 11:42:25
2022-06-15 08:45:26
2022-06-15 15:10:57

Should I make an additional table (temp1) to feed it with the first query result (just the rows before 9am) and then make a cross table query to find in the source table public.tickets all the days which are equal to the public.temp1?

Select * from tickets, temp1
where TO_Char(tickets.date_in, 'YYYY-MM-DD')
    = TO_Char(temp1.date_in, 'YYYY-MM-DD');

or like this:

SELECT *
FROM tickets
WHERE EXISTS (
   SELECT date_in FROM TO_Char(tickets.date_in, 'YYYY-MM-DD') = TO_Char(temp1.date_in, 'YYYY-MM-DD')
   );

Ideally, I'd want to avoid using a temporary table and make a request just for one table.
After that, I need to create a view or update and add some remarks to the source table.

Answer 1

Assuming you mean:

How to select all rows where at least one row exists with a timestamp before 9 am of the same day?

SELECT *
FROM   tickets t
WHERE  EXISTS (
   SELECT FROM tickets t1
   WHERE  t1.date_in::date = t.date_in::date  -- same day
   AND    t1.date_in::time < time '9:00'      -- time before 9:00
   AND    t1.id <> t.id                       -- exclude self
   )
ORDER  BY date_id;  -- optional, but typically helpful

id being the PK column of your undisclosed table.

But be aware that ...

1. ... typically you'll want to work with timestamptz instead of timestamp. See:

   • Ignoring time zones altogether in Rails and PostgreSQL
   • https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

2. ... this query is slow for big tables, because it cannot use a plain index on (date_id) (not "sargable"). Related:

   • How do you do date math that ignores the year?

   There are various ways to optimize performance. The best way depends on undisclosed information for performance questions.