Postgresql: using 'with clause' to iterate over a range of dates

Question

I have a database table that contains a start visdate and an end visdate. If a date is within this range the asset is marked available. Assets belong to a user. My query takes in a date range (start and end date). I need to return data so that for a date range it will query the database and return a count of assets for each day in the date range that assets are available.

I know there are a few examples, I was wondering if it's possible to just execute this as a query/common table expression rather than using a function or a temporary table. I'm also finding it quite complicated because the assets table does not contain one date which an asset is available on. I'm querying a range of dates against a visibility window. What is the best way to do this? Should I just do a separate query for each day in the date range I'm given?

Asset Table
StartvisDate Timestamp
EndvisDate   Timestamp
ID           int

User Table
ID

User & Asset Join table
UserID
AssetID


Date       | Number of Assets Available | User
11/11/14              5                   UK
12/11/14              6                   Greece
13/11/14              4                   America
14/11/14              0                   Italy

Community · Accepted Answer · 2017-05-23 10:25:20Z

3

You need to use a set returning function to generate the needed rows. See this related question:

SQL/Postgres datetime division / normalizing

Example query to get you started:

with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 15:25:00+00'::timestamptz, '2014-12-05 07:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
       count(data.id)
from data
join generate_series(
      date_trunc('day', data.start_date),
      date_trunc('day', data.end_date),
      '1 day'
      ) as days (d)
      on days.d >= date_trunc('day', data.start_date)
      and days.d <= date_trunc('day', data.end_date)
group by data.id

 id | count 
----+-------
  1 |     2
  2 |     1
(2 rows)

You'll want to convert it to using ranges instead, and adapt it to your own schema and data, but it's basically the same kind of query as the one you want.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Dec 10, 2014 at 12:35

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

4 Comments

Sarah92 Over a year ago

Thanks for your answer. I will look alot more into depth of the links you have returned, but will this query return the date within the date range that I have queried with that the number of assets are available?

Denis de Bernardy Over a year ago

I'm not getting the additional question. But I'm guessing that between this query, the one I highlighted in the related question, and the documentation I linked to you'll have everything you need to adapt it to your specific problem.

Sarah92 Over a year ago

basically what I meant was is this iterating over each day in a range of dates and returning me the day so I can associate it with a count? I know I probably just don't understand the syntax and will look at your links to clarify. Thanks for your help.

Denis de Bernardy Over a year ago

@Sarah92: the related question I linked is very similar to the one you just asked.

Collectives™ on Stack Overflow

Postgresql: using 'with clause' to iterate over a range of dates

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related