Solved

Is there an easy way to let the SF handle timezones for one server?

  • 11 May 2020
  • 3 replies
  • 116 views

Userlevel 3
Badge +9

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?). 

 

 

icon

Best answer by Harm Horstman 11 May 2020, 12:23

I have developed 2 SQL functions for this:

create function utc_to_local_datetime
(
@utc_date_time date_time ,
@time_zone_id time_zone
)
returns date_time
as
begin


DECLARE @offset_minutes AS INT

SET @time_zone_id = COALESCE(@time_zone_id, dbo.get_user_setting('time_zone_id', NULL), 'Central European Standard Time')

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)

RETURN DATEADD(MINUTE, @offset_minutes, @utc_date_time)


end
go

grant execute on utc_to_local_datetime to public
go
create function local_to_utc_datetime
(
@date_time date_time ,
@time_zone_id time_zone
)
returns date_time
as
begin

DECLARE @raw_offset_minutes AS INT

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)

RETURN DATEADD(MINUTE, -1 * @raw_offset_minutes, @date_time)

-- OTHER SOLUTION (NOT WORKING WITH NEGATIVE OFFSET)
-- RETURN CONVERT(DATETIME, @date_time) - CONVERT(CONVERT(TIME, RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 5)))


end
go

They 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.

 

 

 

 

 

View original

3 replies

Userlevel 6
Badge +14

I have developed 2 SQL functions for this:

create function utc_to_local_datetime
(
@utc_date_time date_time ,
@time_zone_id time_zone
)
returns date_time
as
begin


DECLARE @offset_minutes AS INT

SET @time_zone_id = COALESCE(@time_zone_id, dbo.get_user_setting('time_zone_id', NULL), 'Central European Standard Time')

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)

RETURN DATEADD(MINUTE, @offset_minutes, @utc_date_time)


end
go

grant execute on utc_to_local_datetime to public
go
create function local_to_utc_datetime
(
@date_time date_time ,
@time_zone_id time_zone
)
returns date_time
as
begin

DECLARE @raw_offset_minutes AS INT

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)

RETURN DATEADD(MINUTE, -1 * @raw_offset_minutes, @date_time)

-- OTHER SOLUTION (NOT WORKING WITH NEGATIVE OFFSET)
-- RETURN CONVERT(DATETIME, @date_time) - CONVERT(CONVERT(TIME, RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 5)))


end
go

They 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.

 

 

 

 

 

Userlevel 6
Badge +8

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. 

You 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.

Reply