Issue displaying datetime fields with timezone

(0) Share

Report

Posted on by Jose Fuentes

140

I'm facing an issue displaying the createddatetime record.

The user requires see the column in the company time zone, as we are working in D365FO the createddatetime is stored in UTC-0, we are working with UTC-6, the first solution I tried to apply was writing the next code to display the record with the company time zone.

info(strFmt("%1",DateTimeUtil::newDateTime(
                    DateTimeUtil::date(table.CreatedDateTime),
                    DateTimeUtil::time(table.CreatedDateTime),
                    DateTimeUtil::getCompanyTimeZone())));

I expected to get the CreatedDateTime with 6 hours subsctracted, but the info displays me the record with 6 hours added.

Example

The Record is stored with the next value: 2019-05-13 00:02:25

I expect the next value with 6 hours substracted: 2019-05-12 18:02:25

but the job retrives me the value with 6 hours added: 2019-05-13 06:02:25

I changed the company time zone to UTC+6 and it "worked" but is wrong because the time zone isn't the correct for the company.

If anyone has any idea what could happen, I would appreciate the suggestions.

thanks in regards

Jose.

Categories:

Development / Customization / SDK

All responses (8)

Answers (2)

Jose Fuentes 140 on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Thanks to all for the answers

I solved the problem reviewing the DateTime documentation and using the next sentence

return DateTimeUtil::applyTimeZoneOffset(this.CreatedDateTime, DateTimeUtil::getCompanyTimeZone());

I had to use the applyTimeZoneOffset insted of "create" another date time with especific TimeZone.

Regards

Jose
Suggested answer

nmaenpaa 101,158 Moderator on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

If the user simply wants the records ordered by createdDateTime, you don't even have to show the dateTime to the user.

On the other hand if you show it, it automatically shows the real exact moment when the record was created. Which makes sense.

If you start tampering with the display values, and use different timezone on different records, the displayed times are not any more in "pseudo chronological" order (ordered by the dates and times that the user sees). Since the order by criteria still uses the real (UTC) dateTime.

For example if someone created a record at 10 AM in US East Coast, and another user created a record 9 AM in the West Coast, the first record is older, but your form would show a newer created date time for it than for the other record. And the sorting would sort by the actual UTC timestamp.
Suggested answer

Mea_ 60,278 on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

You can just sort records in the datasource query by createddatetime without showing this filed on the form, in this case you will avoid confusion. However, if you want to show it on the form you do not require to do any conversion. As you know data is stored in UTC 0 and FnO converts it to user's time zone automatically. Check that all users have correct setup, because if timezone is wrong it will be converted incorrectly.
Jose Fuentes 140 on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Thanks to both for the answers

Let me explain a little more about the context, the user has a inquiry form but he wanted to see the records order by created date and time, it could be as simple as add the createddatetime column in the form, this was what we did, but other users said to us that they created some records before 6:00 PM and when they reviewed the form, the record was created "the next day", to solve this issue a new date column was created, but they want to know the time, I was working to combine a new Time column, but I realized that if I show them the createddatetime in their time zone even the date columns could be discard.

I can't only "substract" the hours because there are another companies in other time zones. and it supossed the getCompanyTimeZone method should works

I've checked the user settings as you suggested me and it is set in GMT-6

Thanks

Jose

The code I'm testing
Verified answer

Sukrut Parab 71,682 Moderator on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Info applies timezone offset. Try to see value in the debugger . Also try to apply time zone offset in the code , if you are creating a new date time in the code

see example below

DateTimeUtil::newDateTime(DateTimeUtil::date(this.CreatedDateTime),86399,Timezone::GMTMINUS0800PACIFICTIME);
Verified answer

Satish Panwar 14,653 Moderator on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Most of it should still be valid.

docs.microsoft.com/.../gg837448(v=ax.60)
Suggested answer

Satish Panwar 14,653 Moderator on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Hi Jose,

See if you can achieve with what Nikolaos mentioned above. The setting is at user > options level. If you can't for any reason, you need to check what value are you getting in code from table.createddatetime. System might be automatically applying your timezone settings to it. There are other methods in DateUtil class that you should look at applytimezoneoffset etc.
Suggested answer

nmaenpaa 101,158 Moderator on at

Like (0)

Report

RE: Issue displaying datetime fields with timezone

Why not set the user timezone to be the same than the company timezone? Then it would be shown correctly without any development