March 22, 2009 at 9:01 pm
Hi there,
I'm in the process of designing a simple data mart/warehouse against which user queries can be run (ie no SSAS cube or other layer). So far, my requirements for storing datetime datatypes are to have them in UTC. I also have a date and a time dimensions so that I can see the time slice of the timestamp and the date of the timestamp in a convenient fashion. This makes it easy to say "give me all facts for monday" for example.
However, after thinking this was all going really well, I realized that while displaying localized data in SSRS is not much of an issue (using toLocalTime in the report layout), my issue is concerning the use of my date dimension when the user is not working in UTC. In that case, if my ETL process looks up a Date ID FK based on the datetime value, it's obviously for UTC. A client wanting to request that data in another time zone would have to look up the Date ID they are actually interested in.
I am concerned this design is just broken once you have a client working say in pacific time. I'm still at the design stage so I haven't really had time to test it but I just have a bad feeling that my date dimension may not be very useful in that case...
I am looking for suggestions, ideas and advice on handling time zones using date/time dimensions if there are any out there? I have read this post already http://www.sqlservercentral.com/Forums/FindPost217946.aspx which makes sense in advising to let the client handle all time conversions. I am just wondering how well SSRS handles these types of conversions for input parameters?
Many thanks,
Greg
P.S: I apologize if this sounds confusing, I just have a bad feeling about this but the more I think about it, the more I get confused :(. I looked into Project Real but the documentation makes no mention of this.
March 23, 2009 at 1:49 pm
I haven't had a lot of experience with UTC dates/times and what is considered to be good design practice, but do have a few comments based on a document management system we recently implemented.
The EDRMS stores all dates/times in UTC and, given that it could be used across multiple timezones (though not for us), this seems like a good idea in principle but has caused a number of issues - it might just be a badly implemented solution in this particular case.
First issue - the EDRMS creates numerous audit log files (.txt) in which it uses UTC dates/times. Troubleshooting is problematic because we can't just read a log-file and see when something happened. Entries for today have yesterdays date/time (-11 hours), yet can appear in a log-file dated today OR yesterday - all todays auditing gets separated into two separate files.
Second issue - because the app handles conversion to local timezone, any queries/reports from SQL must include logic to handle this (it might be easier with SQL2005 and CTEs, but we are on SQL2000). What I'm not sure about is how to properly handle time variances like daylight savings - some of the year we are UTC+11 hours, and the rest of the time UTC+12.
To complicate things further, we (New Zealand) extended daylight savings by 3-weeks last year, so 2008 onwards is different from pre-2008. At least we don't live in Australia, where all the states observe daylight savings, except for Queensland.
Like I said, I'm new to using UTC and there will be, no doubt, better informed people out there with ideas/solutions. UTC seems like a good idea, but may not be as straight foward as it appears.
Chris
March 23, 2009 at 2:23 pm
Hi Naked Ape,
Well that is my issue here. It's one thing to view real time data adapted to your time zone, it's one thing to query it that way. and while CTE may help in that (I'm in SQL 2005), it's not much easier when trying to design a warehouse that is "user-friendly" 🙁
So far I'm thinking that I'll have both UTC and local time stamps so that if I need to compare data across time zones, I'll have to use the UTC timestamp and for "local" data I'd look at the local time stamp.
I'm in Canada and our DST also changes once in a while so it's not even like the dates can be computed following a predictable pattern 🙁
Greg
March 23, 2009 at 2:51 pm
The simple ideas are often the best - storing both UTC and local time, or the difference between them, may not be the ideal model but there has to be a balance between best practice and usability - or theory and reality. 😉
I'm no physicist, but as the fourth dimension in our ever expanding universe I guess date/time will never be quite as simple as it seems. 😀
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply