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:
Add 31*86400 seconds to the start date
Reformat the result as â01-MMM-YY 00:01:00â to place the result 1 minute after midnight of 1st day of month reached
Deduct 120 seconds to get to last minute of previous month
Now, it calculates correctly for a some years 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??
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!
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:
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.
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.
Thanks for the swift checking!
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.
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.
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.
