Início / dba / Perguntas / 152929
Accepted
beldaz
Asked: 2016-10-21 15:11:02 +0800 CST

Divisão de intervalo do PostgreSQL

  • 772

Isso apareceu algumas vezes, por exemplo, no grupo de notícias postgresql e no wiki . Em geral, a relação entre diferentes intervals pode não ser bem definida - um mês pode ter diferentes números de dias, dependendo de qual mês (e ano) está sendo considerado. Mas às vezes é necessário calcular quantos intervalos ocorrem entre dois pontos no tempo, por exemplo (exemplo simplificado):

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

Isso não é permitido no PostgreSQL porque é uma divisão entre dois intervalos, que não teria um comportamento geral bem definido pelos motivos acima. Existe uma solução alternativa para quando um intervalo pode ser convertido em segundos, mas qual é a melhor maneira quando não é o caso, por exemplo, quando o intervalo é da ordem de milissegundos?

postgresql time

4 respostas

  1. Best Answer

    Como @a_horse_with_no_name menciona em seu comentário, a divisão pode ser obtida pela conversão em milissegundos.

    SELECT 
  (1000*EXTRACT(EPOCH FROM tend - tstart) 
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength) 
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;
    • 3

  2. A solução alternativa à qual você vincula (extrair épocas dos intervalos e dividir por eles) funciona bem para segundos e milissegundos. Portanto, se esse for o seu caso de uso, use-o.

    Se você quiser ser mais geral e retornar NULL quando nenhuma resposta for possível, porém, temos que ser mais complexos:

    -- An interval consists of a number of years, months, days, hours,
-- minutes, and seconds.  The problem of dividing two intervals lies
-- in the fact that "one month" is not a fixed number of days.
-- Therefore, dividing, say, P1M with P1D would not make sense.

CREATE OR REPLACE FUNCTION interval_months_part(INTERVAL)
        RETURNS INTEGER AS $$
        SELECT EXTRACT(years FROM $1)::INTEGER * 12
                + EXTRACT(months FROM $1)::INTEGER
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Years plus months as a whole number of months';

CREATE OR REPLACE FUNCTION interval_seconds_part(INTERVAL)
        RETURNS DOUBLE PRECISION AS $$
        SELECT EXTRACT(days FROM $1) * 24 * 60 * 60
                + EXTRACT(hours FROM $1) * 60 * 60
                + EXTRACT(mins FROM $1) * 60
                + EXTRACT(secs FROM $1);
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Days, hours, minutes, and seconds as seconds';

-- If we can divide exactly, do so.  Otherwise return NULL, unless
-- fudging is requested, in which case all months are implicitly
-- assumed to be 30 days, (except for 12 months, which become 365.25
-- days), by extracting and dividing the epochs.
CREATE OR REPLACE FUNCTION interval_divide(dividend INTERVAL,
                                                divisor INTERVAL,
                                                fudge BOOL DEFAULT false)
        RETURNS DOUBLE PRECISION AS $$
        SELECT CASE WHEN interval_months_part($1) = 0
                                AND interval_months_part($2) = 0
                        THEN interval_seconds_part($1)
                                / interval_seconds_part($2)
                        WHEN interval_seconds_part($1) = 0
                                AND interval_seconds_part($2) = 0
                        THEN CAST(interval_months_part($1)
                                        AS DOUBLE PRECISION)
                                / interval_months_part($2)
                        WHEN fudge
                                THEN EXTRACT(EPOCH FROM $1)
                                        / EXTRACT(EPOCH FROM $2)
                        ELSE NULL
                        END;
$$ LANGUAGE SQL IMMUTABLE STRICT;
    • 1

  3. Embora, por enquanto, o Postgres não pareça suportar divisão de intervalo, é claro que você pode converter esses intervalos em um número que pode dividir - como segundos.

    As outras respostas propostas sugerem: EXTRACT(MILLISECOND FROM interval '1 day'), mas descobri que isso às vezes retorna 0. E, claro, não se pode dividir por zero.

    Mas o seguinte funciona muito melhor e é mais confiável:

    SELECT EXTRACT(EPOCH FROM interval '17 days') / EXTRACT(EPOCH FROM interval '1 day');

    Então, para dividir intervalos - extraia o número de segundos no intervalo usando EXTRACT(EPOCH, interval)e divida-os.

    • 1

  4. A resposta aceita está errada.

    Não acho que você deva adicionar os milissegundos de extração do intervalo, pois a época de extração do intervalo já extrai segundos com milissegundos. A documentação diz:

    epoch -- [...] para valores de intervalo, o número total de segundos no intervalo

    milissegundos -- O campo de segundos, incluindo partes fracionárias, multiplicado por 1000. Observe que isso inclui segundos completos.

    Você pode experimentar:

    select extract('epoch' from '1987 years 6 months 5 days 8 hours 15 min 30.123 seconds'::interval);

    date_part
-----------------
 62720964930.123

    você vê como o ".123" aqui nos diz que os milissegundos já foram incluídos?

    Acho que já passou da hora de adicionar o operador de divisão para intervalos.

    CREATE OR REPLACE FUNCTION interval_div(interval, interval) RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM $1) / EXTRACT(EPOCH FROM $2)
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR / ( 
   FUNCTION = interval_div,
   LEFTARG = interval, 
   RIGHTARG = interval );

    E com isso:

    select '1 hour'::interval / '1 second'::interval;
 ?column?
----------
     3600

select '1 hour'::interval / '1.123 second'::interval;
     ?column?
-------------------
 3205.699020480855

    Algumas ressalvas:

    select '1 month'::interval / '1 day'::interval;
 ?column?
----------
       30

integrator=> select '12 month'::interval / '1 day'::interval;
 ?column?
----------
   365.25

    Ano juliano, mas não um mês juliano médio.

    select '1 year'::interval / '1 day'::interval / 12;
 ?column?
----------
  30.4375
    • 0

relate perguntas