Need help converting Date passed via WebHook

Question

Hello,

I have a WebHook configured in Jira to send the Sprint created, updated, started, closed, deleted events to an Automation I created.

The Automation receives the data in a smart value as: {{webhookData}}

An example of data received for the Sprint updated is below:

{webhookEvent=sprint_updated, oldValue={id=3202, self=https://xxxx.atlassian.net/rest/agile/1.0/sprint/3202, state=future, name=Test Sprint 12346, originBoardId=260}, sprint={id=3202, self=https://xxxx.atlassian.net/rest/agile/1.0/sprint/3202, state=future, name=Test Sprint 12346, startDate=2021-12-13T07:00:00.000Z, endDate=2021-12-20T07:00:00.000Z, originBoardId=260, goal=}, timestamp=1639243910842}

As you can see, the value I receive for startDate/endDate is formatted as:

2021-12-13T07:00:00.000Z

I am not totally certain why, but I am having difficulties trying to use date functions (see: here and here  and here and here ) on the resulting smart value -- e.g. {{webhookData.sprint.startDate}}

As I really want to convert this from UTC to PST8PDT.  I have tried just above everything.

According to the documentation,

jiraDateTime1979-11-01T06:23:12.0-0500

But, what I am receiving is formatted slightly differently (I am receiving a *.000Z instead of a *.0-0500).  Is this causing the problem?  Any ideas what the proper way to handle this is?  Should I strip off the characters to the right of the dot "."?

Thanks.

As a note, what I am doing is creating a "record" of each Sprint in a new Jira Project (called Administrivia).  This project includes custom fields for: Sprint Id, Sprint Name, Sprint Start Date, Sprint End Date, Sprint Goal plus statistics about the sprint (Starting count of PBIs, Starting Sum of Story Points, Ending count of PBIs, Ending Sum of Story Points, Average Cycle Time) - plus Team Name (which, by standard, is embedded in the Sprint Name), Sprint Number (which, by standard, is embedded in the Sprint Name).

This will allow me to create Dashboard of various slices of data.  As an example - What are the Sprint Goals of the Active/Open Sprint across 30 Teams operating in parallel Sprints?  What is the trend of Average Cycle Time for Squad 1 over the past 20 Completed Sprints.  Etc.  I am almost completed, I just need to figure out this last thing (hopefully).

Doug Levitt_tunij3 · Answer

Hi @Mykenna Cepek , @Gareth Cantrell

Thanks for tag teaming on the answer.  I fully missed the fact that I had to convert the string to a date.

I ended up creating two variables as follows:

sprintStartDate
{{webhookData.sprint.startDate.replaceAll("T", " ")}}

adjustedSprintStartDate
{{sprintStartDate.todate("yyyy-MM-dd HH:mm:ss").convertToTimeZone("PST8PDT")}}

I imagine I could have combined this, but I think this is more readable.

As a thought, wouldn't it be nice if there was a way to add comments (at various places) to the "code".

Mykenna Cepek · Answer

@Gareth Cantrell is very close with his answer. But it doesn't take into account the time portion of the original timestamp string (which I assume is important, since the OP cares about the timezone).

@Doug Levitt, something like this might get you closer to what you want:

This seems to work when the 'doug' variable is "2021-12-13 07:08:09.345Z", although the fractional seconds revert to a single digit.

Inserting a convertToTimezone() before the format() should get you what you want.

Some notes:

* Note that I have replaced the "T" character between the date and time portions of the 'doug' variable value with a space character. That is important (and how to do it is left as an exercise for the reader; hint). I wasn't able to convince the toDate() function (with a format argument) to ignore it.
* Curiously, using toDate() with no format argument has no trouble with the "T" character -- but it drops everything after the "T" from the result.
* I did NOT have success trying to store the result of the toDate() function in a variable, and then using additional date functions on that variable. That smells like a type problem (bug?) to me. That's why the above chains the functions together.
* I'm assuming the 000Z your OP represents fractional seconds rather than a timezone offset. Normally timezone values are 4 digits, sometimes with a nested colon, usually with a prefix +/- sign.
* I did not have luck using the canonical "V" format to properly recognize the "Z" as "+0000", but it looks like that is the result anyway (even if it is ignored as shown above).

Please feel free to post what finally works for you!

Gareth Cantrell_7nagel · Answer

Hi @Doug Levitt

The dates from the webhook are plain text and will need to be converted to dates before you can perform any date-related functions on them.

This is because the Automation engine is not able to deduce that a JSON field is a date or just plain text.

Using:

{{webhookData.sprint.startDate.toDate.convertToTimeZone("PST8PDT")}}

should do the trick, or to continue in the vein of your example, create a smart value variable using

and then perform any further date-related calculations on the smart value.

Regards,

Gareth