3 Replies Latest reply on Mar 6, 2015 11:10 AM by Matt Coles

    Extract Duration from the PostGres HIST Tables

    Keith Helfrich

      Hello Friends,

       

      I'm having trouble getting the duration of an extract from the HIST tables of the postgres repo. 

       

      "Duration in Ms" is null, and historical_events does not offer a value for completed_at.

       

      Could you help me with identifying the joins to correctly determine the duration of an extract event from the hist* tables ?  So far, each attempt to trace back from hist* to the other repo tables which contain a completed_at has failed.

       

      Thanks!

      Keith Helfrich | Twitter

      Red Headed Step Data

        • 1. Re: Extract Duration from the PostGres HIST Tables
          Matt Coles

          You want the background_jobs table for extract data. This is the Custom SQL that I use for my views:

           

          SELECT
              tasks.id                                            AS "Job Id" ,
              tasks.job_name                                      AS "Job Name" ,
              tasks.progress                                      AS "Progress" ,
              tasks.finish_code                                   AS "Finish Code" ,
              tasks.priority                                      AS "Priority" ,
              tasks.notes                                         AS "Notes" ,
              tasks.created_at                                    AS "Created At" ,
              tasks.started_at                                    AS "Started At" ,
              tasks.completed_at                                  AS "Completed At",
              tasks.backgrounder_id                               AS "Backgrounder Id",
              tasks.created_on_worker                             AS "Created On Worker" ,
              tasks.processed_on_worker                           AS "Processed On Worker" ,
              current_timestamp                                   AS "Current Datetime" ,
              tasks.site_id                                       AS "Site Id" ,
              s.name                                              AS "Site Name" ,
              tasks.subtitle                                      AS "Item Type" ,
              tasks.item_id                                       AS "Item Id" ,
              tasks.item_name                                     AS "Item Name" ,
              tasks.repository_url                                AS "Item Repository Url" ,
              tasks.project_id                                    AS "Item Project Id" ,
              tasks.owner_id                                      AS "Item Owner Id" ,
              su_own.name                                         AS "Item Owner Name" ,
              p.name                                              AS "Project Name"
          FROM
              (
              SELECT
                  bj.id ,
                  bj.job_name ,
                  bj.progress ,
                  bj.finish_code ,
                  bj.priority ,
                  bj.notes ,
                  bj.created_at ,
                  bj.started_at ,
                  bj.completed_at ,
                  bj.site_id ,
                  bj.subtitle ,
                  bj.backgrounder_id ,
                  bj.created_on_worker ,
                  bj.processed_on_worker ,
                  COALESCE(d.repository_url, w.repository_url)        AS "repository_url" ,
                  COALESCE(d.project_id, w.project_id)                AS "project_id" ,
                  COALESCE(d.owner_id, w.owner_id)                    AS "owner_id" ,
                  COALESCE(dc.id, w.id)                               AS "item_id" ,
                  COALESCE(d.name, w.name)                            AS "item_name"
              FROM background_jobs bj
                  LEFT JOIN datasources d
                      ON bj.title = d.name
                          AND bj.subtitle = 'Data Source'
                  LEFT JOIN data_connections dc
                      ON d.id = dc.owner_id
                  AND dc.owner_type = 'Datasource'
                  LEFT JOIN workbooks w
                      ON bj.title = w.name
                          AND bj.subtitle = 'Workbook'
              ) AS tasks
              LEFT JOIN sites s
                  ON tasks.site_id = s.id
              LEFT JOIN users u_own
                  ON tasks.owner_id = u_own.id
              LEFT JOIN system_users su_own
                  ON u_own.system_user_id = su_own.id
              LEFT JOIN projects p
                  ON tasks.project_id = p.id
          
          
          • 2. Re: Extract Duration from the PostGres HIST Tables
            Keith Helfrich

            Thank you. 

             

            The parameter TS Background Tasks Days Data To Retrieve simply an integer which represents the number of days?

             

            Keith Helfrich | Twitter

            Red Headed Step Data

            • 3. Re: Extract Duration from the PostGres HIST Tables
              Matt Coles

              Yeah, I realized too late that I left that in there, then I edited it back out. Basically it's just a means to improve performance, because Custom SQL just gets wrapped in parentheses and run as a subquery, so it tends to be less efficient than using the normal connection editor.