Instead of showing the database time, we want the GUI to show the time ‘at timezone’. Is there an easy way to handle this in the Software Factory?
Maybe the GUI could handle the datetime at timezone, if we could set the timezone for the database and the timezone for the application (runtime configuration?).
Best answer by Harm Horstman
I have developed 2 SQL functions for this:
1create function utc_to_local_datetime2(3 @utc_date_time date_time ,4 @time_zone_id time_zone 5)6returns date_time7as8begin91011 DECLARE @offset_minutes AS INT1213 SET @time_zone_id = COALESCE(@time_zone_id, dbo.get_user_setting('time_zone_id', NULL), 'Central European Standard Time')1415 SET @offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @utc_date_time) AT TIME ZONE @time_zone_id, 6)) x)1617 RETURN DATEADD(MINUTE, @offset_minutes, @utc_date_time)181920end21go2223grant execute on utc_to_local_datetime to public24go251create function local_to_utc_datetime2(3 @date_time date_time ,4 @time_zone_id time_zone 5)6returns date_time7as8begin910 DECLARE @raw_offset_minutes AS INT1112 SET @raw_offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 6)) x)1314 RETURN DATEADD(MINUTE, -1 * @raw_offset_minutes, @date_time)1516 -- OTHER SOLUTION (NOT WORKING WITH NEGATIVE OFFSET)17 -- RETURN CONVERT(DATETIME, @date_time) - CONVERT(CONVERT(TIME, RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 5)))181920end21goThey work fine, but it is still hard to let them work for al scenarios. For example a user in Japan, who wants to schedule a task in Brazil 6 months in advance.
A local time control would be very welcome. Solving the problem with SQL is possible, but sometimes has serious impact on performance.
+21I have developed 2 SQL functions for this:
1create function utc_to_local_datetime2(3 @utc_date_time date_time ,4 @time_zone_id time_zone 5)6returns date_time7as8begin91011 DECLARE @offset_minutes AS INT1213 SET @time_zone_id = COALESCE(@time_zone_id, dbo.get_user_setting('time_zone_id', NULL), 'Central European Standard Time')1415 SET @offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @utc_date_time) AT TIME ZONE @time_zone_id, 6)) x)1617 RETURN DATEADD(MINUTE, @offset_minutes, @utc_date_time)181920end21go2223grant execute on utc_to_local_datetime to public24go251create function local_to_utc_datetime2(3 @date_time date_time ,4 @time_zone_id time_zone 5)6returns date_time7as8begin910 DECLARE @raw_offset_minutes AS INT1112 SET @raw_offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 6)) x)1314 RETURN DATEADD(MINUTE, -1 * @raw_offset_minutes, @date_time)1516 -- OTHER SOLUTION (NOT WORKING WITH NEGATIVE OFFSET)17 -- RETURN CONVERT(DATETIME, @date_time) - CONVERT(CONVERT(TIME, RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 5)))181920end21goThey work fine, but it is still hard to let them work for al scenarios. For example a user in Japan, who wants to schedule a task in Brazil 6 months in advance.
A local time control would be very welcome. Solving the problem with SQL is possible, but sometimes has serious impact on performance.
Hi Andre,
In the future we want to have timezone conversion transparently handled by Indicium, but until then you will indeed have to do the conversion in your business logic yourself. We don't have a schedule for this yet.
+16You could save all datetimes in UTC and use the SQL timezone table (sys.time_zone_info) to show the UTC time in users registered timezone using SWITCHOFFSET() and the UTC offset in the system table.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
