#################
Batch Jobs / Jobs
#################

***********************************************
Distribute execution time of a batch job evenly
***********************************************

Execute batch job ``nice2.dms.DeleteUnreferencedBinariesBatchJob`` hourly at a random time but only between 06:00 and
21:59.

.. code-block:: sql

    UPDATE nice_batch_job
    SET
      minutes = trunc(random()*60)::text,
      hours = '6-21',
      days_of_week = '*',
      months = '*'
    WHERE id = 'nice2.dms.DeleteUnreferencedBinariesBatchJob';


***********************************************
Jobs Grouped by Name and their Longest Duration
***********************************************

.. code-block:: sql

    SELECT
      name,
      round(max(extract(epoch from end_date) - extract(epoch from start_date))) AS longest_duration,
      round(avg(extract(epoch from end_date) - extract(epoch from start_date))) AS avg_duration,
      max(start_date) AS last_execution
    FROM nice_task_execution
    WHERE start_date > now() - interval '10 days'
    GROUP BY name;

Sort by duration::

    sudo -u postgres n2sql-on-all-dbs -w --csv "$SQL" | sort -g -t , -k 3,3


*****************************************
List Jobs that Failed the Last Three Runs
*****************************************


.. code-block:: sql

    WITH third AS (
        SELECT
          te.name,
          (SELECT start_date FROM nice_task_execution WHERE te.name = name ORDER BY start_date DESC OFFSET 2 LIMIT 1) as start_date
        FROM nice_task_execution as te
        GROUP BY te.name
    ) SELECT te.name
    FROM nice_task_execution AS te
    LEFT OUTER JOIN nice_task_execution_status AS tes ON te.fk_task_execution_status = tes.pk
    WHERE start_date >= (SELECT start_date FROM third WHERE name = te.name) and tes.unique_id = 'failed'
    GROUP BY te.name
    HAVING count(*) = 3
    ORDER BY 1;