postgres select all dates from interval

Question

How to with Postgres I Could retrieve all dates using parameters 'from' and 'until'

example:

select date from 'maybe a system table' where date >= :from and date <= :to

then te result is for (02-01-2015 and 05-01-2015)

 date
 ------------
 02-01-2015
 03-01-2015
 04-01-2015
 05-01-2015

What is the Best way to do this with Postgres, I know how to do with Oracle, but I need change my Database?? I Want to do a join with a table that does not have register with all dates and my report needs all days in one days interval

Regards

Gordon Linoff · Accepted Answer · 2015-01-29 23:13:31Z

4

In Postgres, you can use generate_series(). Here is an example for the days in January, 2015:

select generate_series('2015-01-01'::timestamp, '2015-01-31'::timestamp,
                       '1 day')::date

answered Jan 29, 2015 at 23:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

jrey Over a year ago

thanks Gordon, works excellent. I added a Cross Join with generate_series to Retrieve records for all dates.

Collectives™ on Stack Overflow

postgres select all dates from interval

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related