Generate a series of dates in progress-db

progress-database

I am looking for a way in progress-db to generate a series of dates in the same way I would in PostgreSQL.

    select date::date 
    from generate_series(
                          '2021-01-07'::date
                         ,'2021-01-12'::date
                         ,'1 day'::interval
                         ) date;

Returns the following dataset in rows:

1/7/2021 , 1/8/2021, 1/9/2021, 1/10/2021, 1/11/2021, 1/12/2021

I looked in the documentation, but I am unable to find any generate_series keywords or anything like it.
Is this something that can be done in this RDBMS? If this is not possible, please let me know.

Best Answer

Disclaimer: I'm not familiar with progress-db.

generate_series() is a PostgreSQL, set-returning function. AFAIK, generate_series() is not natively available in any other DBMS. (Except maybe in DBMS that are derived from PostgreSQL: Redshift, Greenplum, etc.)

You can get the same effect from most DBMS, though. There are a lot of ways to do that; I don't know whether progress-db supports any of these.

  • Program a stored procedure.
  • Use a calendar table.
  • Use a common table expression (CTE). (Not supported)
  • And I'm sure there are other ways.

A calendar table seems like the "lowest common denominator". It works in all SQL DBMS. For whole days, 100 years takes less than 40k rows. A hundred years of whole minutes takes about 53 million rows. Your query, running against a calendar table of 100 years of minutes (53m rows, near worst case) boils down to this.

select distinct cal_minute::date as cal_date
from caltest
where cal_minute between '2021-01-07' and '2021-01-12'
order by cal_date;
----
cal_date
date
--
2021-01-07
2021-01-08
2021-01-09
2021-01-10
2021-01-11
2021-01-12

On my computer, which isn't anything special, it consistently runs in less than 10ms, and most of that time is sorting. If the order of rows doesn't matter--and it probably shouldn't--it runs in less than 3ms. (A wider range will take longer, of course.)

Against a 40k table of just dates, the equivalent of your query clocked in at about 0.1ms.

Related Question