[Data Grid Reactive] Time zone issue in reactive grid
Forge component by OutSystems R&D
Application Type
Reactive

Date time column should be displayed in BST, but we can see in GMT. Our database has other date time saved and it always displays some other date in grid. 



Well yes, that's what we are trying to explain : the normal way that reactive applications work (like it or not) is to assume that all timestamps in the database are in UTC, and to assume that the user wants to see them in his or her own timezone.  

This is not a problem for the reactive grid, reactive screens work like that as well.  This is not an accident, this is deliberate.

When you say 'datetime in excel and table is correct' what do you mean ?  When the excel has a datetime of "2021/07/05 15:13:00" for where was it that time, in India ?  In what timezone of India ?  You see how this is confusing.

If you want to show the datetime as stored in the database (not transformed to the timezone of the user), you'll have to trick it, maybe by turning it into a text on server side, or by somehow figuring out the timezone of the user, and substracting equal amount of hours as will then be re-added by the platform.

Dorine

Hi Rohit,

your test results are perfectly normal for a reactive application where server is in UTC and users device is in UTC+05:30.  Reactive screens and the reactive datagrid transform all datetimes to the devices datetime.

Your problem might be that while processing the excel, you are not taking into account that the datetime should be stored in the servers timezone.  So for starters, to do this correctly, you will have to know in what timezone the excel data were collected/produced.  

In other words, if it says for example "2021/07/05 15:13:00" in the excel, where in the world was it that time ??  If this was in UTC+05:30, then you'll have to substract 05:30 before storing in the database.

Dorine

Hi Dorine,

Thanks a lot, I will try this and let you know.

Hello @Rohit Kumar,

Can you let us know what component version are you using?

Can you please share some more details, so we can help?

Can you give an example on: 

  • how the date is in the database table
  • how the date is JSON format that is being sent to the grid
  • how the date is displayed in the grid cell


Thanks,

Bruno Martinho



Hi Bruno,

Thanks for your response.

Please find the attached image for all details.


Regards,

Rohit Kumar

ReactiveGridData.png

Hello @Rohit Kumar

Since we are dealing with timezones, can you please let me know a few more information:

  • How is DateTime being stored in the Database, are you using the Data Grid to store the dates, or these dates are being stored in another screen or by an integration?
  • Is your database on cloud server or is it on prem? I'm asking because if it is in cloud the the database is storing the date in UTC.

Thanks,

Bruno Martinho

Hi Bruno,

We are fetching data from excel sheet and capturing it in our database. DateTime for excel sheet and database table are same. That means this is being copied correctly from excel sheet. But, while showing in reactive grid it changes.

Database is on cloud.


Thanks & Regards

Rohit Kumar 

Hello @Rohit Kumar,  

For what I understand you have the dates in BST in the Excel and you are importing them to the database, directly, without any transformation, so the date in the database is the same that you have imported, but since this is a cloud environment, it assumes the date is in UTC. 

That is what is causing the data grid to change the dates that you have, because the data grid received the dates in UTC and shows in local timezone.

If your application deals with several timezones, does it make sense to you, that when you import the dates from the Excel, you should convert them to UTC before storing it?

Or you are only using one timezone (BST) and you don't want to do any changes in the imported date?

Bruno Martinho


Hi Bruno,

Thanks a lot, I will try this and let you know.

Hi Bruno,

I can not change datetime stamp in our database, datetime in excel and table are correct.

We can see in JSON payload, it is correct there as well, it is just getting changed at run time in reactive grid.

My observations-

  1. There might be some parameters which is changing this datetime, that I am not able to find it in service center.
  2. Reactive grid is changing the datetime.
  3. In india we can observe 5 Hours 30 min difference.
  4. In UK we observe around 1 hour of difference.

We just want to show the same data which is there in the table.

Please find the screenshot for your reference.

ReactiveGridData1.png


Well yes, that's what we are trying to explain : the normal way that reactive applications work (like it or not) is to assume that all timestamps in the database are in UTC, and to assume that the user wants to see them in his or her own timezone.  

This is not a problem for the reactive grid, reactive screens work like that as well.  This is not an accident, this is deliberate.

When you say 'datetime in excel and table is correct' what do you mean ?  When the excel has a datetime of "2021/07/05 15:13:00" for where was it that time, in India ?  In what timezone of India ?  You see how this is confusing.

If you want to show the datetime as stored in the database (not transformed to the timezone of the user), you'll have to trick it, maybe by turning it into a text on server side, or by somehow figuring out the timezone of the user, and substracting equal amount of hours as will then be re-added by the platform.

Dorine

Hello @Rohit Kumar,

Can we please let us know if the date time columns are editable or are they just for read-only? 

We can suggest something to help, but dealing with dates and timezones is not simple.

Thanks,

Bruno Martinho

Hi Bruno,

Columns are not editable, we are just displaying the data.

Thanks,

Rohit Kumar

Hi Dorine,

This worked for me, thank you so much.


Regards,

Rohit Kumar

Hi Rohit,

your test results are perfectly normal for a reactive application where server is in UTC and users device is in UTC+05:30.  Reactive screens and the reactive datagrid transform all datetimes to the devices datetime.

Your problem might be that while processing the excel, you are not taking into account that the datetime should be stored in the servers timezone.  So for starters, to do this correctly, you will have to know in what timezone the excel data were collected/produced.  

In other words, if it says for example "2021/07/05 15:13:00" in the excel, where in the world was it that time ??  If this was in UTC+05:30, then you'll have to substract 05:30 before storing in the database.

Dorine

Hi Dorine,

Thanks a lot, I will try this and let you know.

Hi!

About this discussion, I am having an issue with the Grid and displaying date time correctly.

My approach was to "trick" the database. What I am doing is before inserting the date time, I am adding the user's time zone offset to the hour, so it will save on the database the datetime I want to display to every user (there are users in different time zones and all should see the same date time).

For example, I am on Pacific time zone, so before sending to the database I am adding -420 minutes.

The insertion is doing correctly, it is saving on the database the date and time that I want every user to see. 

The issue is to display this date time on the grid. There is one specific case where it fails - when the difference from user's time ozone and the date time to be presented matches the day change. 

For example, in my case I am on pacific time zone (currently -7h). It is saved on the database 2021-07-23 17:00. So on the server action to retrieve the data and just before the ArrangeData action, I am adding the user time zone to do the trick back - in my case, the date time input before ArrangeData is "2021-07-23 10:00". But after the action, the date time transformed is returning "2021-07-24"

I am attaching a simulated example of my issue. 

Is there anything I can do to fix this?

Thank you.

GridTest.oml

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.