Date fields showing as UTC in 'HTTP Request' post to Microsoft Teams

[Steven Cotterell]

Hi,

In a test BPM I have set-up an iBridge (Cloud Automation) node to use an 'HTTP Request' to post to a Microsoft Teams channel when a Change Request is scheduled.

The problem is that the 'Scheduled Start' & 'Scheduled End' date/time fields are being sent as UTC, not in BST.

This is despite taking the advice in the WIKI to wrap any date/time field in 'square brackets' to get it to use the profiles regional settings.

Anyone have any ideas? 

I did have an idea of pushing the two 'Scheduled' date/time fields to another two 'text' custom fields in the BPM and use these in 'BODY', but I dismissed this for two reasons. 

1. I wanted to maintain easy field management
2. Also as the Scheduled date/times fields can be changed independently of the BPM at any time, I cannot always enforce updates to the custom fields. 

Thoughts anyone please?

Thanks.

 

[James Ainsworth]

Hi Steven,

The wiki markup is only applicable to displaying this within Service Manager, based on the Service Manager user's profile.  To use these dates externally you should be able to use the iBridge utility for Dates to get the correct configuration 

The formats that can used for the date and time can seen here.

Let us know if you have any questions about this date formatting. 

Regards,

James

[Steven Cotterell]

Thanks @James Ainsworth, so let me understand your response.

Are you saying that before I call the 'HTTP Request' Cloud Automation node, I will need to insert two 'Date Formatter' Cloud Automation nodes and utilise two custom fields as 'temporary' fields and then use these custom fields in the BODY for the HTTP Request?

[Steven Cotterell]

@James Ainsworth, also looking at the PHP page you linked, I'm still not clear how I would get the date/time formatted to BST.  I can see how you can use this to change the physical formatting eg '2nd February 2019' instead of '02/02/2019'.

Could you (or someone else) elaborate please?

Thanks in advance.

[Steve Giller]

@Steven Cotterell I think https://www.php.net/manual/en/datetime.formats.time.php should have the Timezone info you require

[Steven Cotterell]

Thanks @Steve Giller, saw that link on the original link that James sent me.  Still not 100% sure I follow how to make it do the conversion.

I have put something together in a BPM.  Will try it out, but I think it's going to be a lot of trial and error until I hopefully get it working.

Was hoping someone could give me the correct input & output strings to show a GMT date & time - we are already well behind on getting Hornbill communicating with Microsoft Teams for notifications on Change Requests.

[Steven Cotterell]

Thanks @Steve Giller & @James Ainsworth for your assistance so far with this, but I have hit a real road block with being able to change the time to be displayed from UTC to a BST variant.

Firstly the URL that has been shared in this thread (and others) which shows how to construct the 'Input Format' & 'Output Format' fields is not the correct one.  The 'codes' that describe the different values are incorrect.  I have found a more accurate URL which you might want to share with others in the future and update the WIKI to point people to.  The URL is https://www.php.net/manual/en/function.date.php

Secondly, all the 'Date Formatter' integration seems to do is just alter the 'look' of the date/time, e.g. change how the month is displayed from '02' to 'Feb' or change the minute to be a single digit value if the minute value is between 0-9, etc....

 I cannot find anything that will allow me to take the 'UTC Date/Time' that is stored in the DB and output it to a field that I can use in the HTTP Post as a 'BST Date/Time'.

The way it's looking at the moment, I'm going to have to give up any hope of getting a post containing a timestamp into our Microsoft Teams channel that makes sense, unless someone can come up with some magic ( @Victor or @Steve G  any ideas please....??? )

Even if we were using the Microsoft Teams integration itself I believe we would still have this issue.

The Input Format I am using is:-

• Y-m-d H:i:s

I have tried the following Output Formats (not necessarily in this order):-

• Y-m-d H:i:s eP  
• Y-m-d H:i:s TZ
• Y-m-d H:i:s e
• Y-m-d H:i:s Z

, and none of these will add on the extra hour to reflect the time in BST.

[Steven Cotterell]

I've been exploring other ways of getting date fields to be displayed in a non-UTC format and thought I had cracked it.

Sadly not - but I feel I must be close some how.

In my testing BPM I decided I would try using an 'Update Request' node to populate a TEXT field with the date field and surround this with square brackets, as per the WIKI (see below)

I am then using this custom field in the JSON payload, (by the way, note the typo in the Response Parameters section "Status Cose" instead of "Status Code" - oops - one for bugfix I think)

However, I'm still getting the field being displayed in UTC time....  aaaaarrrrggghh.  Just adding the square brackets in the 'Update Request' node just seems to literally 'add square brackets around the UTC time stamp'.

I really did not expect THIS behavior as I was using the square brackets within a Hornbill managed operation, just like you would quite successfully use square brackets around a date/timestamp when formulating an email body.

Surely this is not the right behavior for an Update Request.  You could quite legitimately want to populate a custom field with a date/timestamp and have it formatted correctly according to the locale for someone to see in a request.

Can someone from Hornbill please review and comment.  What do other people here in the community think of this??

Cheers.

 

[Steven Cotterell]

On 8/30/2019 at 2:33 PM, Steven Cotterell said:

Can someone from Hornbill please review and comment.  What do other people here in the community think of this??

Morning all.

Another week begins and dare I say it, but another week closer to the 'C' word!

.

.

.

Yes that's right, "Collaboration".   (Sorry, couldn't resist)

My latter attempts on Friday to get updates relating to requests out into Microsoft Teams looked really promising, until I hit what I really think must be a 'bug' or a coding oversight in the 'Update Request' node in the Business Process Designer.

I would really appreciate it if someone from Hornbill (and other community members) could review my last post and see what you think.

Cheers.

[James Ainsworth]

Hi Steve, 

The square bracket is a wiki markup that is only available within Hornbill.  Using the square brackets to provide an update to Microsoft Teams will not work as Microsoft Teams do not use this wiki markup.  Even within Hornbill the square brackets will only work in fields where wiki markup is enabled.

Just so I'm clear, the outstanding functionality that you are trying to achieve is converting that date held in Hornbill from UTC to a local time zone and push this to Microsoft Teams as described in your original post?  

[Steven Cotterell]

Good morning @James Ainsworth, I really appreciate your reply.

I take it then from your update that it's not possible to populate a custom field with a date/timestamp and have it formatted correctly according to the persons profile?  The date/time custom fields look to be only populate'able from a date/time picker, but say if you wanted to take the date/time a ticket was raised, add some time onto it (like for example setting an expected delivery/completion date of 3 days after a ticket was raised) and populate a custom text field with that for subsequent communications directly from Hornbill (be that viewing in a ticket or in an email sent from Hornbill), that it would only be displayable in a UTC format?

Yes, our current goal is to get the 'Scheduled Start & End' dates & times posted to a Microsoft Teams message to advise the company when a Change Request is due to be implemented, following its approval.  Also when a Change is started and finished.

Another requirement I have been challenged with is to get the details of any High Priority Incidents posted to Microsoft Teams when any are raised.  This would naturally need to include the date & time the Incident was raised.  Subsequently any notable updates which have dates/timestamps would need to go to Microsoft Teams, e.g. SLA Target breaches, Resolution Updates.

Thanks James.

 

[Steven Cotterell]

Hi @James Ainsworth,

Has there been any more thoughts on this, essentially the last two paragraphs in my post above.

@Steve G, you mentioned a possibility to code an iBridge utility for this as you have an API to do this already in the platform.  I have been asked again this morning about how soon we can get Change notifications going into Teams via the 'HTTP Request iBridge utility'  and also notifications surrounding any High Priority Incidents and their subsequents updates.

Any news would be appreciated.

Thanks.

[Steve G]

Hi @Steven Cotterell,

Rather than providing an iBridge utility to perform this, I've written a new BPM node in Hornbill Collaboration to perform this instead (which is a much better method of providing a utility of this type). Timezone and Seed Time are both optional, and if not provided then it'll provide the current date/time of the session timezone.

It will be available in the next update Hornbill ESP. Keep an eye on the announcements forum to see when this goes live

Cheers,

Steve

[Victor]

Hope I am understanding this correctly... 

On 9/3/2019 at 11:50 AM, Steven Cotterell said:

populate a custom field with a date/timestamp and have it formatted correctly according to the persons profile?

Hope I am understanding this correctly...  Formatted according to the the user profile in the UI, yes, otherwise the system only know UTC. It is the only way for a system working multiple time zones to be able to keep track of time. It is the base reference from which all other local conversions are made. So, every date and time value will be stored in the database as UTC. Therefore when it is pulled from the database it will be as UTC and unless there is something (like Hornbill code) to convert it will remain as UTC.

The wiki markup is a markup language used by Hornbill. It might or might not be used by other systems however in Hornbill it is only used for displaying purposes not to store or manipulate data. 

Encompassing a date/time value in square brackets won't achieve the transformation of the data itself but only on what is being displayed. In other words, only Hornbill UI knows it needs to transform that value in something else based on the user profile. The data, in this case, will be saved in the database having a raw value of [YYYY-MM-DD HH:MM:SSZ]. If the value for this record is used anywhere then that's the exact value that is being used, as you can see in your tests that show in MS Teams the date/time as such.

What you need first is to understand what date/time format MS Team expects. I assume is UTC, as I would find it hard to believe someone would use anything else but that to store date/time, but hey, is MS so anything is possible. On an API level, I find it unlikely/odd for MS to expect date/times in a specific timezone other than GMT +00:00...

[Steven Cotterell]

On 9/16/2019 at 6:12 PM, Steve G said:

Hi @Steven Cotterell,

Rather than providing an iBridge utility to perform this, I've written a new BPM node in Hornbill Collaboration to perform this instead (which is a much better method of providing a utility of this type). Timezone and Seed Time are both optional, and if not provided then it'll provide the current date/time of the session timezone.

It will be available in the next update Hornbill ESP. Keep an eye on the announcements forum to see when this goes live

Cheers,

Steve

Hi @Steve G,

We are seeing a problem with this API.

When the seed time is set behind current time, we get this error thrown...

Is this something you can take a look at please as all the requests which have the seed time set to not the current time are failing.

Cheers

Steve.

[Steve G]

Hi @Steven Cotterell,

This is possibly due to the manual datetime seed in the variable injection field... When setting the input to Manual, you should see the following inputs asking for year/month/date/hours/minutes:

 And only get the freetext field when using the variable injection button:

I've tested with entering the manual datetime in the correct manner, and injecting a datetime variable, and both are working for me as expected.

Cheers,

Steve

[Steven Cotterell]

Morning @Steve G,

This is the section of the BPM

The seed time is being captured on completion of the 'Start Change Implementation' activity... (see below)

and injected here ...

It looks similar to what you are doing so confused why this isn't working.

Can we chat about this maybe?

Cheers.

[Steve G]

Hi @Steven Cotterell,

I can see your issue in those screenshots... You've set the Human Task (Start Change Implementation) to have a datetime field with an ID of h_custom_21, but then you're not writing that task answer into the Incident - then attempting to use the Custom 21 field from the request - which is not auto-populated... Think there may be some confusion here - when you define a field in a Progressive Capture form that matches a Request field, then this will be populated when the Request is logged - but tasks in workflows don't work like that. With tasks, you need to do "something" with the task outputs completion...

So if you want the task answer to be used in the request, then it must be written to the request using a Requests > Update Request > Custom Fields node. If you just want to use that datetime value it in that stage of the BPM, then you can inject the Task field into the Get Local Time node directly as so:

Cheers,

Steve

[Steven Cotterell]

Thanks @Steve G, I see what you mean.  I thought the variables were stored, associated with the running instance of that request.

I have updated the BPM to pull the Task answers.  I will see how that works and post an update.

Thanks again.

[Steven Cotterell]

Hi @Steve G, that does work better but I may have found a bug.

In the Human Task where I was asking for a date/time to be recorded, I had this tick-box ticked to prefill the variable with the current date/time.

If when completing this task they don't change that date/time value, despite having the fix in place you suggested above, the BPM still fails with the same error.  If they use the date/time picker to choose a date/time, everything works perfectly.  For now I have just un-ticked that tick-box so that the field is blank and they have to use the date/time picker to set a date/time.

So essentially the API call does not like the system generated date/value as a seedtime.

Any chance this can this be looked into please?

Cheers.

[Steve G]

HI @Steven Cotterell,

I've given this a whirl and am struggling to replicate your issue  Could you export and send me your workflow definition, and I'll see if I can spot what's going on...

Thanks,

Steve

[Steven Cotterell]

Thanks.  Sent you a DM.