Date Difference

Measure the gap between two date columns as a number.

What it does

Date Difference reads two date columns on the same row. It gives you the distance between them. You choose the unit, such as days or months. The answer is a number, so you can sort it, filter it and average it.

Measure the gap

  1. Type difference in the Search transforms box, in the Pipeline panel.
  2. Select Date Difference in the results list.
  3. Open First date column and pick the earlier date.
  4. Open Second date column and pick the later date.
  5. Look at Unit. It is already set to day.
  6. Type a name in New Column Name.
  7. Click Apply.

Example: a subscription table holds signup_date and cancel_date. Pick them in that order. Name the result days_subscribed.

Read the sign

The tool counts from the first column to the second column. The order matters:

  • The second date is later, so the number is positive.
  • The second date is earlier, so the number is negative.
  • The two dates match, so the number is zero.

A negative result usually means the two columns are the wrong way round. Edit the step and swap them.

Change the unit

Open Unit and select day, week, month, year, hour or minute. Use month for contract length. Use minute for a support response time. The answer is always a whole number.

Where the number goes

The Apply results into section starts on New Column (Number). Type a name in the box. Select Existing Column to write the number over a column you already have. Leave the name empty and the new column is called date_diff.

Notes

  • Both dropdowns list date columns only. Convert text to a date first if a column is missing.
  • Both dates are required. The step reports an error if one is empty.
  • Rows with a blank date give a blank result.

For SQL users

The step calls DuckDB's date difference function. A gap in days runs as:

SELECT *, DATEDIFF('day', "signup_date", "cancel_date") AS "days_subscribed" FROM data
Try Date Difference with sample data →

Related Operations