[Lens] Formula: Date math

[flash1293]

At the moment Lens formula can only deal with numerical values. A common use case is to calculate the elapsed time between two dates (like last_value(end_date) - last_value(start_date)).

This would be a nice addition to todays feature set, but it brings a bunch of technical complexity to formula we could avoid so far. We would have to track the data type when traversing the AST - for example it doesn't make sense to subtract a date from a number and vice versa. Also, tinymath functions would either need to be extended to handle dates as well or we would have to introduce additional functions for date math specifically, e.g seconds_between(last_value(end_date), last_value(start_date)).

Right now there's a workaround for some cases by using runtime fields instead, but this doesn't work for dates originating from different documents.

To clarify:

• What are the exact use cases to cover?
• Which functions do we need for date math?
• Should it be possible to define a date-only formula? Right now it's limited to numerical output

[elasticmachine]

Pinging @elastic/kibana-vis-editors (Team:VisEditors)

[flash1293]

cc @ghudgins - maybe you have some things in mind for this?

[ghudgins]

https://discuss.elastic.co/t/how-to-calculate-elapsed-time-between-timestamps-in-a-given-timerange/283449/

[dej611]

for example it doesn't make sense to subtract a date from a number and vice versa.

Probably it is still useful to do some math with Dates and some number constant.

[ghudgins]

Adding additional cases:

• Exposing the time range size in ms as a variable in the formula
• interval_ms - exposing the size of the current interval when using a date histogram

[flash1293]

Another possible variable - now() which returns the current time stamp in ms