Formatting natural and work time

Owned by Pablo Beltran (Unlicensed)
Last updated: Feb 28, 2020 by Pablo Beltran
2 min read


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:

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

 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:

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)

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.

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

  • 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