CODEWITHSUNDEEP
sqlpostgresqldowntime
Michael Stibich
Michael Stibich

Reputation: 1

calculate total downtime based on shift times

I am trying to write a query to give me the total amount of downtime minutes per shift. I have a calendar table that lists the start and end time for each shift for the entire year.

Biggest difficulties I have been facing is how to calculate the amount of minutes of downtime in a shift when the downtime is spread across multiple shifts. Another problem is how to calculate the amount of downtime when it is still going on (there is no stop record for the latest start record).

In the Postgres database, downtime is recorded by listing the machine id that created it, the timestamp of the creation, and what event it is (startdowntime or stopdowntime). 

pue_produnit_id     pue_tcreation       pue_eventkind
17          2018-12-13 04:45:07     StartDownTime
17          2018-12-14 10:36:35     StopDownTime
18          2018-12-14 10:40:11     StartDownTime
18          2019-01-04 10:46:34     StopDownTime

In the calendar table for shifts it is recorded by listing the id of the type of shift (1, 2, 3), the start of the shift, and the end of the shift.

cae_entrytype_id    cae_from        cae_to
1           2019-01-01 06:30:00     2019-01-01 14:30:00
2           2019-01-01 14:30:00     2019-01-01 22:30:00
3           2019-01-01 22:30:00     2019-01-02 06:30:00

I have been able to write a query to show me this using lead to find the stop downtime and using between to compare the downtime timestamp to the shift calendar:

Date        Shift_number        Machine         ActivityDate        startdttime         stopdttime              Duration_Minutes
2019-02-05      1               17          2019-02-05      2019-02-05 14:19:39         2019-02-05 14:23:46         4

What I have isn't necessarily what I need though. The shift number is taken from when the startdowntime occurred and it doesn't split it up when the stopdowntime is in another shift. I would like to be able to see that from the shift calendar table for one record, how many minutes of downtime there were. This will need to be done for every machine. For example:

Machine     Shift       Date        downtime_minutes
17          1       1/2/2019        26
18          1       1/2/2019        32
17          2       1/2/2019        0
18          2       1/2/2019        100

If figuring out the downtime that is still occuring (no latest stopdowntime record) then that is fine for now. Also depending on results I may be able to work it out with the lead function and adding a current_timestamp to its default

Upvotes: 0

Views: 357

Answers (0)

Related Questions