cancel
Showing results for 
Search instead for 
Did you mean: 

Looking for help to convert the timezone from UTC to Local for a column in collection

Miya
Level 5
Hi,

   I am trying to change the time zone from UTC to Local for a column in collection, i tried to use the object UTC to local but it doesn't working for for collection column, can some one help to sort this issue.

------------------------------
Miya
------------------------------
1 BEST ANSWER

Helpful Answers

Hi Miya,

To achieve your use case I have created the below sample workflow:

17781.png
Let's understand each component, first I am using an action called as 'Get Time Zones' from 'Utility - Date and Time Manipulation' which will return me a collection of all the available time zones configured on my operating system. As of now, you can see there are a total of 141 such time zones which are available with me from the returned collection:

17782.png
So, you need to first figure out what is the local time zone that you have which in my case is 'India Standard Time'. Once, you have determined the time zone ID, you can set a data item with the same initial value or pass it from some configuration file or environment variable as suited per your process. So I call this data item, 'Timezone ID' which holds the Time Zone ID value of my locale:

17783.png

Next I am using a 'Filter Collection' action from 'Utility - Collection Manipulation' VBO which will return me only the row with ID value that I've set in my data item. My input collection will be 'Time Zones' which was returned to me by the prior action and the filter expression is: "ID ='" & [Timezone ID] & "'"  and I am storing the result in a separate collection called as 'Required Timezone' which will have only one row corresponding to the Time zone ID that we passed in the filter criteria: 

17784.png

NOTE: This step you and previous step if you want you can discard as well if you want now. Since these steps we are using just to check the time zone ID which I can even directly pass going ahead. However if you discard it then just pass the data item: 'Timezone ID' directly in the UTC To Local action as shown ahead.


Once you have obtained the Time Zone ID, now use a Loop stage to iterate through the collection where my date column in UTC format is available. In my case I have take two default columns called ID and Date out of which I will be converting the Date column from UTC to my local time zone value. This collection is called 'Inputs'

17785.png

Now inside the loop stage, I have used 'UTC To Local' action from 'Utility - Date and Time Manipulation' which will covert any given datetime value into a local date time format based on the time zone ID provided. Here, I am passing the Date column value of my current row of the 'Inputs' collection and I will be passing the ID column value of my 'Required Timezone' collection which I got after filtering . I will store the output to data item of type 'DateTime' called 'Local Date Time':


17786.png

17787.png
After this I will use a calculation stage to set the value of 'Local Date Time' data item to the date column of my Inputs Collection:

17788.png

Before running the workflow, the values I had were like below:

17789.png

After running the workflow, the values are as follows:

17790.png



------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.

View answer in original post

2 REPLIES 2

Hi Miya,

To achieve your use case I have created the below sample workflow:

17781.png
Let's understand each component, first I am using an action called as 'Get Time Zones' from 'Utility - Date and Time Manipulation' which will return me a collection of all the available time zones configured on my operating system. As of now, you can see there are a total of 141 such time zones which are available with me from the returned collection:

17782.png
So, you need to first figure out what is the local time zone that you have which in my case is 'India Standard Time'. Once, you have determined the time zone ID, you can set a data item with the same initial value or pass it from some configuration file or environment variable as suited per your process. So I call this data item, 'Timezone ID' which holds the Time Zone ID value of my locale:

17783.png

Next I am using a 'Filter Collection' action from 'Utility - Collection Manipulation' VBO which will return me only the row with ID value that I've set in my data item. My input collection will be 'Time Zones' which was returned to me by the prior action and the filter expression is: "ID ='" & [Timezone ID] & "'"  and I am storing the result in a separate collection called as 'Required Timezone' which will have only one row corresponding to the Time zone ID that we passed in the filter criteria: 

17784.png

NOTE: This step you and previous step if you want you can discard as well if you want now. Since these steps we are using just to check the time zone ID which I can even directly pass going ahead. However if you discard it then just pass the data item: 'Timezone ID' directly in the UTC To Local action as shown ahead.


Once you have obtained the Time Zone ID, now use a Loop stage to iterate through the collection where my date column in UTC format is available. In my case I have take two default columns called ID and Date out of which I will be converting the Date column from UTC to my local time zone value. This collection is called 'Inputs'

17785.png

Now inside the loop stage, I have used 'UTC To Local' action from 'Utility - Date and Time Manipulation' which will covert any given datetime value into a local date time format based on the time zone ID provided. Here, I am passing the Date column value of my current row of the 'Inputs' collection and I will be passing the ID column value of my 'Required Timezone' collection which I got after filtering . I will store the output to data item of type 'DateTime' called 'Local Date Time':


17786.png

17787.png
After this I will use a calculation stage to set the value of 'Local Date Time' data item to the date column of my Inputs Collection:

17788.png

Before running the workflow, the values I had were like below:

17789.png

After running the workflow, the values are as follows:

17790.png



------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.

Hi,

  Thank you this helped me, i lopped the collection and used date time manipulation object to convert the required time zone.

Miya

------------------------------
Miya
------------------------------