Datetime Delta
Calculate the time difference between two datetime columns in seconds, minutes, hours, or days. Configure multiple delta calculations in a single operation.
Datetime Delta calculates the difference between two datetime columns and stores the result in a new column. Choose the unit: seconds, minutes, hours, days, or a formatted duration string. The source columns are unchanged. You can configure multiple delta calculations in one operation.
The result is computed as Base Column minus Compare Against. A positive result means the Base column is later in time than the Compare Against column.
When to Use Datetime Delta
- Delivery lead time. Subtract
order_datefromdelivery_dateto calculate days between order and delivery. - Session duration. Subtract a session start timestamp from a session end timestamp to get duration in minutes.
- SLA monitoring. Calculate hours between a ticket created timestamp and a resolved timestamp to flag breaches.
- Age calculation. Calculate days between a signup date and today's date to derive customer age in the dataset.
- Processing time. Subtract a job start time from a job end time to measure pipeline processing duration in seconds.
Sample Dataset
The examples in this doc use the Edilitics sample orders dataset. Download it to follow along in your own workspace.
edilitics_sample_orders.csv
Sample orders dataset for hands-on practice · 500 rows
Relevant columns for Datetime Delta examples:
Prop
Type
Result Units
| Label | Output type | What it returns |
|---|---|---|
| Days | Integer | Whole days only. timedelta.days - fractional days are dropped. |
| Hours | Float (2dp) | Total hours including fractional. total_seconds / 3600. |
| Minutes | Float (2dp) | Total minutes including fractional. total_seconds / 60. |
| Seconds | Float (2dp) | Total seconds. timedelta.total_seconds(). |
| Default | String | Python timedelta string. Example: "32 days, 5:14:22". |
Days uses integer truncation - it returns whole days only via
timedelta.days. A gap of 23.9 hours returns 0 days. Use Hours if you need
sub-day precision.
How to Apply Datetime Delta
Open the Datetime Delta operation
In your Transform pipeline, click Add Operation and select Datetime Delta from the operation list.
If no datetime columns exist in the dataset, an info toast appears and the operation closes automatically. Cast string date columns to datetime first using Cast Data Types.
Select the Base Column
Choose the datetime column to subtract from - the later of the two timestamps. In the sample dataset, use delivery_date as Base Column to calculate delivery lead time.
Only datetime, timestamp, date, and time columns appear in the dropdown.
Select the Compare Against column
Choose the datetime column to subtract - the earlier timestamp. In the sample dataset, use order_date.
The same column cannot be selected for both Base and Compare Against. Selecting the same column clears the Compare Against field automatically.
Choose the result unit
In the Aggregate To dropdown, select the unit for the result: Seconds, Minutes, Hours, Days, or Default.
Use Days for lead time in whole days. Use Hours or Minutes when sub-day precision matters. Use Default to get a human-readable duration string for display purposes.
Name the result column
Type a name in the Result Column Name field. There is no auto-generated name - you must enter one.
The name must:
- Contain only letters, numbers, and underscores
- Not start with
__or a number - Not already exist in the dataset or in another delta row in this operation
Add more delta calculations (optional)
Click Add Column to add another delta row. This button is disabled until the current row is fully complete. Use this to calculate multiple deltas in one save.
Click Save & Preview
Click Save & Preview. Edilitics computes all deltas and adds the new columns to the dataset. The success toast confirms: "Time difference calculated. Preview updated with the result column."
Verify in the preview
Check the new column. Negative values indicate rows where the Base Column is earlier than Compare Against - check whether the columns are in the right order. Null in either source column produces null in the result.
Before and After
Delta: delivery_date (Base) minus order_date (Compare Against), unit Days, result column delivery_days.
Input:
| order_id | order_date | delivery_date | revenue |
|---|---|---|---|
| ORD-2024-0001 | 2024-01-15 08:32:00 | 2024-02-18 14:10:00 | 12450.00 |
| ORD-2024-0047 | 2024-03-22 14:15:00 | 2024-04-09 09:30:00 | 8320.50 |
| ORD-2024-0099 | 2024-06-07 09:45:00 | 2024-07-08 16:22:00 | 83841.00 |
Output:
| order_id | order_date | delivery_date | revenue | delivery_days |
|---|---|---|---|---|
| ORD-2024-0001 | 2024-01-15 08:32:00 | 2024-02-18 14:10:00 | 12450.00 | 34 |
| ORD-2024-0047 | 2024-03-22 14:15:00 | 2024-04-09 09:30:00 | 8320.50 | 17 |
| ORD-2024-0099 | 2024-06-07 09:45:00 | 2024-07-08 16:22:00 | 83841.00 | 31 |
Source columns unchanged. One new integer column added.
Code Equivalent
-- Days difference (PostgreSQL / BigQuery / Snowflake / DuckDB)
SELECT
*,
DATE_PART('day', delivery_date - order_date)::INTEGER AS delivery_days
FROM orders;
-- Hours difference
SELECT
*,
ROUND(EXTRACT(EPOCH FROM (delivery_date - order_date)) / 3600, 2) AS delivery_hours
FROM orders;import polars as pl
# Days (integer, whole days only)
df = df.with_columns(
(pl.col("delivery_date") - pl.col("order_date"))
.dt.total_days()
.alias("delivery_days")
)
# Hours (float, rounded to 2dp)
df = df.with_columns(
((pl.col("delivery_date") - pl.col("order_date"))
.dt.total_seconds() / 3600)
.round(2)
.alias("delivery_hours")
)After Save & Preview, the pipeline shows a DQ delta badge on this step - green if the table score improved, red if it dropped. See Data Quality Scoring for how scores are calculated.
After Save & Preview, the pipeline shows a DQ delta badge on this step - green if the table score improved, red if it dropped. See Data Quality Scoring for how scores are calculated.
Operation Reference
Prop
Type
Frequently Asked Questions
Next Steps
Datetime Aggregation
Extract year, month, weekday, or hour from a datetime column into a new column.
Group By
Group by the delta column to compute average or max lead times per region or tier.
Manage Timezones
Normalise datetime columns to a consistent timezone before calculating deltas.
Conditional Column
Flag rows where the delta exceeds a threshold - for example, deliveries over 30 days.
Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.
Last updated on
Bin / Discretize
Group continuous numerical values into labeled buckets using Equal Width, Quantile, or Custom Breaks visually without writing code in Edilitics Transform.
Datetime Aggregation
Extract year, quarter, month, week, day, or time components from a datetime column into a new column. Configure multiple extractions in one operation.