Re: Feature request: Improve allowed values for generate series
Title: Re: Feature request: Improve allowed values for generate series Hello David, I have a table with services, each service have a period. After which service is auto renewal Services also could be one-time. At this case its interval is '00:00:00' The renewal is calculated via generate_series, when interval '00:00:00' pass to that function query died =( Generate dates for one time service: test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' ); generate_series 2020-11-09 00:00:00+02 (1 row) Generate dates for auto-renew service: test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' ); generate_series 2020-11-09 00:00:00+02 2020-11-10 00:00:00+02 (2 rows) So it is useful in my case. Also behavior is not surprising. Wednesday, November 11, 2020, 9:17:28 PM, you wrote: On Wed, Nov 11, 2020 at 12:12 PM Eugen Konkov <kes-...@yandex.ru> wrote: > So I feature request to allow zero size step for cases when start point is equest to finish > What do you think? hm probably with step 0 we always should generate series of one value and exit, despite on finish value. Because with step 0 we always stay at current position, so there is always should be just one value. How is this better than writing "VALUES (start date)"? David J. -- Best regards, Eugen Konkov
Re: Feature request: Improve allowed values for generate series
On Wed, Nov 11, 2020 at 7:54 PM Eugen Konkov wrote: > Hello David, > > I have a table with services, each service have a period. After which > service is auto renewal > > Services also could be one-time. At this case its interval is '00:00:00' > In which case the concept of interval is undefined - there is no meaningful "second date" here, just the one expiration date - yet you are choosing to keep it in order to introduce an artificial similarity between one-time service and auto-renewal service. This use case isn't convincing for me. Writing the one-time service query without generate_series leaves out extraneous stuff that isn't important, which I would recommend even if generate_series were to work as described. If you are going to introduce code-specific stuff to make this work just write: SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '1000 years' ); It is just as much a construction of code as the other. David J.
Re: Feature request: Improve allowed values for generate series
On Wed, Nov 11, 2020 at 12:12 PM Eugen Konkov wrote: > > > So I feature request to allow zero size step for cases when > start point is equest to finish > > > What do you think? > > > > hm probably with step 0 we always should generate series of one > value and exit, despite on finish value. > Because with step 0 we always stay at current position, so there is > always should be just one value. > > How is this better than writing "VALUES (start date)"? David J.
Re: Feature request: Improve allowed values for generate series
Hello Eugen, Wednesday, November 11, 2020, 8:50:59 PM, you wrote: > Hello Pgsql-hackers, > Seems I fall into corner case: test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' ); > ERROR: step size cannot equal zero > But: test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' ); > generate_series > > 2020-11-09 00:00:00+02 > 2020-11-10 00:00:00+02 > (2 rows) > Here we start at 2020-11-09, add interval of one day and finish at > 2020-11-10 > Done! series is generated. > In first case I expect that I start at 2020-11-09, add interval of zero > and finish at 2020-11-09 > Everything is consistent. test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' ); > generate_series > > 2020-11-09 00:00:00+02 > (1 row) > So I feature request to allow zero size step for cases when start > point is equest to finish > What do you think? hm probably with step 0 we always should generate series of one value and exit, despite on finish value. Because with step 0 we always stay at current position, so there is always should be just one value. -- Best regards, Eugen Konkov
Re: Feature request: Improve allowed values for generate series
On Wed, Nov 11, 2020 at 11:59 AM Eugen Konkov wrote: > So I feature request to allow zero size step for cases when start > point is equest to finish > > What do you think? > I don't see how this is useful. If they are equal and you use a non-zero step you get back the one record you are looking for anyway, plus the non-zero step allows them to be unequal. If zero step is allowed it is only useful for when they are equal, being undefined when they are unequal. David J.
Re: Feature request: Improve allowed values for generate series
st 11. 11. 2020 v 19:59 odesílatel Eugen Konkov napsal: > Hello Pgsql-hackers, > > Seems I fall into corner case: > > test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL > '00:00:00' ); > ERROR: step size cannot equal zero > > But: > test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL > '1 day' ); > generate_series > > 2020-11-09 00:00:00+02 > 2020-11-10 00:00:00+02 > (2 rows) > > Here we start at 2020-11-09, add interval of one day and finish at > 2020-11-10 > Done! series is generated. > > In first case I expect that I start at 2020-11-09, add interval of > zero and finish at 2020-11-09 > Everything is consistent. > > test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL > '00:00:00' ); > generate_series > > 2020-11-09 00:00:00+02 > (1 row) > > > So I feature request to allow zero size step for cases when start > point is equest to finish > > What do you think? > What is the real use case? Current implementation is very simple - increment should not be zero, and then we know so there is no infinity cycle. Regards Pavel > > -- > Best regards, > Eugen Konkov > > > >
Feature request: Improve allowed values for generate series
Hello Pgsql-hackers, Seems I fall into corner case: test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' ); ERROR: step size cannot equal zero But: test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' ); generate_series 2020-11-09 00:00:00+02 2020-11-10 00:00:00+02 (2 rows) Here we start at 2020-11-09, add interval of one day and finish at 2020-11-10 Done! series is generated. In first case I expect that I start at 2020-11-09, add interval of zero and finish at 2020-11-09 Everything is consistent. test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' ); generate_series 2020-11-09 00:00:00+02 (1 row) So I feature request to allow zero size step for cases when start point is equest to finish What do you think? -- Best regards, Eugen Konkov
