Playing with time

kklosson

I want to set CURRENT_TIMESTAMP to adjust to a local. My plan is to have the user set their local in terms of hours +/- from Eastern timee. So for the west coast, they would choose -3. What was be added to a TIME_STAMP in order to adjust the hours?

technopak

Hi,

I've only glanced at the guide but it looks as if the HOUR function will do the job for you.

I'd try -

IF locale='WestCoast' THEN
HOUR(LocalTime) = HOUR(CURRENT-TIMESTAMP)+3

where LocalTime is a TimeStamp attribute for the locale

The guide says HOUR is a date function which doesn't make sense to me, so I bet it works with a TimeStamp.

I haven't tried it though 🙂

Peter

tford

I use this to record a last login time. In this case, server is 3 hours time difference from main user.

LoggedInRegularUser.LastLoginTime=TIME_ADD(CURRENT_TIMESTAMP,3)

You could easily have the last parameter in the TIME_ADD function be user specific.

kklosson

Thanks for the tips. It appears that both solution will work, but here's my challenge and perhaps you can offer a solution or a different approach.

I have a value that the user sets to indicate their difference from server time with a range of -12 to 12. It seems that if I use a Number data type, my choices are not acceptable to the integrity checker. If I use a Plain Text data type, it seems to ignore a negative value. So if my attribute is set to -3 it adds 3 instead of subtracting.

tford

This rule works for me:

LoggedInRegularUser.TimeCheck=TIME_ADD(CURRENT_TIMESTAMP,LoggedInRegularUser.TimeDiff)

where LoggedInRegularUser.TimeDiff is a Number attribute type attribute.

kklosson

It works for me too but not so far with negative numbers. Do you use negative numbers in the TimeDiff?

kklosson

I believe I've got it going now. Thank you again.

rocketman

I'm having a slightly different issue with negative numbers. The following is supposed to bring up a list of people who have an upcoming event in two moths time. It also brings up people who had an upcoming event two months ago ie if used today, the search results would contain dates in November (What I would expect) and July (when I would have expected that to require a syntax of MONTH_DIFFERENCE(Contacts.DateOfEvent,CURRENT_DATE)=-2)

FIND Contacts WHERE MONTH_DIFFERENCE(Contacts.DateOfEvent,CURRENT_DATE)=2 ORDER BY Contacts.DateOfEvent DESC

I know it's not time related but ir does seem as if Month_difference is not a straighfoward mathematial calculation. This is not made clear in the manual

kklosson

On the same note, while I haven's struggled with it much, I'm finding that the above function does not work on a document, which I include in the footer. It will print the time stamp but does not modify it with the TIME_ADD function. I need to play more to be sure I'm doing things right, but that's the result so far.

technopak

Hi,

In relation to your document issue, I think I'd revert to the good old SystemSettings object and place the locale time in an attribute there, calculated at runtime. Handling the SystemSettings attribute in a document should prove much easier.

You could even go all out and have a SystemSettings attribute for each locale which could be calculated dynamically, and used in many processes, etc etc.

Just a suggestion .....

Peter

kklosson

This thread was a while back, but I'm on the issue again. When I use a TIMEADD() function as a document attribute, I get ## instead. This is just a timestamp function that adds a value from the account, which is a simple number like 1,2,3 or -1,-2,-3.

tford

kklosson,

Without reading the whole thread, I'm not clear what you question / issue is. Can you summarize it?

kklosson

Well don't you know in crafting my explanation I discovered my syntax error.
Thanks

tford

Good one. Can't tell you how many times that's happened to me.

Mark HHP

Hey, thought I'd resurrect this one with a simple question (simple to you, complex to me)

I have a leave application form that takes your start date and end date and works out number of leave days requested. BUT I need it to ignore weekends, so that taking start date on Friday and end on Monday returns one day and not 3.

RocketRod

As always there are lots of ways to do these.

You could use: DAY_OF_WEEK, DAY_NUMBER_IN_WEEK and WEEK_DIFFERENCE.

I'd put a test in to make sure start date and end dates were not weekends so that you don't have to worry about that later. Use DAY_OF_WEEK.
If week_difference < 1 and day_number_in_week of the enddate < day_number_in_week of the startdate then take off 2.
Then if the week_difference > 1 you would take off 2 days for each complete week but you still need to look at first and last weeks.

I haven't put down the actual code but this should get you started. Do less than one week and you will see the process for mutliple weeks.

Cheers Rod

customaware

Mark,

As I have discussed with you...I am done a similar app as yourself...Employee Leave Management.

In mine the non working day for an Employee is not just limited to Weekends. It could be any day of the week. I agree with Rod, that you need to start by stripping the leading and trainling target days and then test in between. I spent yesterday nutting through this logic and present it below for you to try.

I have tested each part and it works.

The awesomeness about Aware is that the Rule engine will continue to execute these Rules all the while the conditions are true. Hence, no matter how long your Event is, it will be broken up into as many smaller Events as necessary to avoid an Event day falling on your non-work day.

Monday is Unique, but Sunday and Tuesday to Saturday are virtually the same with just number alterations....

SUNDAY:

Strip Starting Sunday
DAY_NUMBER_IN_WEEK(Event.StartDate)=1
Is the Start Day a Sunday
Event.StartDate=DATE_ADD(Event.StartDate,1)
If Yes, Then Move the Start Date forward by 1 Day. If No, Do Nothing

Strip Ending Sunday
DAY_NUMBER_IN_WEEK(Event.EndDate)=1
Is the End Day a Sunday?
Event.EndDate=DATE_ADD(Event.EndDate,-1)
If Yes, Then Move the End Date backward by 1 Day. If No, Do Nothing

Does the Event Still Contain a Sunday?

Is the a Sunday within a week of our Start Date
END_OF_WEEK(Event.StartDate)>Event.StartDate AND
END_OF_WEEK(Event.StartDate)<Event.EndDate

Is the Date of Next Sunday > our Start Date
Is the Date of Next Sunday < our End Date?

IF so, Create a New Event
Set New Start to 1 Day after Next Sunday…Monday

CREATE Event WITH
Event.StartDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),1),
Event.EndDate=ThisEvent.EndDate

Set End Date to Original Event End Date and Adjust Existing Event
Set End Date to Next Saturday. Day Before Sunday
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),-1)

MONDAY:

Strip Starting Monday
DAY_NUMBER_IN_WEEK(Event.StartDate)=2
Is the Start Day a Monday
Event.StartDate=DATE_ADD(Event.StartDate,1)
If Yes, Then Move the Start Date forward by 1 Day. If No, Do Nothing

Strip Ending Monday
DAY_NUMBER_IN_WEEK(Event.EndDate)=2
Is the End Day a Monday?
Event.EndDate=DATE_ADD(Event.EndDate,-1)
If Yes, Then Move the End Date backward by 1 Day. If No, Do Nothing

Does the Event Still Contain a Monday?

Is the a Monday within a week of our Start Date
DATE_ADD(END_OF_WEEK(Event.StartDate),1)>Event.StartDate AND
DATE_ADD(END_OF_WEEK(Event.StartDate),1)<Event.EndDate

Is the Date of Next Monday > our Start Date
Is the Date of Next Monday < our End Date

IF so, Create a New Event
Set New Start to 2 Days after Next Sunday…Tuesday

CREATE Event WITH
Event.StartDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),2),
Event.EndDate=ThisEvent.EndDate

Set End Date to Original Event End Date
and Adjust Existing Event Set End Date to Next Sunday. Day Before Monday

ThisEvent.EndDate=END_OF_WEEK(ThisEvent.StartDate)

For Tuesday, Wednesday....Saturday, you need to Increment the number in the DAY_NUMBER_IN_WEEK functions when stripping leading and trailing, the number of days to add in the DATE_ADD functions.

Also, the last Action for Monday needs to revert back to same style as for Sunday when you do Tuesday...Saturday...but the days to add increments from 1 for Tues ..... 5 for Sat as below...

Tue:
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),1)
Wed:
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),2)
Thu
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),3)
Fri
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),4)
Sat
ThisEvent.EndDate=DATE_ADD(END_OF_WEEK(ThisEvent.StartDate),5)

Hope this helps.

Mark HHP

Ah Mark, you're a magician!

Let me try this and see how it works out 🙂

customaware

Don't get to excited Mark.

I implemented this over the weekend and it seems to work perfectly is the is only a single non working day in the week.

However, for some wierd reason, if is mor than one day, ie Saturday and Sunday, or even two days separated by some other day.....It goes nuts and ends up in an endless loop.

Have looked at the logic time and time again and cannot work out the issue.
Seems to skip dates and then ends up outside the Event range, hence...keeps going.

🙁

Mark HHP

Hmm.

Ok, well, I've printed out the code and I'm staring at it intensely, hoping that the error will magically jump out...

Will let you know if I win.