Блог

У меня есть отношение, которое поддерживает ежемесячные исторические данные. Эти данные добавляются в таблицу в последний день каждого месяца. Затем можно вызвать службу, которую я пишу, указав месяц и количество месяцев, предшествующих для получения исторических данных. Я делаю это, создавая переменные StartDate и EndDate, а затем возвращая данные между ними. Проблема, с которой я сталкиваюсь, заключается в том, что StartDate — это переменное число месяцев до EndDate, и я не могу понять, как использовать переменный период в интервале.

Вот что у меня есть:

     DECLARE
      endDate   TIMESTAMP := (DATE_TRUNC('MONTH',$2)   INTERVAL '1 MONTH') - INTERVAL '1 DAY';
      startDate TIMESTAMP := endDate - INTERVAL $3 'MONTH';
  

Я знаю, что строка для StartDate неверна. Как это правильно сделать?

Используйте эту строку:

 startDate TIMESTAMP := endDate - ($3 || ' MONTH')::INTERVAL;
  

и обратите внимание на пробел перед MONTH .
В принципе: вы создаете строку с like 4 MONTH и добавляете ее ::type в соответствующий интервал.

Редактировать: я нашел другое решение: вы можете вычислять с interval помощью этого:

 startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';
  

Для меня это выглядит немного приятнее.

Этот код не имеет прямого отношения к вашей ситуации, но он иллюстрирует, как использовать переменные в интервальной арифметике. Имя моей таблицы — «календарь».

 CREATE OR REPLACE FUNCTION test_param(num_months integer)
  RETURNS SETOF calendar AS
$BODY$

    select * from calendar
    where cal_date <= '2008-12-31 00:00:00'
    and cal_date > date '2008-12-31' - ($1 || ' month')::interval;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
  

Наиболее удобный способ, который я нашел для передачи переменного периода времени в Postgres, аналогичен ответу A.H.: путем умножения на целое число. Но это можно сделать и без приведения.

Пример Python (с использованием sqlalchemy и pandas):

 import pandas as pd
import sqlalchemy as sa

connection = sa.create_engine(connection_string)

df = pd.read_sql(
   sa.text('''
       select * from events
       where
       event_date between now() - (interval '1 day' * :ndays) and now()
       limit 100;
'''),
   connection,
   params={'ndays': 100}
)
  

Количество дней (ndays) передается как целое число из Python, поэтому непреднамеренные последствия менее вероятны.

Мой подход такой.. Это дает мне возможность установить конкретную дату или относительный диапазон.

 create or replace function search_data(_time_from timestamptz default null, _last_interval text default null)
    returns setof journal
    language plpgsql as
$$
begin
    return query
        select *
        from journal
        where created >= case
                             when _time_from is not null
                                 then _time_from
                             else now() - _last_interval::interval end;
end;
$$;
  

Хотя приведенный выше принятый ответ хорош, он немного устарел — для чтения требуется немного больше умственной энергии, чем необходимо, если вы работаете на Postgres 9.4 .

Старый способ (версии Postgres <9.4)

 startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';
  

Новый способ (Postgres 9.4 )

 startDate TIMESTAMP := endDate - MAKE_INTERVAL(MONTHS => $3);
  

Если вы используете Postgres 9.4 , новая функция MAKE_INTERVAL() кажется гораздо более читаемой — вероятно, поэтому они ее создали.

Если вы хотите что-то, что вы можете запустить в своем редакторе, вот несколько примеров (я заменил исходную привязку переменной $3 числом 2 для примера за 2 месяца до текущей даты).

 SELECT CURRENT_DATE - 2 * INTERVAL '1 MONTH';

SELECT CURRENT_DATE - MAKE_INTERVAL(MONTHS => 2);