Calculate time between 2 points

Consider your house. I want to calculate the time the lights were on between 2 points. For example, I want to know how long the lights were on in August.

Every time the lights are turned on and off, the system records the on time and off time in a database table.

The obvious answer is to sum the “on time” between the from_date and to_date. However, there are 3 other scenarios to consider. See the red lines in this diagram:

Time on

  1. The light was switched on before the interval, and off after. So it was on the entire time.
  2. The light was switched on before, and off during. I only want the time the light was on past the from_date.
  3. The light was switched on during, and off after. I only want the time the light was on up until the to_date.


Any ideas?

I’m betting that you would have come to this conclusion sooner or later on your own with as well as you diagrammed and described your problem. But hopefully this explanation saves you a bit of time getting there.

Light_On = The date/time the light was switched on
Light_Off = The date/time the light was switched off
Start_Date = The date/time of the start of the interval
End_Date = The date/time of the end of the interval

  1. If Light_On < Start_Date AND Light_Off > End_Date : Duration = End_Date - Start_Date (basically the full interval)
  2. If Light_On < Start_Date AND Light_Off < End_Date : Duration = Light_Off - Start_Date
  3. If Light_On > Start_Date AND Light_Off > End_Date : Duration = End_Date - Light_On
  4. (for completeness sake) If Light_On > Start_Date AND Light_Off < End_Date : Duration = Light_Off - Light_On

So you basically already laid out the logic, just a matter of checking those statements and then using the proper start / end date/times. Hopefully that makes sense.

Thanks for taking a look at it @bubble.trouble.

The Light_on and Light_off is in a database table.

Let’s say I have 10 lights and multiple occasions the lights switched on and off in the database table. How do you extract the data and sum it up?

Am I right in thinking 4 steps in a workflow as per your If statements, adding the data to a state. You then display the state as the final answer at the end.

Apologies for the delay.

As is true with other development platforms, there are a number of approaches within Bubble that would get you to the end result. You could use four workflows, each one running with one of the 4 conditions in the “only when” section. Or you could use a single workflow or no workflows at all.

I don’t know how your data structure is setup, but you should be able to adjust this example to fit your application. Again, this is just one approach and I didn’t give any thought to if it’s the best approach. Also, what I’m showing you can be done in one step, I broke it out simply to show you how the solution works (for clarity).

Database structure:

Output structure as list, then as list of time differences, then as sum of differences:

Editor (as list)

Editor (as list of differences):

Editor (as sum of differences):

Set State in a workflow

In the middle example (Editor as a list of differences) I actually show how you don’t need to use a workflow. The other examples are coming from a state set in a workflow with the last image above showing how the state is set.

Regardless of how you choose to implement, the idea is that Bubble allows you to subtract two dates and then lets you choose how you want the number returned hours/minutes/seconds/etc. From there it is a matter of summing that number together with the other numbers in your list.

This topic was automatically closed after 70 days. New replies are no longer allowed.