Page 1 of 1

Strange results with generate_series

PostPosted: Mon Mar 04, 2019 11:23 am
by jdbruijn
Can someone explain to me why I have different responses when I run the following 2 queries in pgAdmin.
SELECT dd FROM generate_series ( '2018-10-01T10:00:00.000Z', '2018-12-31T10:59:59.000Z', '1 week'::interval) dd;
SELECT dd FROM generate_series ( '2018-10-01T10:00:00.000Z', '2018-12-31T11:59:59.000Z', '1 week'::interval) dd;

The first will return 13 rows, the second will return 14 rows.

And Servoy gives the same results as pgAdmin, so is there a reason why the last date is only included when the second timestamp is above 11:00?
I have also tested these queries in another query tool (Datagrip from Jetbrains) and there the breakpoint seems to be at 10:00.

Re: Strange results with generate_series

PostPosted: Mon Mar 04, 2019 12:02 pm
by mboegem
Hi Jos,

The hour difference 'sounds' like some timezone offset issue.
But it's far more interesting on what result you're after: the 13 row, or 14 row result.

The reason why just not 'any' time on the 31st of December will return you a result, is that counting from the 1st of October not always a full day has passed.
If you're interested in days, I would make sure PG is ignoring the time portion of the dates you're inserting.
Like this:
Code: Select all
SELECT dd FROM generate_series ( date_trunc('day', '2018-10-01T10:00:00.000Z'::timestamp), date_trunc('day', '2018-12-31T10:59:59.000Z'::timestamp), '1 week'::interval) dd

You won't need the '::timestamp' part if you're passing the dates as date object to the query.

Hope this helps

Re: Strange results with generate_series

PostPosted: Mon Mar 04, 2019 12:19 pm
by jdbruijn
Hi Marc,
The 11am time seems strange if it was a timezone issue.
I am after the 14 rows result. As I see it if the date is included in the range, why is it not in the results.

For now forcing it to use only the date portion of the timestamp is working for me, so I will go with that:
SELECT dd FROM generate_series (date '2018-10-01T10:00:00.000Z', '2018-12-31T09:59:59.000Z', '1 week'::interval) dd