Aggregation over date properties | Voters

Aggregation over date properties

Jiri Melichar

Currently aggregation properties are only being calculated on number properties and not date properties.

Created by Yair Siman Tov

January 19, 2024

Mark Tarry

For some additional context here, we've run into this limitation in the following scenario:
* Blueprint: commit
 - which we upsert to Port via our own automation, and includes a committed_at
 property (type: "string"
 and format: "date-time"
)
* Blueprint: pull_request
 - which is related to one or more commit
 entities
As part of calculating the DORA "Lead time to change" metric, we want an aggregate property on pull_request
 which finds the min
committed_at
 date-time value.
But we cannot do that - as aggregates only support number
 properties. 
We employed the following workaround:
commit
 blueprint now has a calculation property (type: "number"
) named committed_at_epoch
, using JQ: .properties.committed_at | .[0:19]+"Z" | fromdateiso8601
pull_request
 aggregate is now first_commit_date_time_epoch
, and pulls the min
committed_at_epoch
 value from related commit
 entities
pull_request
 blueprint has an additional calculation property (type: "string"
, format: "date-time"
) to format the epoch value into something more user friendly, using JQ: .properties.first_commit_date_time_epoch | todateiso8601
This isn't pretty. But it does allow us get around the current limitation.
However, this workaround has it's own limitations:
* Calculation properties cannot be used when searching/filtering entities - so we cannot search for pull_request
s with a first commit in preset date-range
* ...which limits what we can do with pull_request
 entities when building aggregates elsewhere - i.e. to calculate an average "Lead time to change" across all PRs on a repository!
I'm currently reviewing options to abandon the use of calculation properties here, and instead rely on automations to keep the various fields aligned.