主页 / coding / 问题 / 79265819
Accepted
MAK
Asked: 2024-12-10 00:55:40 +0800 CST

生成给定日期之间的时间戳系列

  • 772

给定的最小和最大日期为：

最短日期：2023-06-19 09:54:23.000

最大日期：now()::timestamp(0)-interval '1 year'

预期输出：

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

我的尝试：

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;

以下是上述查询结果中缺失的日期。

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

为什么我需要这个日期条目？ - 我希望第一个条目包含当前日期- 1 year条目和月份开始日期。该日期应为结束日期，其月份开始日期应为系列的开始日期。current - 1 year

如果当前日期是2024-12-22则输出应该是：

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

如果当前日期是2024-01-01则输出应该是：

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

3 个回答

  1. Best Answer

    扩大generate_series()范围并用来least()切换最终日期：
    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;
    开始日期 结束日期
    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

    仅在外部查询中使用order by，否则 Postgres 不保证它遵循子查询和 CTE 的顺序。

    • 2

  2. 您只需更改间隔并删除一个月（参见示例）即可获得完整的 moth 集，

    添加发送 cte 来获取上个月，您需要区分上个月和其余月份

    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
    开始日期 结束日期
    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

    小提琴

    • 1

  3. 根据您的预期用途，您可能希望生成tsrange（时间戳范围）而不是单独的日期。（参见演示）

    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;
    开始_结束_日期
    [“2023-12-01 00:00:00”,“2023-12-10 00:00:00”)
    [“2023-11-01 00:00:00”,“2023-12-01 00:00:00”)
    [“2023-10-01 00:00:00”,“2023-11-01 00:00:00”)
    [“2023-09-01 00:00:00”,“2023-10-01 00:00:00”)
    [“2023-08-01 00:00:00”,“2023-09-01 00:00:00”)
    [“2023-07-01 00:00:00”,“2023-08-01 00:00:00”)
    [“2023-06-01 00:00:00”,“2023-07-01 00:00:00”)

    关于范围的说明：范围可以包括或排除范围开始或范围结束。以上创建了包括tsrange范围开始和排除范围结束的。读取值时请记住，对于范围结束，这意味着最多但不包括该值。因此，上面的第一行表示从“2023-12-01 00:00:00”开始的所有时间，直到但不包括“2023-12-10 00:00:00”
    关于演示的说明：演示显示范围结束时间比指示的预期值多 1 天。这是因为它在 UTC 时区运行，因为这是db<>fiddle服务器上的设置。（它位于英国）。日期已更改为 2024-12-10。

    • 0

相关问题