Strange results with generate_series

Questions and answers regarding general SQL and backend databases

Strange results with generate_series

Postby jdbruijn » Mon Mar 04, 2019 11:23 am

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.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 488
Joined: Sun Apr 11, 2010 6:34 pm

Re: Strange results with generate_series

Postby mboegem » Mon Mar 04, 2019 12:02 pm

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
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1573
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Strange results with generate_series

Postby jdbruijn » Mon Mar 04, 2019 12:19 pm

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
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 488
Joined: Sun Apr 11, 2010 6:34 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 1 guest

cron