Início / coding / Perguntas / 79265819
Accepted
MAK
Asked: 2024-12-10 00:55:40 +0800 CST

Gerar séries de timestamps entre datas fornecidas

  • 772

As datas mínimas e máximas fornecidas são:

Data mínima:2023-06-19 09:54:23.000

Data máxima:now()::timestamp(0)-interval '1 year'

Saída esperada:

start_date                      end_date
-------------------------------------------
2023-12-01 00:00:00.000     2023-12-09 23:59:59.999
2023-11-01 00:00:00.000     2023-11-30 23:59:59.999
2023-10-01 00:00:00.000     2023-10-31 23:59:59.999
2023-09-01 00:00:00.000     2023-09-30 23:59:59.999
2023-08-01 00:00:00.000     2023-08-31 23:59:59.999
2023-07-01 00:00:00.000     2023-07-31 23:59:59.999
2023-06-01 00:00:00.000     2023-06-30 23:59:59.999

Minha tentativa:

with cte 
as 
(
    SELECT days 
    from generate_series('2023-06-19 09:54:23.000',now()::timestamp(0)-interval '1 year',  '1 month') days 
    order by days desc
)
select date_trunc('MONTH', days::date)::timestamp(0) start_date, 
       (date_trunc('month',  days::date)::timestamp(0) + interval '1 month -1 millisecond') end_date
from cte;

A seguir está a data ausente do resultado da consulta acima.

start_date                      end_date
--------------------------------------------------
2023-12-01 00:00:00.000     2023-12-09 23:59:59.999

Por que preciso dessa entrada de data? - Quero a - 1 yearentrada de data atual com a data de início do mês SOMENTE para a primeira entrada . A current - 1 yeardata deve ser a data de término e sua data de início do mês deve ser a data de início da série.

Se a data atual for 2024-12-22, a saída deverá ser:

start_date                      end_date
--------------------------------------------------
2023-12-01 00:00:00.000     2023-12-22 23:59:59.999

Se a data atual for 2024-01-01, a saída deverá ser:

start_date                      end_date
--------------------------------------------------
2023-01-01 00:00:00.000     2023-01-01 23:59:59.999
postgresql

3 respostas

  1. Best Answer

    Amplie seu generate_series()alcance e use least()para mudar sua data final:
    demonstração em db<>fiddle

    select days::timestamp(0) AS start_date
      ,least( current_date+interval '-1 year +1 day -1ms'
             ,date_trunc('month',  days)::timestamp(0)
              + interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2023-06-19 09:54:23.000'::date)
                     ,current_date-interval '12 months'
                     ,'1 month') AS days
order by start_date desc;
    data_de_início data_final
    2023-12-01 00:00:00 2023-12- 09 23:59:59.999
    2023-11-01 00:00:00 2023-11-30 23:59:59.999
    2023-10-01 00:00:00 2023-10-31 23:59:59.999
    2023-09-01 00:00:00 2023-09-30 23:59:59.999
    2023-08-01 00:00:00 2023-08-31 23:59:59.999
    2023-07-01 00:00:00 2023-07-31 23:59:59.999
    2023-06-01 00:00:00 2023-06-30 23:59:59.999

    Use order bysomente na consulta externa, caso contrário o Postgres não garante que seguirá a ordem das subconsultas e CTEs.

    • 2

  2. Você precisa alterar apenas o intervalo e remover um mês, veja exemplos, para obter o conjunto completo de traças,

    Adicionando um cte de envio para pegar o último mês, que você precisa diferenciar entre o último mês e o resto

    With cte as  (    SELECT days 
    from generate_series('2023-03-19 09:54:23.000'
      ,now()::timestamp(0)-interval '1 year  -1 Month',  '1 month') days 
    order by days desc)
  , MAXmonth as (
  SELECT
  MAX(date_part('month', days)) max_month
  FROM CTE)
select date_trunc('month', days) start_date ,
  CASE WHEN max_month = date_part('month', days) 
  THEN 
  CURRENT_DATE 
     + 
    MAKE_INTERVAL(YEARS := EXTRACT(YEAR FROM days)::INTEGER - EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER 
   )
   + TIME '23:59:59.999'
  ELSE
  (date_trunc('month',  days::date)::timestamp(0) + interval '1 month -1 millisecond') END
  end_date
  FROM cte CROSS JOIN MAXmonth
    data_de_início data_final
    2023-12-01 00:00:00 2023-12-09 23:59:59.999
    2023-11-01 00:00:00 2023-11-30 23:59:59.999
    2023-10-01 00:00:00 2023-10-31 23:59:59.999
    2023-09-01 00:00:00 2023-09-30 23:59:59.999
    2023-08-01 00:00:00 2023-08-31 23:59:59.999
    2023-07-01 00:00:00 2023-07-31 23:59:59.999
    2023-06-01 00:00:00 2023-06-30 23:59:59.999
    2023-05-01 00:00:00 2023-05-31 23:59:59.999
    2023-04-01 00:00:00 2023-04-30 23:59:59.999
    2023-03-01 00:00:00 2023-03-31 23:59:59.999 
    SELECT 10

    violino

    • 1

  3. Dependendo do uso pretendido, você pode querer gerar tsrange (intervalo de registro de data e hora) em vez de datas separadas. (veja a demonstração )

    with cte(days) as 
     ( select *  
         from generate_series( date_trunc('month','2023-06-30 09:54:23.000'::timestamp)
                             , current_date-interval '1 year' 
                             ,'1 month'
                             ) 
     ) -- select * from cte
select tsrange(days::timestamp(0) 
            , least ((days+interval '1 month ')::timestamp(0)
                    , (current_date-interval '1 year - 1 day')::timestamp(0)
                    )       
            ,'[)')  start_end_dates
  from cte
 order by days desc;
    datas_de_início_término
    ["2023-12-01 00:00:00","2023-12-10 00:00:00")
    ["01/11/2023 00:00:00","01/12/2023 00:00:00")
    ["01/10/2023 00:00:00","01/11/2023 00:00:00")
    ["01/09/2023 00:00:00","01/10/2023 00:00:00")
    ["01/08/2023 00:00:00","01/09/2023 00:00:00")
    ["01/07/2023 00:00:00","01/08/2023 00:00:00")
    ["01/06/2023 00:00:00","01/07/2023 00:00:00")

    NOTA sobre INTERVALOS : Os intervalos podem incluir ou excluir o início ou o fim do intervalo. O acima cria tsrangepara incluir o início do intervalo e para excluir o fim do intervalo. Quando os valores são lidos, tenha em mente que para o fim do intervalo isso significa até, mas não incluindo esse valor. Portanto, a primeira linha acima significa todos os horários começando em "2023-12-01 00:00:00" até, mas não incluindo "2023-12-10 00:00:00"
    NOTA sobre a demonstração : A demonstração mostra o intervalo terminando 1 dia adicional dos valores esperados indicados. Isso ocorre porque ele está sendo executado no fuso horário UTC, pois essa é a configuração no servidor db<>fiddle . (Ele está localizado no Reino Unido). Onde a data já havia mudado para 2024-12-10.

    • 0

relate perguntas