动态日期表

Question

我正在PowerPivot中创建一个数据模型，我想知道是否有可以在SQL中创建动态日期表的方法。我能够在PowerQuery中创建一个，但是在PowerPivot中修改表时，PowerQuery（只读连接）中存在一些错误。我想要的是有一个2013年1月1日的开始日期（间隔为天），随着每一个新年的临近，行被添加到日期表中。有办法这样做吗？

我正在运行Postgres

到目前为止，我想出了这个，

SELECT * FROM dbo.fof_GetDates('1/1/2013', GETDATE())

但我希望它能显示到年底的所有日期。

Answer 1

完全动态方法将是基于generate_series()的查询：

SELECT the_date::date
FROM   generate_series('2013-01-01 0:0'::timestamp
                     , date_trunc('year', now()::timestamp)
                                    + interval '1 year - 1 day'
                     , interval '1 day') the_date;

• 日期和时间戳始终使用ISO 8601格式，这与区域设置无关。

• 最后强制转换为date（the_date::date），因为函数返回timestamp（当提供timestamp参数时）。

• 表达式

  date_trunc('year', now()::timestamp) + interval '1 year - 1 day'
  

  计算当前年度的最后一天。或者，您可以使用EXTRACT (year FROM now())::text || '-12-31')::date，但速度较慢。

您可以将其封装到自定义的"表函数"（也称为集返回函数）中，基本上可以用作查询中表名的插入替换：

CREATE OR REPLACE FUNCTION f_dates_since_2013()
  RETURNS SETOF date AS
$func$
SELECT the_date::date
FROM   generate_series('2013-01-01 0:0'::timestamp
                     , date_trunc('year', now()::timestamp)
                                    + interval '1 year - 1 day'
                     , interval '1 day') the_date;
$func$  LANGUAGE sql STABLE;

示例：

SELECT * FROM f_dates_since_2013();

更进一步，您可以基于此函数（或直接创建底层查询）创建一个表，或者更优雅地创建一个MATERIALIZED VIEW：

CREATE MATERIALIZED VIEW my_dates(the_date) AS
SELECT * FROM f_dates_since_2013();

呼叫：

SELECT * FROM my_dates;

您现在所要做的就是安排一个年度cron作业，该作业在每个新年开始时运行REFRESH MATERIALIZED VIEW：

REFRESH MATERIALIZED VIEW my_dates;