Question 115047 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate time between steps in PostgreSQL

I have an audit table that tracks the steps in a process and I need to track the time for each step. It used to be stored in MS SQL Server but is now stored in PostgreSQL. I have the query from SQL server and have not been successful at converting it. Here is the MS SQL working example: http://www.sqlfiddle.com/#!18/1b423/1

Here are the rules:

  • The steps are not required to be sequential, so step 1 can happen
    after step 5.

  • The records for an order are not stored sequentially by step
    or order, but rather are intermixed with other orders based upon
    the Time Entered.

  • The sample data being ordered by Order Number then New is NOT
    normal and cannot be depended upon.

  • Each step can be repeated for any given order, if repeated for an
    order, then sum the times by step.

  • The starting step record is always null in the Old column

  • Starting step is calculated as the time difference between
    when it is in the New column and when it is the value in the Old
    column for a given order.

  • For the steps that the order never came out of, the time is computed
    up to the present moment

  • A step can be repeated many times and am only looking for the total
    time spent in each step.

I cannot get the date difference to sum or handle the null old status value for the first step. I get various forms of this error when running the following sql.

ERROR: function isnull(timestamp without time zone, timestamp with
time zone) does not exist LINE 4: sum(a1.timeentered –
isnull(a2.timeentered,now())) as "tota…
^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

SELECT
    a1.ordernumber, 
    a1."new" AS "Step",
    sum(a1.timeentered - isnull(a2.timeentered,now())) as "total time"
FROM
    audittrail AS a1
    LEFT JOIN
    audittrail AS a2
    ON 
        a1."new" = a2."old" AND
        a1.ordernumber = a2.ordernumber
GROUP BY
    a1.ordernumber, 
    a1."new"
ORDER BY
    a1.ordernumber ASC

Here is the sample data as well as a link to a sample online: http://www.sqlfiddle.com/#!17/e6fd5a

 Old      New           Time Entered        Order Number
 NULL     Step 1        4/30/12 10:43       1C2014A
 Step 1   Step 2        5/2/12 10:17        1C2014A
 Step 2   Step 3        5/2/12 10:28        1C2014A
 Step 3   Step 4        5/2/12 11:14        1C2014A
 Step 4   Step 5        5/2/12 11:19        1C2014A
 Step 5   Step 9        5/3/12 11:23        1C2014A
 NULL     Step 1        5/18/12 15:49       1C2014B
 Step 1   Step 2        5/21/12 9:21        1C2014B
 Step 2   Step 3        5/21/12 9:34        1C2014B
 Step 3   Step 4        5/21/12 10:08       1C2014B
 Step 4   Step 5        5/21/12 10:09       1C2014B
 Step 5   Step 6        5/21/12 16:27       1C2014B
 Step 6   Step 9        5/21/12 18:07       1C2014B
 NULL     Step 1        6/12/12 10:28       1C2014C
 Step 1   Step 2        6/13/12 8:36        1C2014C
 Step 2   Step 3        6/13/12 9:05        1C2014C
 Step 3   Step 4        6/13/12 10:28       1C2014C
 Step 4   Step 6        6/13/12 10:50       1C2014C
 Step 6   Step 8        6/13/12 12:14       1C2014C
 Step 8   Step 4        6/13/12 15:13       1C2014C
 Step 4   Step 5        6/13/12 15:23       1C2014C
 Step 5   Step 8        6/13/12 15:30       1C2014C
 Step 8   Step 9        6/18/12 14:04       1C2014C

This is the expected result:

| OrderNumber |   Step | Total Time in Step (seconds) |
|-------------|--------|------------------------------|
|     1C2014A | Step 1 |                       171240 |
|     1C2014A | Step 2 |                          660 |
|     1C2014A | Step 3 |                         2760 |
|     1C2014A | Step 4 |                          300 |
|     1C2014A | Step 5 |                        86640 |
|     1C2014A | Step 9 |                    324902599 |
|     1C2014B | Step 1 |                       235920 |
|     1C2014B | Step 2 |                          780 |
|     1C2014B | Step 3 |                         2040 |
|     1C2014B | Step 4 |                           60 |
|     1C2014B | Step 5 |                        22680 |
|     1C2014B | Step 6 |                         6000 |
|     1C2014B | Step 9 |                    323323159 |
|     1C2014C | Step 1 |                        79680 |
|     1C2014C | Step 2 |                         1740 |
|     1C2014C | Step 3 |                         4980 |
|     1C2014C | Step 4 |                         3840 |
|     1C2014C | Step 5 |                          420 |
|     1C2014C | Step 6 |                         5040 |
|     1C2014C | Step 8 |                       875160 |
|     1C2014C | Step 9 |                    320918539 |
Tags:

2

Answers


  1. 0

    This turned out to be harder than I thought. This is the full query I used. It doesn’t have the subsitution for ISNULL function, but it gets most of the way there. I used the extract function from Date/Time Functions. Specifically, to get everything in seconds, I used extract(epoch from ...

    SELECT
    a1.ordernumber, 
    a1."new" AS "Step",
    sum(extract(epoch from a2.timeentered) - 
    extract(epoch from a1.timeentered)) as total_time
FROM
    audittrail AS a1
    LEFT JOIN
    audittrail AS a2
    ON 
        a1.new = a2.old AND
        a1.ordernumber = a2.ordernumber 
GROUP BY
    a1.ordernumber,
    a1.new
ORDER BY a1.ordernumber ASC

    which gives

    ordernumber Step total_time
    1C2014A Step 1 171240
    1C2014A Step 2 660
    1C2014A Step 3 2760
    1C2014A Step 4 300
    1C2014A Step 5 86640
    1C2014A Step 9 NULL
    1C2014B Step 1 235920
    1C2014B Step 2 780
    1C2014B Step 3 2040
    1C2014B Step 4 60
    1C2014B Step 5 22680
    1C2014B Step 6 6000
    1C2014B Step 9 NULL
    1C2014C Step 1 79680
    1C2014C Step 2 1740
    1C2014C Step 3 4980
    1C2014C Step 4 3840
    1C2014C Step 5 420
    1C2014C Step 6 5040
    1C2014C Step 8 875160
    1C2014C Step 9 NULL

    To me this calculation looks wrong. For me, it makes more sense (for example) for the entry for Step 3/Order 1C2014A, the total_time should be 11 minutes or 660 seconds. To achieve this, swap old and new in the join and swap a1 and a2 in the sum(part(epoch….) to become

    SELECT
    a1.ordernumber, 
    a1.new AS Step,
    sum(extract(epoch from a1.timeentered) - 
    extract(epoch from a2.timeentered)) as total_time
FROM
    audittrail AS a1
    LEFT JOIN
    audittrail AS a2
    ON 
        a1.old = a2.new AND
        a1.ordernumber = a2.ordernumber 
GROUP BY
    a1.ordernumber,
    a1.new
ORDER BY a1.ordernumber ASC
    Login or Signup to reply.
  2. 0

    Just replace isnull and datediff with equivalent PostgreSQL expressions in the second query line.

    select a1.OrderNumber as "OrderNumber", a1.New as "Step", 
    extract('epoch' from 
            sum(coalesce(a2.TimeEntered, now()) - a1.TimeEntered))::integer 
    as "Total Time in Step (seconds)"
from AuditTrail a1
left join AuditTrail a2
  on a1.New = a2.Old 
  and a1.OrderNumber = a2.OrderNumber
group by a1.OrderNumber, a1.New
order by a1.OrderNumber;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search