Dates, Time, and Scheduling

Compare the app with Pipeline runtime

Once the app and our Lesson 18 records were corrected to Central Time, we ran the Pipeline and inspected the Date/Time values it received. These examples connect the local values visible in Quickbase with the UTC instants exposed during Pipeline execution.

{# Show the two Date/Time values exactly as the Pipeline sees them. #}
Due: {{ aa.due_date }}
Completed: {{ aa.date_completed }}

{# Subtract the Due Date directly from Date Completed.
   This tests whether Jinja can perform datetime arithmetic
   without trying to extract a separate numeric .time property. #}
Difference: {{ aa.date_completed - aa.due_date }}

{# Subtract Due Date from Date Completed.
   The result is a duration, not a plain number. #}
{% set elapsed = aa.date_completed - aa.due_date %}

{# Convert that duration into total seconds so we can
   compare it against a fixed business threshold. #}
{% set seconds = elapsed.total_seconds() %}

{# Three hours = 10,800 seconds.
   The exact 3:00 boundary still counts as within the requirement. #}
{% if seconds <= 10800 %}
WITHIN 3-HOUR REQUIREMENT
{% else %}
OVER 3-HOUR REQUIREMENT
{% endif %}

{# runtime.triggered_at gives us the UTC timestamp
   for when this Pipeline run began. This becomes our "current time"
   for every record evaluated during this run. #}
Run started: {{ runtime.triggered_at }}

{# Show the Task's stored Due Date for comparison. #}
Due: {{ aa.due_date }}

{# Instead of comparing two dates stored on the record,
   subtract the Due Date from the Pipeline's run time.
   Positive duration = the Due Date is in the past.
   Negative duration = the Due Date is still in the future. #}
Difference: {{ runtime.triggered_at - aa.due_date }}

{# Compare the Pipeline's run time with the Task's Due Date.
   The result tells us where the Task sits relative to "now." #}
{% set elapsed = runtime.triggered_at - aa.due_date %}

{# Convert the duration into seconds so its sign and size
   can be tested against our three-hour business window. #}
{% set seconds = elapsed.total_seconds() %}

{# A negative result means the Due Date has not arrived yet.
   The Task is still upcoming. #}
{% if seconds < 0 %}
UPCOMING

{# Zero through 10,800 seconds means the Due Date has passed,
   but by no more than three hours. #}
{% elif seconds <= 10800 %}
WITHIN 3-HOUR WINDOW

{# Anything greater than 10,800 seconds means more than
   three hours have passed since the Task was due. #}
{% else %}
OVER 3-HOUR WINDOW

{% endif %}

{# Compare the Pipeline's run time with the Due Date
   and convert the resulting duration into seconds. #}
{% set elapsed = runtime.triggered_at - aa.due_date %}
{% set seconds = elapsed.total_seconds() %}

{# Check the business data BEFORE classifying the Task by time.
   A Complete Task with no Date Completed contradicts our expected data,
   so we identify it as an exception instead of calling it overdue. #}
{% if aa.status == 'Complete' %}
DATA EXCEPTION — COMPLETE WITHOUT COMPLETION DATE

{# Only records that pass the business-data check continue
   into our normal time-based classification. #}
{% elif seconds < 0 %}
UPCOMING

{# The Due Date has passed, but by no more than three hours. #}
{% elif seconds <= 10800 %}
WITHIN 3-HOUR WINDOW

{# More than three hours have passed since the Due Date. #}
{% else %}
OVERDUE

{% endif %}

{# Show the Task and the two dates we are about to compare.
   The Project Target Date reaches the Task through the table relationship. #}
Task: {{ aa.task_name }}
Task Due: {{ aa.due_date }}
Project Target: {{ aa.project_target_date }}

{# Subtract the Task's Due Date from the Project Target Date.
   This gives us the amount of time separating the two deadlines. #}
{% set project_gap = aa.project_target_date - aa.due_date %}

{# Write the calculated gap into Pipeline Results so we can
   inspect both the duration and the classification that follows. #}
Task-to-Project Gap: {{ project_gap }}

{# If the Task is due later than the Project Target Date,
   the Task's own schedule extends beyond its parent Project's target. #}
{% if aa.due_date > aa.project_target_date %}
TASK DUE AFTER PROJECT TARGET

{# Equal Date/Time values mean the Task is due exactly
   at the Project's Target Date. #}
{% elif aa.due_date == aa.project_target_date %}
TASK DUE AT PROJECT TARGET

{# Otherwise, the Task is scheduled before the Project Target Date. #}
{% else %}
TASK DUE BEFORE PROJECT TARGET

{% endif %}