Calculating time between Timestamps in Wrangler

This document describes how to calculate different units of time between two timestamps using Wrangler. For example, a user may want to calculate the number of months between two calendar dates. This assumes the columns are already timestamps. If they are strings, use the parse-as-simple-date directive to parse the string into a timestamp.

Milliseconds between timestamps

Wrangler includes a diff-date directive that can be used to calculate the milliseconds between two dates.

Years between timestamps

As of 6.4.0, there is no out of the box directive for this, so the set-column directive must be used to calculate the year difference. This can be done as:

set-column :year_diff dt1.getYear() - dt2.getYear()

where year_diff is the name of a new column, and the dt1 and dt2 columns are the names of the timestamps that are being subtracted.

Months between timestamps

As of 6.4.0, there is no out of the box directive for this, so the set-column directive must be used to calculate the month difference. This can be done as:

set-column :month_diff 12 * (dt1.getYear() - dt2.getYear()) + (dt1.getMonthValue() - dt2.getMonthValue())

where month_diff is the name of a new column, and the dt1 and dt2 columns are the names of the timestamps that are being subtracted.

Days between timestamps

As of 6.4.0, there is no out of the box directive for this, so the set-column directive must be used. This can be done as:

set-column :day_diff date:DAYS_BETWEEN(dt1, dt2)