CODEWITHSUNDEEP
sqlpostgresql
Elijah Leis
Elijah Leis

Reputation: 415

Populating a table in PostgreSQL and logic formulating

I need some help in populating a table.

First I have an inventory table in which holds all of my data and is already populated. The primary key for this table is terminal_id

Second I have another table, a down_event table which holds all of the down event of each machine on the inventory table. It involves the column: terminal_id, event_descriptiondown_date,down_time, anddown_duration_sec` and is already populated.

The one I need to populate is the availability table that holds all of the available time of the machines on the inventory table. The columns are: terminal_id(primary key), availability_date(primary key), availability_time(primary key), duration_sec.

The catch is that each terminal_id needs to have a 1-hour interval in one day meaning 00:00:00 up to 23:59:59 for one terminal_id.

So for example:

    terminal_id       availability_time
   _____________     ___________________
    012345678          00:00:00
    012345678          01:00:00
    012345678          02:00:00
    012345678          03:00:00

So on and so forth until 23:00:00. I need help for doing it like that on the availability_table like in the picture below:

availability_table

Another catch is that the availability_date will come from the down_event table. In the down_event table each terminal_id has different down_date that will reflect on the availability table that's why in the picture 2019-09-01 keeps being repeated.

I tried populating first the terminal_id so I can try to UPDATE the remaining column but the problem is that I can't because it's a primary key on the availability table and won't repeat that's why I'm wondering how did it happen on the result in the picture.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If I understand correctly, the following query generates the results you want:

select td.terminal_id, td.down_date, (hh * interval '1 hour')::time
from (select distinct terminal_id, down_date
      from down_event_table
     ) td cross join
     generate_series(0, 23) gs(hh);

You can then insert these rows into your availability table.

Upvotes: 2

Related Questions