Hi Harm,

SQL Server does support time zone conversion. For that, you need a From and a To time zone:

declare @from varchar(100) = 'Central Europe Standard Time'
declare @to varchar(100) = 'UTC'
SELECT (getdate() at time zone @from) AT TIME ZONE @to

Above an example of how it can work. For your users you will need to register the time zone they work in/or want to see the time in. The time zones stored and used in IAM cannot be directly used inside the above query I'm afraid.

Note that the platform already has this capability with the time zone set in IAM for users. DATETIME domains can either show datetimes (stored as UTC) in UTC or in the user's their set time zone. This cannot be used in the HTML card.

Thank you Mark,

In a (Read Only) HTML field we will make use of the FORMAT function to convert dates to text, this is not a problem.

The thing is that we don't want to save the users Time Zone setting at 2 places.

We could maybe make a Process Flow that runs on start-up to retrieve the user record from IAM with a HTTP connector etc..

But then there is also a difference in the time zone names used in IAM and SQL Server

For example, this does not work:

declare @from varchar(100) = 'Europe/Amsterdam'
declare @to varchar(100) = 'UTC'
SELECT (getdate() at time zone @from) AT TIME ZONE @to

A process flow/system flow could indeed work to keep them synchronized. Wikipedia has all the time zones as specified in IAM with a dateoffsettime: List of tz database time zones - Wikipedia

If you add this to a base data table, you can map the time zone to the corresponding offset and convert any UTC datetime to the intended time. The problem lies with Daylight saving time, so maybe this is better: cldr/common/supplemental/windowsZones.xml at main · unicode-org/cldr · GitHub

Does require some manual labor to get them all nicely in a table, but shouldn’t be too time consuming. Once you have this, you can write some functions to obtain the SQL Server time zone name so you can use it. That should also take care of DST I believe. Hope it helps!

Thanks Mark,

I know enough for now and we will come to a solution.

Some easy-to-use funtions available to everyone, such as dbo.tsf_user_zone(), dbo.tsf_user_local_time_offset() and dbo.tsf_user_language() shouldn't be too difficult for Thinkwise to add to the platform, maybe an idea for later.

...dbo.tsf_user_language()

Got that as tsf_appl_lang_id: Logic concepts | Thinkwise Documentation (thinkwisesoftware.com)

For the time zone, I think returning the IANA time zone is not a problem, but the SQL Server one might be a little tricky; but not impossible of course. Feel free to create an idea.

Mark,

Perfect, I didn't know this one yet.

For the time zone, we need exactly one like this. I will create an idea for that.