The packed H2 database engine provides a lot of built-in functions to help users to format the data output as well as to perform calculations. A complete list of the supported functions is available at:

http://www.h2database.com/html/functions.html

Work vs Natural time units

Timestamp and date columns are always in natural days (24 hours/day and 7 days/week) so, to calculate the interval between two days, you can use the H2 built-in DATEDIFF function:

I.e: interval time between the issue modified and created dates:

 It returns the difference in milliseconds. You might want to show the results in a human-readable string like: "7w 5d 12h 19m" similar to the JIRA pattern.

However, Jira stores users' work logs in busines/work time (a long number in seconds)

FORMATDURATIONNATURAL (for time)

The plugin's built-in  FORMATDURATIONNATURAL(BIGINT time) function expects a time difference in natural milliseconds:

FORMATDURATION (for users's work logs)

On the other hand,  JIRA stores users' work logs as well as other work time units like estimated time, spend time, remaining time, etc. in a long number value in seconds. Furthermore, they are based on the working calendar configuration on JIRA usually: 8 hours / day and 5 days / week.

The plugin's built-in FORMATDURATION(BIGINT time, BOOLEAN shortPattern) function invokes the Jira API to transform working time units into human-readable strings. The result is in business time units.

The second parameter allows switching the output among the long output: 3 weeks, 2 days, 1 hours and the short output: 3w 2d 1h. It also takes into consideration the working days calendar.

It just invokes the JIRA Java API.

DURATION2NATURAL & NATURAL2DURATION

You might want to transform natural time into work units (or vice versa) with this rough formula:

Note the decimal in at least one number (1.0 in this case) to force to work with double rather than the long Java type. Otherwise, you might get 0 (zero).

The formula transforms milliseconds into seconds (1.0/1000), natural into work hours (8/24) and natural weeks into working weeks (without weekends). The formula works pretty fine for a large period of times (like months)  and quite bad for very short periods (like minutes)

We provide two built-in functions to make such transformation:

• DURATION2NATURAL 

• NATURAL2DURATION

They allow converting time intervals from natural to business units and vice-versa according to the rough formula above.

Progress bar

The function

builds an HTML bar to display progress.

Open

Colors:

• TimeSpent is painted in RED or GREEN depending if it is OVER or UNDER the OriginalEstimate

• Remaining is painted in LIGHT RED or GRAY if it is OVER or UNDER the OriginalEstimate

• OriginalEstimate is painted BLUE if it exceeds > TimeSpent + Remaining