1

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

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

and

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?

  • 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.