0

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

Michael Stibich
  • 1
  • join your list of start/stop events to your list of shift times ON whether the start OR stop time is BETWEEN the shift times. for events that start in one shift and end in another they will list twice, which is helpful. compare the event start time to the shift start, take the later of the two. compare the event end time to the shift end, take the earlier of the two. for events that have no downtime end record, sub in the current datetime – Caius Jard Feb 06 '19 at 15:47
  • Any such question should start by declaring the version of Postgres in use and table definition(s) for relevant tables - `CREATE TABLE` statements showing data types and constraints. And mention the rough number of rows to deal with. – Erwin Brandstetter Feb 07 '19 at 00:53
  • I checked the veriosn by doing select version(): "PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit" – Michael Stibich Feb 07 '19 at 13:45

0 Answers0