Dynamic Date Table

Question

I am creating a Data Model in PowerPivot and am wondering if there is anyway I can create a dynamic date table in SQL. I was able to create one in PowerQuery however there are some bugs in PowerQuery(read only connection) when a table is modified in PowerPivot. What I am looking for is to have a start date of 1/1/2013 (interval is days) and as each new year rolls around rows are added to the date table. Is there anyway to do this?

I am running Postgres

So far I came up with this,

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

But I want it to display all dates till end of the year.

Answer 1

The completely dynamic approach would be a query based on 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;

• Always use ISO 8601 format for dates and timestamps, which works irregardless of locale settings.

• A final cast to date (the_date::date), because the function returns timestamp (when fed timestamp arguments).

• The expression

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

  calculates the last day of the current year. Alternatively you could use EXTRACT (year FROM now())::text || '-12-31')::date, but that's slower.

You can wrap this into a custom "table-function" (a.k.a. set-returning function) that you can basically use as drop-in replacement for a table name in queries:

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;

Example:

SELECT * FROM f_dates_since_2013();

Going one step further, you could create a table or - more elegantly - a MATERIALIZED VIEW based on this function (or the underlying query directly):

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

Call:

SELECT * FROM my_dates;

All you have to do now is to schedule a yearly cron job that runs REFRESH MATERIALIZED VIEW at the start of each new year:

REFRESH MATERIALIZED VIEW my_dates;