How to display number of days for Total Time in Status in Table Transformer

Adrian Avalos_ywpjg0

Hello. I added Total Time in Status field to Table Transformer, but it displays issues in format 2w 1 d 2h 33m. I'm looking to display this instead as number of days (rounded to nearest day)  Can someone provide a script to do this conversion or provide an alternative?

 

Stiltsoft support

Hi @Adrian Avalos ,

You may try the following workaround:

Now you set your date format (for the FORMATDATE function) and your worklog settings (for the FORMATWORKLOG function) as following:

Then you may use the following query:

SELECT *,
FORMATDATE(T1.'Time') AS 'Days 1',
FORMATWORKLOG(T1.'Time') AS 'Days 2'
FROM T*

The more complicated query

SELECT *,
ROUND((FORMATWORKLOG(T1.'Time')->split("d")->0)::number) AS 'Days 3',
CEILING((FORMATWORKLOG(T1.'Time')->split("d")->0)::number) AS 'Days 4'
FROM T*

can round the number of days mathematically ('Days 3') and round the number of days to the next day ('Days 4' that is pretty similar to 'Days 1' with the FORMATDATE function):

So, you may "play" with the queries and choose what suits you more. Hope we were able to help.