Блог

Текущая ситуация

У меня есть таблица, которая отслеживает использование определенного ресурса. Это выглядит так:

 started   | timestamp with time zone | not null
last_ping | timestamp with time zone |
stopped   | timestamp with time zone |
  

Поля stopped и last_ping могут быть пустыми. Если они оба заполнены, stopped это актуально.

Даты начала и окончания могут занимать всего несколько секунд или несколько дат.

Цель

Я хочу получить список ежедневного использования за последние 14 дней.

Текущее состояние

Я знаю, что могу получить список за последние 14 дней:

 SELECT day
FROM generate_series(CURRENT_DATE, CURRENT_DATE - 14, '-1 day'::interval) day;
  

Я могу получить общую продолжительность для каждой записи использования:

 SELECT COALESCE(stopped, last_ping, started) - started AS duration
FROM api_sessionusage;
  

Я также могу объединить два запроса и добавить ограничение, которое учитывает только продолжительность до полуночи:

 SELECT
  day,
  (
    SELECT SUM(
      LEAST(COALESCE(stopped, last_ping, started), day   interval '1 day') - started
    )
    FROM api_sessionusage
    WHERE started >= day AND started < day   interval '1 day'
  ) AS aggregated_duration
FROM generate_series(CURRENT_DATE, CURRENT_DATE -14, '-1 day'::interval) day;
  

Проблема здесь в том, что сеансы использования, которые заканчиваются после полуночи, учитываются только для начального дня, но продолжительность после полуночи не учитывается.

Как я могу переписать запрос, чтобы получить агрегированное использование за каждый день в течение последних 14 дней?

Используйте операторы перекрытия и пересечения типа диапазона временных меток:

 select 
    day, 
    sum(upper(daily_range) - lower(daily_range))
from (
    select 
        day, 
        session_range * tstzrange(day, day::date   1) daily_range
    from generate_series(current_date, current_date -14, '-1 day'::interval) day
    left join (
        select tstzrange(started, coalesce(stopped, last_ping, started)) session_range
        from api_sessionusage
        ) s
    on session_range amp;amp; tstzrange(day, day::date   1)
) s
group by 1
order by 1;
  

Примечание.

 coalesce(stopped, last_ping, started) - started as duration
  

выдает ноль, если оба stopped и last_ping равны нулю. Может быть, это должно быть

 coalesce(stopped, last_ping, current_date) --?