Hi Dennis,

The time zone configured for the user only has effect when the stored date-and-time fields are explicitly marked as being expressed in the UTC time zone.

Domains | Thinkwise Documentation

By default, they are configured to be shown as stored, without the user time zone offset being applied.

The language/culture should only affect the format of the date and time.

I have a question regarding how time zones are handled in our application and how this can be implemented consistently.
 

Anne Buit:

"The time zone configured for the user only has effect when the stored date-and-time fields are explicitly marked as being expressed in the UTC time zone."

Our configuration:

1. Domains > Datetime > Time zone: set to "Local"
2. IAM > Users > Time zone: set to "Europe/Amsterdam"

My expectation:
When a Dutch user creates a record on December 5, 2024, at 13:30, this timestamp would be stored as 13:30 in the SQL database and displayed in the user interface in the Europe/Amsterdam time zone. However, I am currently observing a different result.

Possible issue:
We populate our trace fields (such as insert_date and update_date) using the SQL function GETDATE(). This function uses the server's time zone, which leads to inconsistencies when users operate in different time zones. I believe it would be more logical and consistent to use SYSUTCDATETIME() instead, ensuring that all timestamps are stored in UTC.

Questions:

1. Is it correct that using SYSUTCDATETIME() is preferred over GETDATE() to ensure consistency?
2. Does the conversion from the database value to the local time zone (as configured in IAM) assume that the stored value is in UTC and then calculate the appropriate time zone offset based on this?

I would appreciate any insights or best practices on this matter. 

When using the domain time zone setting “Local”, the value will be assumed to be an UTC date and time. You should indeed use the SYSUTCDATETIME() function to populate the trace fields.

The value will be transformed by the GUI from the UTC time to the time zone of the user. This also works vice-versa, when the value is manually entered by the user.

Does this answer your questions sufficiently?

Hi ​@Dennis van Leeuwen, did Anne his responses answer all the questions you have regarding this topic, or do you require further assistance?

Anne's answer does indeed answer my question.

We practically have trace columns on 98% of our data model where there is an insert and update timestamp that has been populated with GETDATE() so far. Changing the functionality to use the SYSUTCDATETIME() is easy. I will especially have to consider what to do with historsic records because then, based on the insert user, it must be traced in which timezone the insert took place and a query then changes the date to UTC.

I will at least then (temporarily) disable all update triggers to avoid unwanted triggering.

DECLARE @sql NVARCHAR(MAX) = '';

-- Genereer de DISABLE TRIGGER statements voor alle UPDATE triggers
SELECT @sql = @sql + 'DISABLE TRIGGER [' + t.name + '] ON [' + s.name + '].[' + t.name + ']; '
FROM sys.triggers t
INNER JOIN sys.tables s ON t.parent_id = s.object_id
WHERE t.is_disabled = 0 -- Alleen ingeschakelde triggers
AND t.type = 'TR' -- Alleen triggers
AND EXISTS (SELECT 1 
            FROM sys.trigger_events te
            WHERE te.object_id = t.object_id
            AND te.type = 'U'); -- Alleen triggers die reageren op UPDATE

-- Voer de gegenereerde SQL uit
EXEC sp_executesql @sql;

If you have any tips for this convert action, they are welcome.

Does this also work for the Windows GUI or only Universal (as stated in the documentation)?
We have the same ‘issue’ with multiple time zones, but the Windows GUI is still the most used GUI at the moment.