This question already has an answer here:

I am using Postgres. I have 2 tables. PS : week starts from monday.

Time (id (bigInt), startOfWeek (Date), startOfMonth (Date), startOfQuarter(Date), day (Date) ...)


User (id (bigInt), timeId (FK --> Time), total (Int)).

Given 2 dates (start and end), a dimension (day / week /month / quarter), I need to find out the total for each day / week / month / quarter.

Example :

Time (id (1), startOfWeek ('07-13-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-13-2015') ...)

Time (id (2), startOfWeek ('07-13-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-14-2015') ...)

//for 22nd july.
Time (id (3), startOfWeek ('07-20-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-22-2015') ...)

User (id (11), timeId (1), total (5)).
User (id (12), timeId (2), total (10)).

Given 2 date values start and end, and dimension 'd' (W /M /Q etc) how can I write a query to fetch values from User table like :

'07-13-2015' 100
'07-20-2015' 122 etc

The problem is the startOfWeek field will be same for 7 days. I need to get the value of only the first row in User table where startOfWeek changes.

I could do it using multiple queries :

  1. get all startOfWeeks from Time table

  2. for each startOfWeek, query User table and do first(..)

Is there a better / more efficient solution?

marked as duplicate by Erwin Brandstetter postgresql Jul 14 '15 at 15:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    You can use generate_series() to generate the dates you want and then join it with the 2 tables. In the first table (and please add the names of the tables in the question!) is there one row for each date? – ypercubeᵀᴹ Jul 13 '15 at 8:57
  • @ypercube - The table names are User and Time :P. Yes, in the TIme table, there is one row for each date (but week, month and quarter start dates could be same). You mean I should generate values and then do a join? – TheLostMind Jul 13 '15 at 9:05
  • As an aside, it's not a very wise idea to name tables with keywords. – dezso Jul 13 '15 at 9:11
  • @dezso - They are not real names :).. They are placeholder names. – TheLostMind Jul 13 '15 at 9:14
  • We prefer real names since those are often part of the problem. And unsuspecting readers might get confused with syntactically invalid identifiers. We also like to see the query you tired, even if it's not working. And your version of Postgres, obviously. – Erwin Brandstetter Jul 13 '15 at 14:03

Browse other questions tagged or ask your own question.