如何獲取屬於時間戳範圍的所有日期

我有一個tsrange作為輸入。例如：

tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)

我想生成屬於這個範圍的所有日期。

我從以下可能幼稚的方法開始：

select generate_series(lower(r), upper(r), '1 day')::date
from   tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) r;

這似乎很好用，它給出了：

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

但是，也有處理不好的異常。以以下範圍為例：

tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp)

這是一個從2019-03-04 00:00 包容到2019-03-06 00:00 排斥的範圍。也就是說，日期2019-03-06不屬於這個範圍。

重複上述解決方案時，我得到以下結果：

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

如您所見，2019-03-06它在輸出中，而它不應該在輸出中。

我完全理解為什麼會這樣（我們在generate_series通話中要求它；它不知道這2019-03-06是我們範圍內的獨家產品）。

一個解決方案可能是：

select generate_series(
        date_trunc('day', lower(r)),
        case when upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
             else                               date_trunc('day', upper(r))
        end,
        '1 day'
      )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r;

現在我得到：

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
+-----------------+

這似乎工作正常。但是這個解決方案感覺很hackish。它還假設範圍的上限是獨占的（這是預設值）。如果上限包含在內，則原始解決方案很好。我們可以對此添加測試：

select generate_series(
        date_trunc('day', lower(r)),
        case when not(upper_inc(r)) and upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
             else                                                     date_trunc('day', upper(r))
        end,
        '1 day'
      )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp, '[]') r;

有更好的可能嗎？

如果將 移至generate_series()FROM 子句，則可以使用 WHERE 條件來限制行：

select t.dt::date
from  tsrange('2019-03-04 10:00'::timestamp, '2019-03-06 20:00'::timestamp) r, 
     generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case 
               when upper(r) = date_trunc('day', upper(r)) 
                    then upper(r)::date 
               else upper(r)::date + 1 
            end;

dt        
----------
2019-03-04
2019-03-05
2019-03-06

如果upper(r) 是在午夜，那么生成的日期( t.dt) 需要小於upper(r)。如果不是，則生成日期需要小於範圍上限後的第二天*。*

此查詢完全獨立於實際範圍內的小時。

select t.dt::date
from  tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r, 
     generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case 
               when upper(r) = date_trunc('day', upper(r)) 
                    then upper(r)::date 
               else upper(r)::date + 1 
            end;

dt        
----------
2019-03-04
2019-03-05

引用自：https://dba.stackexchange.com/questions/232729