Re: Strange behavior in generate_series(date, date, interval) with DST

[Francisco Olarte <folarte@xxxxxxxxxxxxxx>]

Hi Sérgio:

On Sun, Dec 7, 2014 at 9:11 PM, Sérgio Saquetim <sergiosaquetim@xxxxxxxxx> wrote:

I've noticed a strange behavior in the generate_series functions.

I'm trying to get all days between a start and an end date including the bounds. So naturally I've tried something like the query below

​. 

As both your examples and previous responses highlight your problem is that generate_series is not defined for dates, I'll avoid further comments on this. As you are using dates and your working example is using dates I would suggest rewriting your query around to avoid implicits cast problems. Date difference is integer, generate_series is defined on them, so:


=> select generate_series(0,'2014-10-20'::date - '2014-10-10'::date);
 generate_series
-----------------
               0
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(11 rows)

cdrs=> select '2014-10-10'::date+ generate_series(0,'2014-10-20'::date - '2014-10-10'::date);
  ?column? 
------------
 2014-10-10
 2014-10-11
 2014-10-12
 2014-10-13
 2014-10-14
 2014-10-15
 2014-10-16
 2014-10-17
 2014-10-18
 2014-10-19
 2014-10-20
(11 rows)

Or even

=> select ini + generate_series(0, fin-ini) from (select '2014-10-10'::date as ini, '2014-10-20'::date as fin) data ;
  ?column? 
------------
 2014-10-10
 2014-10-11
 2014-10-12
 2014-10-13
 2014-10-14
 2014-10-15
 2014-10-16
 2014-10-17
 2014-10-18
 2014-10-19
 2014-10-20
(11 rows)

Regards

   Francisco Olarte.