CODEWITHSUNDEEP
postgresqljoinview
user6167852
user6167852

Reputation: 35

Use JOIN to multiply rows

I'm writing this to share with this community the PRO and CONS of the solution that I have implemented as prototype in PostgreSQL Database.

Considering to have table (A) where for each row we have DateTime (h:00) and Value (int) as below:

10:00  50
11:00  40
12:00  85

I need to transform those value in table where all minutes are present (as example)

10:00  50
10:01  50
...
10:59  50
11:00  40
11:01  40
...
11:59  40
12:00  85

My idea is to right join table (A) with table (B) where all increments minutes (0-59) are present in order to develop a join and adding DateTime (h:00) with Table (B) minutes on other to get a VIEW with N x M all rows with values. Technically all is working but I would like to understand if this could be a solution considering also the rows trend of increasing.

Best Regards Al

Upvotes: 1

Views: 106

Answers (1)

user330315
user330315

Reputation:

You can use generate_series() for that:

select g.dt::time, t.*
from the_table t
  cross join generate_series(t.the_column, t.the_column + interval '1 hour', interval '1 minute') as g(dt)
order by g.dt;

Where the_column is the name of the column containing the "date time" (although your example seems to indicate that it only contains a time)

Upvotes: 1

Related Questions