Getting first messages only for every day in a specific date range

Question

Table messages:

conv_id	user_id	content	sent_time
1	001	1st_msg	01-01-1990 00:00:00
2	002	2nd_msg	02-01-1990 00:00:00

How do we select the first message and the first reply sent in a conversation (conv_id) every day?

Notes:

1. There can be many users.
2. A single user can send multiple messages.
3. This is a dataset of people, and only two people are chatting with each other.
4. Throughout the day, multiple messages get exchanged.
5. First message can be defined by the minimum sent time within day 1 of the first user.
6. First reply can be defined as the minimum sent time within day 1 of the second user.

Answer 1

There is a truly simple solution with DISTINCT ON:

SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id)
       *
FROM   tbl
ORDER  BY date_trunc('day', sent_time), conv_id, user_id, sent_time;

db<>fiddle here

See:

• Select first row in each GROUP BY group?

An emulated index-skip may be faster. See:

• Optimize GROUP BY query to retrieve latest row per user
• Best performance to get distinct values for a given key from a big table

In case of timestamptz, "days" are defined by the timezone setting of your current session, unless defined explicitly. See:

• Ignoring time zones altogether in Rails and PostgreSQL