admin管理员组

文章数量:1333389

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
Share Improve this question edited Nov 20, 2024 at 20:17 marc_s 756k184 gold badges1.4k silver badges1.5k bronze badges asked Nov 20, 2024 at 18:56 IlanIlan 133 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 0

See examples

log_datetime details
2024-11-15 11:56:29.826209 Info
2024-11-15 12:56:04.009068 Info
2024-11-14 23:59:51.867106 Info
2024-11-13 23:59:59.750325 Info
2024-11-12 23:59:58.308221 Info
2024-11-11 23:59:54.70224 Info
2024-11-10 23:59:58.758529 Info
2024-11-09 23:59:56.966632 Info
2024-11-08 23:59:54.472331 Info
2024-11-08 23:59:54.472331 Info
2024-11-08 23:59:54.472331 Info
2024-11-08 23:59:54.472331 Info
2024-11-08 23:59:54.472331 Info
2024-11-08 23:59:54.472331 Info
2024-11-07 23:59:57.830607 Info 
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');
log_datetime details
2024-11-15 12:56:04.009068 Info
2024-11-14 23:59:51.867106 Info
2024-11-13 23:59:59.750325 Info
2024-11-12 23:59:58.308221 Info
2024-11-11 23:59:54.70224 Info
2024-11-10 23:59:58.758529 Info
2024-11-09 23:59:56.966632 Info
2024-11-08 23:59:54.472331 Info
2024-11-07 23:59:57.830607 Info 
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');
log_datetime details
2024-11-15 12:56:04.009068 Info
2024-11-15 11:56:29.826209 Info
2024-11-14 23:59:51.867106 Info
2024-11-13 23:59:59.750325 Info
2024-11-12 23:59:58.308221 Info
2024-11-11 23:59:54.70224 Info
2024-11-10 23:59:58.758529 Info
2024-11-09 23:59:56.966632 Info
2024-11-08 23:59:54.472331 Info
2024-11-07 23:59:57.830607 Info
log_datetime details
2024-11-15 12:56:04.009068 Info
2024-11-14 23:59:51.867106 Info
2024-11-13 23:59:59.750325 Info
2024-11-12 23:59:58.308221 Info
2024-11-11 23:59:54.70224 Info
2024-11-10 23:59:58.758529 Info
2024-11-09 23:59:56.966632 Info
2024-11-08 23:59:54.472331 Info
2024-11-07 23:59:57.830607 Info
log_datetime details
2024-11-15 12:56:04.009068 Info

fiddle

本文标签：

版权声明：本文标题：postgresql - I need to retrieve the last log of a period of time(day,week, month, year) - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742336028a2455643.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。