Skip to main content

I'm struggling a bit to understand where the platform support automatic timezone adjustments and where not. Basically I've set every SF part up to always save time elements in UTC and via domains to show local. Whereas based on the user-settings it will show at the timezone of the user. 

  • How does the platform change the timezone output? Is it done at runtime in the GUI? 
  • Does it only work via IAM or also via SF? 
  • It appears that it doesn't always work. Is there a difference between table or view? (see point 1 as well)

And then we have custom elements and generated HTML. Here we have to basically sync the timezone information from the IAM with our App.

This because the SQL function at time zone only listens to the SQL windows server timezones and not the ones registered in IAM. 

  • This also seems to be the case when it comes to languages stored in the IAM for Culture Formatting. The codes registered cannot be applied directly to the SQL server FORMAT() function. Is there a translation table available somewhere?

 

Any update on this topic? 
@Anne Buit @Jasper 

Hi Freddy,

The GUI performs the localization of the dates in the controls. This is done based on the time zone of the user.

The time zone of the user is stored in IAM. When starting via the SF, no time zone transformation is applied, the data is shown as-is (UTC).

There should be no difference in behavior between tables, views, task parameters etc.

You can easily determine the time zone of the user by querying IAM, either directly or via an application connector on application iam.

select time_zone_id
from usr
where usr_id = 'j.dough'

The available timezones are based on the Time Zone Database by iana. Browsers and operating systems keep these up-to-date and allow transformation by the GUI via these time zone identifiers, considering specific offsets and DST behavior.

You can implement transformations in database logic by storing this time zone information in your database. An interesting source is the CSV and SQL version of the time zone database. It uses unix timestamps - using DATEADD(s, @unix_timestamp, '1970-01-01') allows you to transform to SQL dates.

Note that there may be changes to time zones with little notice. Keeping this information up-to-date requires constant effort.

For culture-specific formatting, I’m not aware of any such tranaslations tables. Note that application language identifiers are free-format, so they might be specific to the languages you've configured for your applications.

Hi Freddy,

The GUI performs the localization of the dates in the controls. This is done based on the time zone of the user.

The time zone of the user is stored in IAM. When starting via the SF, no time zone transformation is applied, the data is shown as-is (UTC).

There should be no difference in behavior between tables, views, task parameters etc.

You can easily determine the time zone of the user by querying IAM, either directly or via an application connector on application iam.

select time_zone_id
from usr
where usr_id = 'j.dough'

The available timezones are based on the Time Zone Database by iana. Browsers and operating systems keep these up-to-date and allow transformation by the GUI via these time zone identifiers, considering specific offsets and DST behavior.

You can implement transformations in database logic by storing this time zone information in your database. An interesting source is the CSV and SQL version of the time zone database. It uses unix timestamps - using DATEADD(s, @unix_timestamp, '1970-01-01') allows you to transform to SQL dates.

Note that there may be changes to time zones with little notice. Keeping this information up-to-date requires constant effort.

For culture-specific formatting, I’m not aware of any such tranaslations tables. Note that application language identifiers are free-format, so they might be specific to the languages you've configured for your applications.

 

Thanks for the update. 

Could you guys consider something like https://20minuten-skandal.com/?_=%2Fmj1856%2FTimeZoneConverter%23KJWqMdlUlBnsIvkdRR%2BuhIT4 to be build into Indicium for example where the user time zone from IAM can als be returned as the equivalent TZ for windows (SQL).. so something like dbo.tsf_user_tz_iana() and dbo.tsf_user_tz_windows() 

Anyone who has the same issue of translating timezones:

select t.map.value('@other','varchar(40)') as windows_time_zone
      ,t.map.value('@type','varchar(40)') as iana_time_zone
  from @wtzx.nodes('/supplementalData/windowsZones/mapTimezones/mapZone') t (map)

 

Thanks for updating us with a good solution, Freddy!

Regarding the capability of Indicium to transform UTC datetimes to user datetimes, do you mean via the API? Or rather via a process action for the current user, specified user or a certain timezone identifier?

Please create a topic for this in the ideation section so we can explore this further.

 

Terms & ConditionsCookie settings