I'm trying to write a query to retrieve the last log of different time periods that can be given.

I'm pretty new to SQL, looked over a few other threads but didn't find any relevant answer to my problem.

I have a table with logs that hold cumulative information so I would like to retrieve the last log of each time period:

| log_datetime                    | details 
----------------------------------+---------
|2024-11-15 12:56:29.826209+00    |  Info
|2024-11-15 12:56:04.009068+00    |  Info
|2024-11-14 23:59:51.867106+00    |  Info
|2024-11-13 23:59:59.750325+00    |  Info
|2024-11-12 23:59:58.308221+00    |  Info
|2024-11-11 23:59:54.70224+00     |  Info
|2024-11-10 23:59:58.758529+00    |  Info
|2024-11-09 23:59:56.966632+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-07 23:59:57.830607+00    |  Info

I used this query :

SELECT DISTINCT ON (log_datetime::date) log_datetime, * 
FROM "logs" 
ORDER BY log_datetime::date DESC, log_datetime DESC; 

This returns the last log of each day:

| log_datetime                    | details |
----------------------------------+---------
|2024-11-15 23:59:56.945604+00    |
|2024-11-14 23:59:51.867106+00    |
|2024-11-13 23:59:59.750325+00    |
|2024-11-12 23:59:58.308221+00    |
|2024-11-11 23:59:54.70224+00     |
|2024-11-10 23:59:58.758529+00    | 
|2024-11-09 23:59:56.966632+00    |
|2024-11-08 23:59:54.472331+00    |
|2024-11-07 23:59:57.830607+00    |

I'd like to use one query where I can modify the time period and it will return the last log of each period as in instead of day return week or month or quarter or year a little stuck hope someone has an elegant way to do this instead of writing 4 different queries thanks.

solution managed using this modified version:

SELECT DISTINCT ON (date_trunc('day', log_datetime::timestamp)::date), * 
FROM "logs"
ORDER BY date_trunc('day', log_datetime::timestamp)::date DESC, log_datetime DESC;

I'm trying to write a query to retrieve the last log of different time periods that can be given.

I'm pretty new to SQL, looked over a few other threads but didn't find any relevant answer to my problem.

I have a table with logs that hold cumulative information so I would like to retrieve the last log of each time period:

| log_datetime                    | details 
----------------------------------+---------
|2024-11-15 12:56:29.826209+00    |  Info
|2024-11-15 12:56:04.009068+00    |  Info
|2024-11-14 23:59:51.867106+00    |  Info
|2024-11-13 23:59:59.750325+00    |  Info
|2024-11-12 23:59:58.308221+00    |  Info
|2024-11-11 23:59:54.70224+00     |  Info
|2024-11-10 23:59:58.758529+00    |  Info
|2024-11-09 23:59:56.966632+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-08 23:59:54.472331+00    |  Info
|2024-11-07 23:59:57.830607+00    |  Info

I used this query :

SELECT DISTINCT ON (log_datetime::date) log_datetime, * 
FROM "logs" 
ORDER BY log_datetime::date DESC, log_datetime DESC; 

This returns the last log of each day:

| log_datetime                    | details |
----------------------------------+---------
|2024-11-15 23:59:56.945604+00    |
|2024-11-14 23:59:51.867106+00    |
|2024-11-13 23:59:59.750325+00    |
|2024-11-12 23:59:58.308221+00    |
|2024-11-11 23:59:54.70224+00     |
|2024-11-10 23:59:58.758529+00    | 
|2024-11-09 23:59:56.966632+00    |
|2024-11-08 23:59:54.472331+00    |
|2024-11-07 23:59:57.830607+00    |

I'd like to use one query where I can modify the time period and it will return the last log of each period as in instead of day return week or month or quarter or year a little stuck hope someone has an elegant way to do this instead of writing 4 different queries thanks.

solution managed using this modified version:

SELECT DISTINCT ON (date_trunc('day', log_datetime::timestamp)::date), * 
FROM "logs"
ORDER BY date_trunc('day', log_datetime::timestamp)::date DESC, log_datetime DESC;

• postgresql

1 Answer 1

See examples

PREPARE qExample (varchar(10)) AS
 SELECT DISTINCT ON (date_trunc($1, log_datetime)) * 
  FROM "logs"
  ORDER BY date_trunc($1, log_datetime) DESC, log_datetime DESC
;
EXECUTE qExample('day');

CREATE function LastLog(dtpart varchar) RETURNS setof logs AS $$
 SELECT DISTINCT ON (date_trunc(dtpart, log_datetime)) * 
  FROM "logs"
  ORDER BY date_trunc(dtpart, log_datetime) DESC, log_datetime DESC
$$ LANGUAGE sql;
select * from LastLog('hour');
select * from LastLog('day');
select * from LastLog('month');

fiddle