The worklogDate function in the JQL search is showing issues where work was logged 1 day later

Summary

The worklogDate function allows Jira users to search for issues where some work was logged at a specific date. For example, the JQL query below will search for Jira issues were work was logged on Feb 22nd 2022:

In some cases, this JQL query might return issues where work was logged 1 day later, as shown in the screenshots below:

• The screenshot below shows that work was logged on Feb 25th:

  

• However, when searching for issues where were was logged on Feb 24th, this issue mysteriously appears:

  

Environment

Any Jira version on 7.0.0 and above.

Diagnosis

• Look for the actual date/time where the work was logged for the problematic ticket directly in the Database, by using the following SQL query. ⚠️ Replace ABC and 123 with the project key and issue number in the project.

  1 2 3 4 5 SELECT created, updated, startdate, timeworked FROM worklog WHERE issueid= ( select j.id from project p, jiraissue j where p.id=j.project and j.issuenum=123 and p.pkey='ABC' );

• The query below shows the time when the work log was added to the DB (in the created column), for the issue shown in the example. What we can see is that the work log was actually added on Feb 24th at 14:41:47 in the UTC+1 timezone, which is the Jira system timezone:

  

• When checking the timezone of the user who logged work (by going to the User Avatar > Profile page), we can see that the user is in a timezone which is "far in the future" (for example UTC+11 in the screenshot below) compared to the Jira system timezone:

  

Cause

The reason why the JQL search might show issues where work was logged on a different date than the date used in the search is because of the following reasons:

• the JQL search uses the Jira server timezone to search for work that was logged on a specific day, and does not take into account the timezone of the Jira user who initiated the search

• in the case where there is a big different between the Jira server timezone (for example UTC+1) and the user timezone (for example UTC+11), if the user logs work on a certain day, it does not necessarily mean that the work will be logged for that day in the Jira server timezone. Depending on the gap between the 2 timezones, it could be that the worklog entry will be added with a timestamp from 1 day earlier or 1 day later in the worklog table in the database

Solution

There is no workaround for this issue, since the observed behavior is expected due to how the logged work is stored in the Database, and how the JQL search works.