Re: Strange behavior in generate_series(date, date, interval) with DST
| От | Francisco Olarte |
|---|---|
| Тема | Re: Strange behavior in generate_series(date, date, interval) with DST |
| Дата | |
| Msg-id | CA+bJJbz13Jf7XHMZU3ugWZVbdZE1CTY=DetjmYw1cVqo_A_EUw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Strange behavior in generate_series(date, date, interval) with DST (Sérgio Saquetim <sergiosaquetim@gmail.com>) |
| Список | pgsql-general |
Hi Sérgio:
On Sun, Dec 7, 2014 at 9:11 PM, Sérgio Saquetim <sergiosaquetim@gmail.com> wrote:
RegardsI'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)
=> 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)
=> 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)
Francisco Olarte.
В списке pgsql-general по дате отправления: