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

All we need is an easy explanation of the problem, so here it is.

Table messages:

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0