Get fist record based on time in PostgreSQL-CodePudding

DO we have a way to get first record considering the time.

example get first record today, get first record yesterday, get first record day before yesterday ...

Note: I want to get all records considering the time

sample expected output should be

first_record_today,
first_record_yesterday,..

CodePudding user response：

To get the first record based on the day, month, and year in PostgreSQL, you can use the DATE_TRUNC function, along with the MIN function and a GROUP BY clause.

For example, if you have a table called posts with a created_at column that contains timestamps, you can use the following query to get the first record for each day:
SELECT MIN(created_at)
FROM posts
GROUP BY DATE_TRUNC('day', created_at);

This query uses the DATE_TRUNC function to truncate the created_at timestamps to the day, and then groups the records by the truncated date. The MIN function is used to get the minimum (i.e. first) timestamp for each day.

You can change the 'day' argument to 'month' or 'year' to get the first record for each month or year, respectively. You can also use the DATE function to extract just the date part of the timestamp, if you don't need the time component.

For example, to get the first record for each month, you can use the following query:

SELECT MIN(created_at)
FROM posts
GROUP BY DATE_TRUNC('month', created_at);

Or, to get the first record for each year, you can use the following query:

SELECT MIN(created_at)
FROM posts
GROUP BY DATE_TRUNC('year', created_at);

These queries will return the first record for each day, month, or year, based on the created_at timestamp.

CodePudding user response：

As I understand the question, the "first" record per day is the earliest one.

For that, we can use RANK and do the PARTITION BY the day only, truncating the time.

In the ORDER BY clause, we will sort by the time:

SELECT sub.yourdate FROM ( 
  SELECT yourdate, 
    RANK() OVER 
      (PARTITION BY DATE_TRUNC('DAY',yourdate) 
       ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

In the main query, we will sort the data beginning with the latest date, meaning today's one, if available.

We can try out here: db<>fiddle

If this understanding of the question is incorrect, please let us know what to change by editing your question.

A note: Using a window function is not necessary according to your description. A shorter GROUP BY like shown in the other answer can produce the correct result, too and might be absolutely fine. I like the window function approach because this makes it easy to add further conditions or change conditions which might not be usable in a simple GROUP BY, therefore I chose this way.

EDIT because the question's author provided further information:

Here the query fetching also the first message:

SELECT sub.yourdate, sub.message FROM ( 
  SELECT yourdate, message,
    RANK() OVER (PARTITION BY DATE_TRUNC('DAY',yourdate) 
    ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

Or if only the message without the date should be selected:

SELECT sub.message FROM ( 
  SELECT yourdate, message,
    RANK() OVER (PARTITION BY DATE_TRUNC('DAY',yourdate) 
    ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

Updated fiddle here: db<>fiddle