Add generate_series(date, date) and generate_series(date, date, integer)

From: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add generate_series(date, date) and generate_series(date, date, integer)
Date: 2016-01-25 06:00:15
Message-ID: CADkLM=dzw0Pvdqp5yWKxMd+VmNkAMhG=4ku7GnCZxebWnzmz3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This patch addresses a personal need: nearly every time I use
generate_series for timestamps, I end up casting the result into date or
the ISO string thereof. Like such:

SELECT d.dt::date as dt
FROM generate_series('2015-01-01'::date,
'2016-01-04'::date,
interval '1 day') AS d(dt);

That's less than elegant.

With this patch, we can do this:

SELECT d.date_val FROM
generate_series('1991-09-24'::date,'1991-10-01'::date) as d(date_val);
date_val
------------
1991-09-24
1991-09-25
1991-09-26
1991-09-27
1991-09-28
1991-09-29
1991-09-30
1991-10-01
(8 rows)

SELECT d.date_val FROM
generate_series('1991-09-24'::date,'1991-10-01'::date,7) as d(date_val);
date_val
------------
1991-09-24
1991-10-01
(2 rows)

SELECT d.date_val FROM
generate_series('1999-12-31'::date,'1999-12-29'::date,-1) as d(date_val);
date_val
------------
1999-12-31
1999-12-30
1999-12-29
(3 rows)

One thing I discovered in doing this patch is that if you do a timestamp
generate_series involving infinity....it tries to do it. I didn't wait to
see if it finished.

For the date series, I put in checks to return an empty set:

SELECT d.date_val FROM
generate_series('-infinity'::date,'1999-12-29'::date) as d(date_val);
date_val
----------
(0 rows)

SELECT d.date_val FROM generate_series('1991-09-24'::date,'infinity'::date)
as d(date_val);
date_val
----------
(0 rows)

Notes:
- I borrowed the int4 implementation's check for step-size of 0 for POLA
reasons. However, it occurred to me that the function might be leakproof if
the behavior where changed to instead return an empty set. I'm not sure
that leakproof is a goal in and of itself.

First attempt at this patch attached. The examples above are copied from
the new test cases.

Attachment Content-Type Size
0001-add_generate_series_date.diff text/plain 8.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2016-01-25 06:12:07 Re: Add generate_series(date, date) and generate_series(date, date, integer)
Previous Message Dilip Kumar 2016-01-25 05:09:06 Re: Patch: fix lock contention for HASHHDR.mutex