LINUX.ORG.RU

История изменений

Исправление x3al, (текущая версия) :

А.

CREATE TABLE logs (id SERIAL PRIMARY KEY, event_time TIMESTAMP NOT NULL, message TEXT NOT NULL);
CREATE INDEX logs_date ON logs(event_time);


INSERT INTO LOGS (event_time, message)
VALUES 
('2025-09-29', 'in range'), 
('2025-09-26', 'in range'),
('2025-09-25T01:00:00', 'in range'), 
('2025-09-09', 'out of range'),
('2025-09-25T02:00:00', 'in range'),
('2025-09-23', 'in range'),
('2025-09-22', 'in range');


WITH first_nonempty_date AS (
  SELECT day::timestamp AS day_ts FROM (
    SELECT DISTINCT event_time::date AS day
    FROM logs
    WHERE message IS NOT NULL AND btrim(message) <> ''
    ORDER BY day DESC
    LIMIT 5
  ) ORDER BY day ASC LIMIT 1
)
SELECT logs.*
FROM logs
JOIN first_nonempty_date ON TRUE
WHERE event_time >= first_nonempty_date.day_ts
ORDER BY event_time DESC;

Так?

Исправление x3al, :

А.

CREATE TABLE logs (id SERIAL PRIMARY KEY, event_time TIMESTAMP NOT NULL, message TEXT NOT NULL);
CREATE INDEX logs_date ON logs(event_time);


INSERT INTO LOGS (event_time, message)
VALUES 
('2025-09-29', 'in range'), 
('2025-09-26', 'in range'),
('2025-09-25T01:00:00', 'in range'), 
('2025-09-09', 'out of range'),
('2025-09-25T02:00:00', 'in range'),
('2025-09-23', 'in range'),
('2025-09-22', 'in range');


WITH first_nonempty_date AS (
  SELECT day::timestamp AS day_ts FROM (
    SELECT DISTINCT event_time::date AS day
    FROM logs
    WHERE message IS NOT NULL AND btrim(message) <> ''
    ORDER BY day DESC
    LIMIT 7
  ) ORDER BY day ASC LIMIT 1
)
SELECT logs.*
FROM logs
JOIN first_nonempty_date ON TRUE
WHERE event_time > first_nonempty_date.day_ts
ORDER BY event_time DESC;

Так?

Исходная версия x3al, :

А.

CREATE TABLE logs (id SERIAL PRIMARY KEY, event_time TIMESTAMP NOT NULL, message TEXT NOT NULL);
CREATE INDEX logs_date ON logs(event_time);


INSERT INTO LOGS (event_time, message)
VALUES 
('2025-09-29', 'in range'), 
('2025-09-26', 'in range'),
('2025-09-25T01:00:00', 'in range'), 
('2025-09-09', 'out of range'),
('2025-09-25T02:00:00', 'in range'),
('2025-09-23', 'in range'),
('2025-09-22', 'in range');


WITH first_nonempty_date AS (
  SELECT day::timestamp AS day_ts FROM (
    SELECT DISTINCT event_time::date AS day
    FROM logs
    WHERE message IS NOT NULL AND btrim(message) <> ''
    ORDER BY day DESC
    LIMIT 7
  ) ORDER BY day ASC LIMIT 1
)
SELECT *
FROM logs
JOIN first_nonempty_date ON TRUE
WHERE event_time > first_nonempty_date.day_ts
ORDER BY event_time DESC;

Так?