UTC vs. Local Time
-
I have a bit of a dilemma. When a user in Hawaii enters a record with a timestamp the timestamp would represent the local time in Hawaii. When they view the report that includes that timestamp they have a reasonable expectation that the time in the report is local Hawaii time. The time is stored as UTC in the database. The user's timezone is stored in the user record. For displaying times the report has a function using TimeZoneInfo to convert the UTC time to the user's timezone. That part works flawlessly. The problem arises when I have to create parameters for Startdate and Enddate that compare columns with a date and time value. I cannot use TimeZoneInfo inside the report until AFTER the query runs and obtains the user's time zone. I tried creating a CLR but Microsoft has designated TimeZoneInfo as unsafe and it won't run unless the user is authorized for unsafe assemblies. That's not going to happen.
Here's the problem restated: The user enters a start date and end date expecting to return records based upon their time zone. The column they are querying contains both date and time values stored in UTC time. With no conversion the query would return excess records on one end and insufficient records on the other end. The database is cloud based, the report runs on a cloud based computer, and the user can be in any number of time zones. I thought about pulling records starting with one day before the start date through on day after the end date and filtering them in the report. That works for detail records but has absolutely no effect on data that is summarized in the query.The restrictions I have to work with are:
1. The application owner does not want to have the user enter or select a time zone.
2. Daylight saving time is a consideration.
3. The application stores the time and user's timezone in the database.
3. The time zone is stored in Windows format (e.g. "Central Standard Time")
4. The application will not change any time in the foreseeable future.
5. The report server database is SQL Server 2012.
6. The source database is SQL Server 2012.
7. The reports are developed using Visual Studio 2008 R2.
8. I am currently dealing with 12 different databases,
What I have tried so far:
1. A CLR using the .NET TimeZone class. It publishes and executes flawlessly but none of the methods allow a timezone as a parameter.
2. A CLR using the .NET TimeZoneInfo class. The methods in the class do exactly what I am looking for and it will publish but won't execute unless the user has UNSAFE authorization.
3. I thought I found a solution using TZDB but it uses the NODA time zone tables which would have to be converted to Windows format for each database and the handling of daylight saving time looks like it's a manual process.
Does anyone have any suggestions?
Thanks.
Ray
-
1. The application owner does not want to have the user enter or select a time zone.
This statement prompts the question of what the possibilities are to correlate different time zones to universal time without registering the offset, I for one cannot think of any (if I'm understanding the question correctly).
-
Eirikur Eiriksson (5/23/2015)
1. The application owner does not want to have the user enter or select a time zone.
This statement prompts the question of what the possibilities are to correlate different time zones to universal time without registering the offset, I for one cannot think of any (if I'm understanding the question correctly).
The application sends a session ID which the report can use to get the time zone (via a query) but not the offset. With the TimeZone class I can translate UTC to local time but it's the local time of the server the report runs on and not the user's time.
-
ray_su (5/23/2015)
Eirikur Eiriksson (5/23/2015)
1. The application owner does not want to have the user enter or select a time zone.
This statement prompts the question of what the possibilities are to correlate different time zones to universal time without registering the offset, I for one cannot think of any (if I'm understanding the question correctly).
The application sends a session ID which the report can use to get the time zone (via a query) but not the offset. With the TimeZone class I can translate UTC to local time but it's the local time of the server the report runs on and not the user's time.
That leaves few options open, maybe DBCC TIMEWARP could do the trick
-
I'm struggling to understand how you can convert a date to Local Time to display it, but not calculate UTC from Local Time to use in a query, could you explain how you can convert to Local Time to display a date correctly? Is this happening in the report or in the database?
(I am working towards something, but need to understand what you can and can't do)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
mister.magoo (5/23/2015)
I'm struggling to understand how you can convert a date to Local Time to display it, but not calculate UTC from Local Time to use in a query, could you explain how you can convert to Local Time to display a date correctly? Is this happening in the report or in the database?(I am working towards something, but need to understand what you can and can't do)
It happens in the report. I add a function to the report:
Public Function FromUTC(ByVal d As Date, ByVal tz As String) As Date
Return (System.TimeZoneInfo.ConvertTimeFromUtc(d, System.TimeZoneInfo.FindSystemTimeZoneById(tz)))
end function
The following expression displays the converted time:
=Code.FromUTC(Fields!DateTime.Value, Fields!Timezone.Value)
The input parameters d and tz come from the report dataset.
Ok, so the method I would use is this:
1) Create a dataset that retrieves the user's TimeZone from the database.
2) Create a hidden parameter that takes its default value from the dataset in 1) above.
3) When you pass the Selected dates to the main dataset, use another function = toUTC() function to convert them from "local" time to UTC using the Timezone id stored in your hidden parameter in 2) above.
This should solve the problem.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);