[Time Zone] TimeZone conversion for list of Records

[Time Zone] TimeZone conversion for list of Records

  
Forge Component
(9)
Published on 19 Sep (6 days ago) by Justin James
9 votes
Published on 19 Sep (6 days ago) by Justin James

Hi ,

I have a list of records with transaction date.The transaction date was saved in UTC dateformat in Database.After user perform the query, I should return user the query result.How am i suppose to do the conversion date from UTC to user local time?I have the user local TimeZone Identifier.How should I do this in aggregate?

Hi Johnson,

You cannot do it in an Aggregate, as that will execute on the database server. You will need to iterate through the output of the aggregate (using a Cycle tool) and either create a copy of the list with the converted transaction date (using ConvertFromUTC Action) or update directly the Aggregate's output list records... 

Hi Jorge,

Few more things to check with you

1.Is it possible to do the conversion through SQL widgets(Screen1)?By using Iteration, will it slow down the system heavily,may be 1 thousand records to show on the screen with pagination of 10 records per page?

                                                                                   Screen 1

2.I try to call other function in the sample solution(Screen 2). But it gives me error "Unknown Function 'ConvertToUTC' in expression",I learn that this is the available function which is ready to use.Can you advise what steps i have miss out? The  'ConvertFromTimeZone' can be used without any error.


Thanks for your patient for novice user like me 

Johnson Lim wrote:

1.Is it possible to do the conversion through SQL widgets(Screen1)?By using Iteration, will it slow down the system heavily,may be 1 thousand records to show on the screen with pagination of 10 records per page?

Unless your database engine has the required timezone transformation functionality, you are out of luck. SQL Server 2008+ has such a functionality (https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql) and so does Oracle (https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#NLSPG004) but I've never used them.

My suggestion is to make use of the Cycle tool properties Start Index and Maximum Iterations to make sure you only fix the relevant dates. you can get the values to assign to those properties from your List_Navigation (List_Navigation_GetStartIndex() action) and TableRecords/ListRecords (LineCount runtime property)

2.I try to call other function in the sample solution(Screen 2). But it gives me error "Unknown Function 'ConvertToUTC' in expression",I learn that this is the available function which is ready to use.Can you advise what steps i have miss out? The  'ConvertFromTimeZone' can be used without any error.

You need to Manage Dependencies (the power plug icon on your Toolbar, right below the Debugger menu), look for the TimeZone extension on the left pane of the dialog, and select the relevant Actions (and eventually required Entities or Structures) from the right pane of the same dialog.

Hi Jorge, Thanks for you advise.I have mange to convert to UTC.However i am stuck in converting it back to local time when display in list.Can you advise and provide some sample code or picture to see?I understand what you say but does not how to put it to action.

"My suggestion is to make use of the Cycle tool properties Start Index and Maximum Iterations to make sure you only fix the relevant dates. you can get the values to assign to those properties from your List_Navigation (List_Navigation_GetStartIndex() action) and TableRecords/ListRecords (LineCount runtime property)"

Hi Johnson,

Sorry, but I currently only have time to write a short set of instructions.

Converting back from UTC to local time, you just use a different action (ConvertFromUTC also from TimeZone extension - if it's not available, follow the instructions above to add the required dependency) and pass it the UTC date and the user's Time Zone Id.

"My suggestion is to make use of the Cycle tool properties Start Index and Maximum Iterations to make sure you only fix the relevant dates. you can get the values to assign to those properties from your List_Navigation (List_Navigation_GetStartIndex() action) and TableRecords/ListRecords (LineCount runtime property)"

Consider you created a GetTransactions Aggregate that fetches your Transaction entities from the database, that have a TransactionDate stored as UTC:

  1. After your Agregate, you can place a Cycle tool that goes through each record of the Aggregate;
  2. Add the ConvertFromUTC action to the flow, pass as inputs the Transaction.TransactionDate and the user's TimeZone Id, and connect the Cycle tool to it (the branch should be automatically labeled Cycle);
  3. Add an Assign tool and connect the ConvertFromUTC action to it;
  4. On the newly created Assign tool, assign to variable Transaction.TransactionDate with the value returned from the ConvertFromUTC action;
  5. Finally, connect the Assign tool back to the Cycle tool (effectively closing the cycle).

Your Aggregate will now have TransactionDate values in the user's Time Zone.

Hi Jorge, thanks for the explanation in details.

I manage to draw the flow on the Preparation action and Refresh action.

The conversion manages to take place on page load and pagination click

However just wanna check with you that is my way of doing conversion correct?I should do it both at Preparation and Refresh action.Is that correct?


        Conversion at Preparation actions so that the first page date in list table is converted when page load


Conversion at Pagination function so that when page number is clicked, the conversion will take place


Are you sure you want to do all this conversion as part of the query?

It strikes me you should rather be doing this as an expression in your grid where you display the data.

The only problem here is you can't use the standard DateTime_PrettyFormat method coz that uses the SERVER local time.  If only OutSystems provided an optional "LocalTime" or TimeZone offset to that method.


Thanks Lester and Jorge, you guys have help a lot.   


Solution

Hi Johnson,

Just to be sure, you're only converting between UTC and user TimeZone for items you are currently displaying right?

In this case, you probably want to make sure you are properly configuring the Cycle tools your are using, with a Start Index of List_Navigation_GetStartIndex(YourTableOrListRecords.Id) and the Maximum Iterations set to YourTableRecords.LineCount, so that you don't unnecessarily convert records you won't display. 

Since  ConvertFromUTC is setup as a Function, you can alternatively follow Lester's suggestion and do the conversion on the Expression that displays the date/time on screen:

instead of using GetTransactions.List.Current.Transaction.TransactionDate you can use ConvertFromUTC(GetTransactions.List.Current.Transaction.TransactionDate, UserProfile.TimeZoneId) assuming UserProfile.TimeZoneId is where you store the user's local time zone Identifier.

Solution

Hi Jorge, you are correct.

You are always helpful 

^^