1

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.

2

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.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.