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.
Just as a follow up on this to friends working with time in LTS versions of Tulip.
My project is on LTS 8.2 where we do not have add_time function.
Our app is instructing cleaning of rooms and areas to be completed by end of day, and moves target to next âend of dayâ upon completion. Most of the time it is working fine but approaching daylight savings change we get in trouble. In LTS8.2 we have to add seconds (86400) to move one day which in the coming weekend pushes me from 23:59:00 to 22:59:00, i.e. one hour off. In the spring it would jump to ~1AM the following day. It isnt critical because we have delayed go live of the app to after daylight saving change, but @Pete_Hartnett do you know which LTS release will include ADD_TIME? I have tested ADD_TIME in my r239.3 release and adding 1 day will not mess up the timestamps, so I just need to upgrade and correct my app before March 2023.
LTS9 will include ADD_TIME(). LTS9 is in the final stages of testing right now. The final release date is contingent on this testing, but we are targeting a launch date in mid-November, with a 2-week delay for customers running on a customer cloud.
The intention of this function is to address exactly what you are talking about with timezones, leap years, and more making reliably getting 1 month from today a challenge.
That is excellent news! Thanks for the quick reply.
When using addition of month we just need to be aware of how it returns dates. If adding â1 monthâ to Jan 31st I get Feb 28th. Adding 1 month to Feb 28th gave me March 28th. So given I was looking for âEnd of monthâ it only worked half of the year .
I completely get that you had to implement rules around what happens and as longs as we are clear on what goes on we are fine. For my end of month use case it is a little tricky to use. But not impossible
Thanks for keeping a constant eye on the community and taking over the ideas we bring up!
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.
