Duration Between Two Dates

A very frequent use case is a computation of the time difference between two dates or time columns. The duration between two dates is best expressed as a humanly readable information.

For that, we can use the benefit of the duration data type in Lumeer. It show a human friendly values. E.g. 4d5h means 4 days and 5 hours.

Let’s imagine we have a table with tasks, events, or any actions. Those typically have some start and end. No matter if the are just dates or dates including time. We use the same procedure.

How To?

In our example, we will have a table with projects. There are columns Begin and End. We have already added a new column Duration.

Now click on the small down arrow/chevron right to the Duration label. In the context menu, select Attribute type. Set the column type to Duration. As shown below.

Switch the time scope to Normal. This way, a day is equal to 24 hours, an hour is 60 minutes etc. Like we are used to.

The default option was Work. This is better suited for accounting task durations because 16 hours worth of effort are equal to 2 work days.

After clicking on the Save button, open the context menu for the Duration column once again. This time, select Edit function.

Functions are edited as blocks. This allows to represent them visually in an easy to understand way.

First, use the mathematical operation from the Math menu (displayed with the + sign by default). Connect it to Duration and switch it to subtraction.

We also need to read two attributes from the current document (row). We get End and Begin column values. The type of these columns is set to Date.

Normally, dates cannot be subtracted. So convert them to milliseconds using the date to millis block. This way, we’ll get a number.

Are you interested on what is that number? It is a number of milliseconds since January 1st 1970 (so called Epoch). This is how dates are often internally represented by computers.

We can now normally subtract these numbers. End being later means a greater number (longer since January 1st 1970). So End comes first.

When everything is put together, we can see the duration between two dates automatically calculated.

You don’t ever need to worry to copy any formulas between all the cells.