How do I generate a date series in PostgreSQL?

All we need is an easy explanation of the problem, so here it is.

If you’re looking to generate a time series, see this question

Let’s say that I want to generate a series of dates between two dates.
I see the function generate_series provides only

Function                                    Argument Type                         Return Type                                                               Description
generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step

So how would I go about doing this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use generate_series for this, but be sure to explicitly cast the arguments to “timestamp without time zone” otherwise they will default to “timestamp with timezone”. PostgreSQL overloads generate_series for both inputs.

Problems with timestamp with timezone

You can see the drawback here.

SET timezone = 'America/Santiago';
SELECT generate_series(date '2016-08-15', date '2016-08-15', '1 day');    
SELECT generate_series(date '2016-08-14', date '2016-08-15', '1 day');

Both of the above return the same amount of days. You can see it again here.

SET timezone = 'America/Sao_Paulo';
SELECT generate_series(date '2016-10-16', date '2016-10-17', '1 day');
SELECT generate_series(date '2016-10-17', date '2016-10-17', '1 day');

The above shows two ranges of one day.

The reason for this behavior is that these timezones have their “DST boundary at midnight, rather than a more sensible time in the small hours”

So what does it look like to “do it right”,

SELECT generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
);

Now you can cast to date..

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
) AS gs(d);

This question and answer was inspired by a conversation with RhodiumToad on IRC (irc://irc.freenode.net/#postgresql). He altered me to this issue and provided the solution.

Update: two potential fixes

Option 1: generate_series(date,date,interval)

Playing around, I discovered I could perhaps save the need to explicitly cast to timestamp without time zone by overload for generate_series(date,date,interval)

Here is my function,

CREATE FUNCTION generate_series( t1 date, t2 date, i interval )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Now I can rerun the test case above and it’s no longer fishy. These two both return the same thing,

SET timezone = 'America/Santiago';
SELECT d::date
FROM generate_series(date '2016-08-15', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-15',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

As do these two,

SELECT d::date
FROM generate_series(date '2016-08-14', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-14',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

Option 2: generate_series(date,date,int)

Another option is to create a new function generate_series(date,date,int) however you can’t have both for the reasons mentioned here. So pick one of these,

generate_series(date,date,interval)
generate_series(date,date,int)

If you want the second option, try this one:

CREATE FUNCTION generate_series( t1 date, t2 date, i int )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i * interval '1 day'
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Caveats

With review on irc, there are some problems with those ideas,

< johto> generate_series(date, date, unknown) already works today. when you don’t break it outright with the int version (e.g. generate_series(date, date, '1 day')) you change the return type from timestamptz to date. (date, date, interval) would break fewer cases, but you’d still change the output type. (it’s also not obvious what should happen with (date, date, '1 hour') that currently “works” just fine)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply