Postgres Generate_Series return wrong number of results

Question

I would like to generate a series of timestamps using Postgres's generate_series(), but it return the wrong number of results:

select generate_series(
                 now() - interval '1 year',
                 now(),
                 interval '1 year' / 365
                     );

I would expect 365 results, but it returns 371 results.

Each interval in the results represents a little less than a day, but I can't figure out why.

2017-10-21 19:21:01.355108
2017-10-22 19:01:17.761508
2017-10-23 18:41:34.167908
2017-10-24 18:21:50.574308
2017-10-25 18:02:06.980708
2017-10-26 17:42:23.387108

Grzegorz Grabek · Accepted Answer · 2018-10-21 21:01:47Z

3

Interval '1 year' is not equal to 365 days that's why you receive more than 365 rows. I assume that you want to have series of datestamp from 1 year before to now for each day>

select generate_series(
             now() - interval '1 year' ,
             now(),
             interval '1 day'
                );

You will receive 366 rows, not 365 as you start exactly 1 year before so it will be 366 timestamps (1 Year before is your starting date of series +365 [or 366 if the year will be leap year] next days)

If you want it without first timestamp then add "interval '1 day'" to the first argument.

answered Oct 21, 2018 at 21:01

Grzegorz Grabek

1,0007 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Generate_Series return wrong number of results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related