代码之家 › 专栏 › 技术社区 › Lin Du Correcter

如何在PostgreSQL中获取唯一的日期范围?

postgresql

Lin Du Correcter · 技术社区 · 7 年前

       start_dte        |        end_dte
------------------------+------------------------
 2018-08-01 00:00:00+00 | 2018-08-05 00:00:00+00
 2018-08-03 00:00:00+00 | 2018-08-05 00:00:00+00
 2018-08-09 00:00:00+00 | 2018-08-10 00:00:00+00
(3 rows)

我想得到这样的结果:

  [
    '2018-08-01',
    '2018-08-02',
    '2018-08-03',
    '2018-08-04',
    '2018-08-05',
    '2018-08-09',
    '2018-08-10',
  ]

我如何使用 SQL

3 回复 | 直到 7 年前

dwir182 7 年前

这可能是解决方案:

select 
  distinct generate_series(start_date, end_date, '1 day'::interval)::date 
from 
  my_table
order by 
  generate_series asc;

输出:

      generate_series  
 ------------------------  
"2018-08-01"  
"2018-08-02"  
"2018-08-03"  
"2018-08-04"  
"2018-08-05"  
"2018-08-09"  
"2018-08-10"

Lin Du Correcter 7 年前

我找到了一种方法,但不知道这是否是最好的方法。

select 
  distinct generate_series 
from 
  results, 
  generate_series(results.start_dte, results.end_dte, '1 day'::interval) 
order by generate_series asc;

输出:

    generate_series
------------------------
 2018-08-01 00:00:00+00
 2018-08-02 00:00:00+00
 2018-08-03 00:00:00+00
 2018-08-04 00:00:00+00
 2018-08-05 00:00:00+00
 2018-08-09 00:00:00+00
 2018-08-10 00:00:00+00
(7 rows)

e_i_pi 7 年前

generate_series ,然后从该系列中选择主表上开始日期和结束日期之间的所有值:

CREATE TABLE my_data (
  start_date DATE,
  end_date DATE
 );
 INSERT INTO my_data (start_date, end_date) VALUES
 ('2018-08-01 00:00:00+00', '2018-08-05 00:00:00+00'),
 ('2018-08-03 00:00:00+00', '2018-08-05 00:00:00+00'),
 ('2018-08-09 00:00:00+00', '2018-08-10 00:00:00+00');

SELECT DISTINCT all_dates.dates
FROM (
  SELECT dates.d AS dates
  FROM generate_series(
    (SELECT MIN(start_date) FROM my_data),
    (SELECT MAX(end_date) FROM my_data),
    '1 day'::interval
  ) AS dates(d)
) AS all_dates
INNER JOIN my_data
    ON all_dates.dates BETWEEN my_data.start_date AND my_data.end_date
ORDER BY all_dates.dates;

Here's the fiddle