Feature request: Improve allowed values for generate series

Started by Eugen Konkovabout 5 years ago7 messages

kes-kes@yandex.ru

about 5 years ago

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

Eugen Konkov

kes-kes@yandex.ru

about 5 years ago

In reply to: Eugen Konkov (#1)

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

Pavel Stehule

pavel.stehule@gmail.com

about 5 years ago

In reply to: Eugen Konkov (#1)

Re: Feature request: Improve allowed values for generate series

st 11. 11. 2020 v 19:59 odesílatel Eugen Konkov <kes-kes@yandex.ru> 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

Show quoted text

--
Best regards,
Eugen Konkov

David G. Johnston

david.g.johnston@gmail.com

about 5 years ago

In reply to: Eugen Konkov (#1)

Re: Feature request: Improve allowed values for generate series

On Wed, Nov 11, 2020 at 11:59 AM Eugen Konkov <kes-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?

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.

David G. Johnston

david.g.johnston@gmail.com

about 5 years ago

In reply to: Eugen Konkov (#2)

Re: Feature request: Improve allowed values for generate series

On Wed, Nov 11, 2020 at 12:12 PM Eugen Konkov <kes-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.

Eugen Konkov

kes-kes@yandex.ru

about 5 years ago

In reply to: David G. Johnston (#5)

Re: Feature request: Improve allowed values for generate series

<html><head><title>Re: Feature request: Improve allowed values for generate series</title>
</head>
<body>
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: 
 
<table style =" border-collapse: collapse;" cellSpacing = 2>
<tr>
<td width=2 bgcolor= #0000ff style="border: 1px solid black;"> 
</td>
<td style="border: 1px solid black;">On Wed, Nov 11, 2020 at 12:12 PM Eugen Konkov <<a style=" font-family:'tahoma'; font-size: 9pt;" href="mailto:kes-kes@yandex.ru">kes-kes@yandex.ru</a>> wrote: 
<table style =" border-collapse: collapse;" cellSpacing = 2>
<tr>
<td width=2 bgcolor= #3200ff style="border: 1px solid black;"> 
</td>
<td style="border: 1px solid black;"> 
> 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. 
</td>
</tr>
</table>
 
How is this better than writing "VALUES (start date)"? 
 
David J. 
</td>
</tr>
</table>
 
 
 
-- 
Best regards, 
Eugen Konkov</body></html>

David G. Johnston

david.g.johnston@gmail.com

about 5 years ago

In reply to: Eugen Konkov (#6)

Re: Feature request: Improve allowed values for generate series

On Wed, Nov 11, 2020 at 7:54 PM Eugen Konkov <kes-kes@yandex.ru> 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.