How to manage addition of months in expressions?

Hi fellow tulippers,

I am creating an app where areas in a facility are cleaned following various schedules. Daily (w & w/o weekend), Weekly, Bi-weekly, Monthly, Bi yearly and so on. The cleaning needs to be completed before the expiration of the task is reached. For most of them it is no issue, but moving the expiration to end of the next month, or at the end of an additional 6 months proves a bit of a challenge when working with seconds as addition unit.
Have anyone managed to add months to a date?

In my case I e.g. start with “31-Jan-22 23:59:00” as start date, and I want the new expiration to move to “28-Feb-22 23:59:00” which do not seem to easy working with seconds.
I have come up with a workaround which can be done in an expression (shown below).
Calculation steps are as follows:

  1. Add 31*86400 seconds to the start date
  2. Reformat the result as ‘01-MMM-YY 00:01:00’ to place the result 1 minute after midnight of 1st day of month reached
  3. Deduct 120 seconds to get to last minute of previous month

Now, it calculates correctly for a some years :grinning: before it gets off track, so for my purpose it will be fine - but it seems to be a really convoluted way of adding a month to a date.

Any other suggestions, or is this really the way to do it??

Thanks in advance for your good ideas :slight_smile:
Asger


Expression that adds the necessary months:


Expression:
texttodatetime(datetimetotext(@Variable.Start Date + (86400 * 31 * @Variable.NumberOfMonth ), ‘01-MMM-YY 00:01:00’), ‘DD-MMM-YY HH:mm:ss’) - 120

Test app:
image

Hey @ASharp-J,

You hit on the right way to do this right now. I ran into this about 2 weeks ago when adding a year to a date. I have proposed a new function for the expression editor INTERVAL(amount,unit). So in your case you could do ROUNDDATETIME(app info.current date and time,‘day’) + INTERVAL(1,‘month’). This would account for months of different lengths and leap years.

This feature is still being scoped, but it isn’t a hard add. Let me connect with the engineering teams to see if this can be snuck into an upcoming release.

Thanks for a bump on this limitation! We prioritize our engineering based on what we hear from the community!

Pete

1 Like

I moved this topic to “Product suggestions” so that the feature request portion isn’t missed and we can get some votes if others have same request.

I have the same requirement as you. in general, what we want is datetime math. hope tulip can come up with solution instead of using multiple function.

Spent some time with our engineering teams last week talking about this and came to a couple limitations.

We really can’t use interval datatype for this usecase because intervals represent a time in seconds, and adding a month to feb 1 will be a different amount of seconds than adding a month to april 1. Because of this, we can take two different approaches:

  1. add another datatype that supports this dynamic assignment based on what it is calcuated against. This makes it more confusing for new users, and is a lot of work on the engineering side.
  2. Add a ADD_TIME(prev_value,count,unit) and SUBRACT_TIME(prev_value,count,unit) functions that operate like that proposed INTERVAL function. This approach is an easy win and can be snuck into a future release much easier.
ADD_TIME(@app info.Current date and Time, 1, 'month')

Is there any use case where just add and subtract wouldn’t do what you need? I can’t think of one.

Pete

Thanks for the swift checking! :slight_smile:
For my use case having access to option 2 would be sufficient. I am as such not using interval for this. I would add that having two functions wouldn’t necessarily be required since the “count” could be either positive or negative. An upside might be that triggers e.g. wouldn’t need to figure out what function to call but just call the same “add_time” function.

Hey @ASharp-J,

I’ll pipe this back to the team! Realistically adding both functions is probably the safer approach just so we don’t get a flurry of “I see add time but I want to subtract time” requests.

Let me connect with the engineering teams and I will sort out what release we might expect this.

Pete

option 2 is enough to meet my requirement