Date filter confusion/possible bug

Post any question regarding setting up, running or configuring Verj.io Workflow

Moderators: Jon, Steve, Ian, Dave

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Date filter confusion/possible bug

#1

Postby ericb » Mon Feb 22, 2021 10:39 pm

Hello,

Our workflow processes have an "effective date" that is set by the initiator when they submit the initial form that creates a workflow job. The last node in the process is a system task that saves the form's data to the database, and the second-to-last node is a pause node that runs every hour to check if the effective date has been reached. Since it runs every hour, this pause node would be completed sometime between midnight-1am on the effective date.

Recently, we noticed that rather than being completed between midnight and 1am as expected, pause nodes were instead being completed on the night before, between 7 and 8pm, or 5 hours ahead of schedule. We're in the EST timezone which is GMT-5 right now, so I figured this was some kind of timezone-related issue, and I started looking into it.

Before going further, I need to mention that we've added a few fields to the included Workflow Administration form to help us filter jobs. Notably for this issue is the Form Creation To/From date fields:
timezone0.png
timezone0.png (15.23 KiB) Viewed 5283 times
On Feb 18th, I created a test job at 8:52pm to troubleshoot the issue. When I went to the Workflow Admin to see it there, I noticed it didn't appear when I filtered for jobs created on the 18th:
timezone_18.png
timezone_18.png (16.81 KiB) Viewed 5283 times
However, it does appear if I filter for jobs started on the 19th:
timezone_19.png
timezone_19.png (19.53 KiB) Viewed 5283 times
I've logged the js Date objects that are being passed to the getFilteredJobs() function, as well as the values for job.getCreationDate(), and I get this, which is what I would expect:

Code: Select all

Mon Feb 22 17:28:16: INFO fromDate    : Fri Feb 19 2021 00:00:00 GMT-0500 (EST)
Mon Feb 22 17:28:16: INFO toDate      : Fri Feb 19 2021 00:00:00 GMT-0500 (EST)
Mon Feb 22 17:28:16: INFO Thu Feb 18 2021 20:52:36 GMT-0500 (EST)
Mon Feb 22 17:28:16: INFO Fri Feb 19 2021 15:07:46 GMT-0500 (EST)
It seems like there's some kind of timezone conversion that should happen but isn't, or shouldn't happen but is, when Verj is processing the getFilteredJobs() function with included creationDateFrom and creationDateTo parameters.

This is the code that gets the values from the date filter fields and retrieves the jobs:

Code: Select all

var fromDate;
fromDate = new Date(fields.JOB_CREATED_FROM.value);
log("fromDate    : "+ fromDate);

var toDate;
toDate = new Date(fields.JOB_CREATED_TO.value);
log("toDate      : "+ toDate);

var jobs = api.getFilteredJobs(jobId, null, null, null, finishedJobs, fromDate, toDate, null, null, procAttr, true, false, form.language, system.securityManager.getUserName());
I'm hoping someone could shed some light on what's going on, or look into whether there's a bug in the getFilteredJobs() function? That said, this still wouldn't explain why the pause nodes were completing 5 hours early, but I wonder if whatever is causing one issue might also be causing the other.
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#2

Postby Steve » Tue Feb 23, 2021 3:56 pm

Hi Eric,

I think I will need to look into this a bit further and maybe try and replicate. There might be an issue with the way we handle dates as process attributes values.

Could you also tell me whether you have the use system Timezone set using the ebase admin app?

Could you tell me which version of the software you are using?

Kind regards

Steve
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: Date filter confusion/possible bug

#3

Postby ericb » Tue Feb 23, 2021 6:45 pm

Hi Steve, thanks for your reply.

Yes, "Use system timezone" is checked.

The app is using version 5.5.1. I checked the release notes of the versions released since that one, and didn't see anything related to timezone, so I figured it was probably still a relevant issue.
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#4

Postby Steve » Wed Feb 24, 2021 5:27 pm

Hi Eric,

The getFilteredJobs does not do anything with the dates by converting them. The creation data sent into the getFilteredJobs is compared directly with the date coming from the database using standard Java Date and Timestamp objects.

I tested this and it appears to be fine.

I have looked at the open job and this does not appear to do anything with the date create or modified that I would expect not to work. It also just creates uses standard Java Date objects.

If the "Use System Timezone:" is checked on the server admin app, I am expecting this work as expected.

Could you confirm which database you are using?

I'd be interested in seeing the "workflow_job" database table rows in the UFS Repository and see whether the dates are wrongly inserted here.

You could email me a snapshot of this table to support@ebasetech.com

Kind regards

Steve
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: Date filter confusion/possible bug

#5

Postby ericb » Wed Feb 24, 2021 9:58 pm

Hi Steve,

My local dev environment is using the standard embedded Derby UFS database. Our production environment uses postgres for the UFS database. I found a form on our prod environment that was created after 9pm local time, and it also had the same issue.

---

I did some more testing today by keeping the "Use System Timezone" checkbox checked, but by manually changing my computer's timezone to UTC. Here's what I observed:

- In the Derby DB, the timestamp stays the same regardless of what system timezone I'm using. I think this is to be expected since the Derby documentation says this:
Derby’s SQL TIMESTAMP type represents a time of day in the form yyyy-mm-dd hh:mm:ss.fffffffff (nanosecond granularity) with no associated time zone information.
(https://db.apache.org/derby/papers/JDBC ... +TIMESTAMP)

- After restarting the integrated server to apply the timezone change, the filter now works correctly:
timezone_18_utc.png
timezone_18_utc.png (5.3 KiB) Viewed 5257 times
---

After this, I changed my system's timezone back to my real one, and tested adding some time to the toDate filter field, and seeing what would happen.

Code: Select all

toDate.setHours(18, 59, 59, 999);
Adding 18:59:59.999 to the date results in neither the job from the 18th or the 19th being displayed.

If I added 1 ms more though, to make it an even 19 hours:

Code: Select all

toDate.setHours(19, 0, 0, 0);
Then both the jobs from the 18th and 19th are displayed.

Therefore it seems that getFilteredJobs() ignores the time component of the Date object passed to them as filters. So it's just looking at "2021-02-18".

Additionally, when retrieving the creation date from the database, a Java Date object is created which treats that timestamp as a local time. I've verified this by checking getCreationDate().getTimezoneOffset() on the jobs returned from getFilteredJobs():
offset.png
offset.png (6.66 KiB) Viewed 5257 times
My conclusion is that it seems that getFilteredJobs() is comparing the UTC date components of both the Date object retrieved from the database and the date filter passed to it. If the job was created inside the window where the local date doesn't match the local date (2021-02-18 local vs 2021-02-19 UTC in this case), then the job is erroneously excluded from the results. With us being 5 hours behind UTC, that means that all jobs created after 7pm will be treated as if they were created on the day after, because the comparison doesn't take time or timezone into account.
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#6

Postby Steve » Thu Feb 25, 2021 9:23 am

Hi Eric,

Thanks for the information. Let me look into the comparison routine as this is not using SQL to filter dates, but it does use Java Date comparisons.

I will have a look later today.

Kind regards

Steve
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#7

Postby Steve » Thu Feb 25, 2021 12:22 pm

Hi Eric,

I have analysed the getFilteredJobLists and I have found the following:

The creation and modified dates in the database are using Timestamp fields and take time into account. I cannot see any problem with these dates using the system timezone.

But the dateFrom and dateTo fields passed into the getFilteredJobs() are using standard Date objects. This means there is no time set on them. So you will have to explicitly set the dateTo.. time to 23:59.59.999 if you want the set it to the end of the day, my debug is as follows:

hu Feb 25 06:32:57: INFO fromDate : Thu Feb 25 2021 00:00:00 GMT-0500 (GMT-05:00)
Thu Feb 25 06:32:57: INFO toDate : Thu Feb 25 2021 00:00:00 GMT-0500 (GMT-05:00)
Thu Feb 25 06:32:57: INFO toDate: time adjustment : Thu Feb 25 2021 05:15:59 GMT-0500 (GMT-05:00)
Thu Feb 25 06:32:57: INFO Thu Feb 25 2021 05:15:17 GMT-0500 (GMT-05:00)
Thu Feb 25 06:32:57: INFO 300
Thu Feb 25 06:32:57: INFO Thu Feb 25 2021 05:15:35 GMT-0500 (GMT-05:00)

Note that the toDate time adjustment is:
toDate.setHours(05, 15, 59, 999);

The code that compares the from and to dates also compares the times are above and below the from and to respectively.

Another option is to make the from and to fields on the page, datetime fields instead, but I would think that a simple setHours(23, 59, 59, 999) on the toDate will be sufficient.

For the Pause node, I am wondering if this is the same problem where you are using a type "date" for the process attribute instead of a datetime so that the time is set to 00:00:00 instead of the real time.

I think there might be an issue with the Pause node logic and the way it is comparing the effective date?

Kind regards

Steve
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: Date filter confusion/possible bug

#8

Postby ericb » Thu Feb 25, 2021 3:14 pm

Hi Steve,

I had tried adding time with setHours yesterday, and tried again today. Before this I also changed the to/from date fields to datetime fields instead, in case it made a difference.

With no time adjustement, I don't get any of the jobs:

Code: Select all

Thu Feb 25 10:05:35: INFO Executing Javascript script wfa_jobs_fetchJobs
Thu Feb 25 10:05:35: INFO fromDate    : Thu Feb 18 2021 00:00:00 GMT-0500 (EST)
Thu Feb 25 10:05:35: INFO toDate      : Thu Feb 18 2021 00:00:00 GMT-0500 (EST)
Thu Feb 25 10:05:35: INFO ** No jobs found **
Thu Feb 25 10:05:35: INFO <-- Displaying page JOBS
If I add 23:59:59.999, I get both the one on the 18th as well as the one from the 19th:

Code: Select all

Thu Feb 25 10:06:58: INFO Executing Javascript script wfa_jobs_fetchJobs
Thu Feb 25 10:06:58: INFO fromDate    : Thu Feb 18 2021 00:00:00 GMT-0500 (EST)
Thu Feb 25 10:06:58: INFO toDate      : Thu Feb 18 2021 23:59:59 GMT-0500 (EST)
Thu Feb 25 10:06:58: INFO Job found:
Thu Feb 25 10:06:58: INFO job.getCreationDate(): Thu Feb 18 2021 20:52:36 GMT-0500 (EST)
Thu Feb 25 10:06:58: INFO ----
Thu Feb 25 10:06:58: INFO Job found:
Thu Feb 25 10:06:58: INFO job.getCreationDate(): Fri Feb 19 2021 15:07:46 GMT-0500 (EST)
Thu Feb 25 10:06:58: INFO ----
Thu Feb 25 10:06:58: INFO <-- Displaying page JOBS
Adding 18:59:59.999 returns no jobs:

Code: Select all

Thu Feb 25 10:08:02: INFO Executing Javascript script wfa_jobs_fetchJobs
Thu Feb 25 10:08:02: INFO fromDate    : Thu Feb 18 2021 00:00:00 GMT-0500 (EST)
Thu Feb 25 10:08:02: INFO toDate      : Thu Feb 18 2021 18:59:59 GMT-0500 (EST)
Thu Feb 25 10:08:02: INFO ** No jobs found **
Adding 1 more ms to make it 19:00:00.000 returns both jobs again:

Code: Select all

Thu Feb 25 10:08:52: INFO Executing Javascript script wfa_jobs_fetchJobs
Thu Feb 25 10:08:52: INFO fromDate    : Thu Feb 18 2021 00:00:00 GMT-0500 (EST)
Thu Feb 25 10:08:52: INFO toDate      : Thu Feb 18 2021 19:00:00 GMT-0500 (EST)
Thu Feb 25 10:08:52: INFO Job found:
Thu Feb 25 10:08:52: INFO job.getCreationDate(): Thu Feb 18 2021 20:52:36 GMT-0500 (EST)
Thu Feb 25 10:08:52: INFO ----
Thu Feb 25 10:08:52: INFO Job found:
Thu Feb 25 10:08:52: INFO job.getCreationDate(): Fri Feb 19 2021 15:07:46 GMT-0500 (EST)
Thu Feb 25 10:08:52: INFO ----
Thu Feb 25 10:08:52: INFO <-- Displaying page JOBS
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#9

Postby Steve » Fri Feb 26, 2021 9:20 am

Hi Eric,

I think I will set my Timestamps to the same as yours in my database and see if I can replicate this. It might be the date comparison, but even this does not make much sense at the moment because taking into account the UTC - 5 hours..

Fri Feb 19 2021 15:07:46 GMT-0500 --> Fri Feb 19 2021 20:07:46 GMT

This does not fall between the boundary of:

Thu Feb 18 2021 00:00:00 GMT-0500
and
Thu Feb 18 2021 23:59:59 GMT-0500

I will investigate when I get some time this afternoon.

Kind regards

Steve
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#10

Postby Steve » Fri Feb 26, 2021 6:08 pm

Hi Eric,

You are right.. there is a problem with the Timezone and Date comparisons.

There is a conversion routine in the query that converts the dates into days and then compares the day conversion value:

The conversion algorithm is: date.getTime() / (24 * 60 * 60 * 1000);

It looks to me as though the from and to dates when converted are not converted correctly because:

The days are converted to days using the milliseconds of the date.

for example:

18676 = 18/01/2021 00:00:00.000

The date below gets converted to

18677 = 18/01/2021 19:00:00.000

This is because the above date is EST (UTC - 5 hours ) and 19:00 UTC is 00:00 UTC the next day... as you correctly pointed out.

The dates compared to in the database are converted as converted UTC days and this is why they are not compared correctly.

I think I need to fix this by converting the dates and timestamps all to the system Timezone.. in your case EST time and then comparing the days on this.

I will have to fix this Monday. I may need to send you a patch for this.

Kind regards

Steve
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#11

Postby Steve » Mon Mar 01, 2021 2:30 pm

Hi Eric,

I think I have fixed the getFilteredJobs Java code to filter the correct jobs based on date. I applied the timezone offset to the Timestamp read from the database before comparing the dates. This does filter out the correct Jobs by date now without any adjustments to the time in the JavaScript.

If you email me at support@ebasetech.com, I will email you a patched class file for you to test.

Kind regards

Steve
0 x

Steve
Moderator
Moderator
Posts: 414
Joined: Fri Sep 07, 2007 3:44 pm
Location: Sandy, UK
Contact:

Re: Date filter confusion/possible bug

#12

Postby Steve » Tue Mar 02, 2021 9:32 am

Hi Eric,

How did you get on with the potential fix I sent through email? If it works as expected... I will fix our code.

Kind regards

Steve
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: Date filter confusion/possible bug

#13

Postby ericb » Tue Mar 02, 2021 2:18 pm

Hi Steve,

I applied the patched class yesterday and waited until after hours to restart the apps. It appears fixed now; forms that were created in the evening appear on the correct date now:
fixed.png
fixed.png (5.5 KiB) Viewed 5167 times
Thanks for looking into this!
0 x


Who is online

Users browsing this forum: No registered users and 2 guests