Converting the time format of the "Time spent (seconds)" column

The "Time spent (seconds)" column in the Jira "Issue worklog" table is a real data type column that captures the number of seconds logged by users on a Jira work item. To convert the logged time into the same format that you see in the “Time tracking” field or the work log in Jira, you can use this series of Visual SQL steps (replace "Time spent (seconds)" with the name of your column):

1. After your query, add a Formula column step then select Custom as the formula type.

2. Use the following SQLite formula to zero-fill rows with null values and cast the values as integers:

   1 CAST(coalesce("Time spent (seconds)",0) as INT)

3. Use the Apply formula step on the custom formula you added, select Custom as the formula type, and use this SQLite formula:

   1 concat( "Time spent (seconds)"/144000, "w ", ("Time spent (seconds)"%144000)/28800, "d ", ("Time spent (seconds)"%28800)/3600, "h ", ("Time spent (seconds)"%3600)/60, "m")