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

...

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

select DATEDIFF('MILLISECOND', created, updated) from ISSUES where id = 10000

...

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

select FORMATDURATIONNNATURAL(DATEDIFF('MILLISECOND', created, updated)) from ISSUES where id = 10000

...

where 1 hour has 60m minutes, 1 day has 24 hours and 1 week has 7 days.

FORMATDURATION (for users's work logs)

...

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.

...

select formatduration(originalestimate, true) from issues where id=10000

DURATION2NATURAL & NATURAL2DURATION

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

 (1.0/1000) * (8/24) * (5/7) natural = work

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

progressBar(BIGINT TimeSpent, BIGINT Remaining, BIGINT OriginalEstimate)

builds an HTML bar to display progress.

...

...

Colors:

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

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

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