When your DotNetNuke Portal is in a different timezone to the IIS Web Server and / or SQL Server

Don't get me wrong - the fact that DotNetNuke even has timezone features puts it far ahead of many, many websites in the world.  The fact is most of us will make an underlying assumption that most of our users, stakeholders, servers and everything will be in the same timezone, right?

Well, no - in countries like the USA, Canada, Australia, Russia, users could span multiple timezones - and that is when you are making the assumption that all your users are in your own country, which is not a sound assumption anyway.  No, you need to be timezone-savvy.  Luckily DotNetNuke has this all figured out and allows each registered user to have a different timezone setting to the Portal.

I've identified a shortcoming (and I could be wrong here) which is : there seems to be an underlying assumption that the Host portal (portal 0) is on the same timezone as the installed web server. 

With many DotNetNuke websites hosted on whichever host is offering the best service/deal, the web servers can be just about anywhere, and in any old timezone.  As many of them are shared or on Net time synchronisation, changing the timezone on the server isn't going to work.

For the most part, the server date doesn't really matter that much - but one area where it does pose a problem is in the website logs.  If you are running a site for a client (like I do) and are allowing website log reporting on the site - well, it's a nifty feature and one that tends to get a bit of use.

Here's the scenario : Website owner is having lunch at their desk, and decides 'I might go on and view my website stats'.  So they log on, bring up today's statistics and..... nothing.  Not because nobody has been on the site - but becase 'today' is a relative concept, and the web server might be on the West Coast of the USA, one of the 'latest' timezones in the world.  If our intrepid Website owner is in New Zealand which has one of the 'earliest' timezones, well, they are going to have to wait the better part of 24 hours for that query to work, or indulge in some brain gymnastics working out the timezone offset (anyone who has received an international call at 3am will attest to the fact that most people are not good at working out timezone differences).

Another problem with the Website log is the report that shows Pages by Hour of day - this lists all the hours from 0 - 24, and shows how many requests have been made in each of those hours in the reporting period.  Because the logs are done on server time, it might look a bit strange to a website owner, or worse, bring about a bad marketing decision (hey! all my visitors are buying products at 3 am.  I might advertise on late night TV!)

The Solution

The solution is pretty simple really, and requires very little modification to the DotNetNuke code, and none to the data.  Sounds like a good solution?  Read on...

First, to get the solution, here's the problem. The website log reports are the results of a set of stored procedures in the DotNetNuke database.  These are named dnn_GetSiteLog1 => dnnGetSiteLog12. (numbers 10 and 11 are missing, presumably dropped functionality?)

These stored procedures all have one thing in common - the date range with which they filter the data in the SQL Select statement :

The problem occures because the dnn_SiteLog column is datestamped with the SQL Server Time, and the @StartDate and @EndDate parameters are passed in from the values supplied by the user, who naturally specifies these in their local timezone (without realising, mind you).

To fix the problem, all we need to do is convert the user supplied parameters to match the timezone of the dnn_SiteLog column.  This is easily done by subtracting the difference in timezones between the SQL Server time, and the Portal timezone.  Thankfully the Portal timezone is easily accessible:

Talking through these lines of code one by one:

Now, modifying the WHERE clause in the SiteLog stored procedures can be done, using the value of @timeDiff:

The above statement shows the modification to the dnn_SiteLog select clause - we now convert the @startDate and @endDate parameters back to the same timezone as the SQL Server by subtracting the time difference between what timezone the portal runs in, and what timezone the SQL Server runs in.

That takes care of selecting the correct data, but we are not finished yet, as the  display data also needs to be corrected so that the user sees the results as per their own timezone.

The date column in most of the reports is selected with this statement:

select 'Date' = Convert(varChar, [DateTime],102),

This statement returns the dnn_SiteLog.DateTime column in a more ‘user friendly’ format, which is what the ‘102’ is for.  See the SQL Server books online for more information on date time conversion formats.  The square brackets are needed because DateTime is a SQL Server reserved word, and shouldn’t have really been used for a column name.

However, this returns the time in the SQL Server timezone values, which is incorrect as far as the user of a Portal in a different timezone is concerned.  To fix it, we must add back the difference in the SQL Server Timezone and the Portal Timezone.  This is done with the following line of code:

select 'Date' = Convert(varChar,DateAdd(mi,@timeDiff,[DateTime]),102),

Here we add back the @timeDiff value to the dnn_SiteLog.DateTime field, which results in the user seeing the datetime values in the correct timezone for their portal.  This is done using the DateAdd function, and specifying minutes as the type of addition (‘mi’).  VB programmers will recognise the function but it is slightly different in SQL Server format.

But we’re not quite finished yet: most have a grouping statement to summarize the data, and most are grouped by date (and/or time).  The original code is:

group by Convert(varChar, [DateTime] ,102)

So we need to modify the GROUP BY clause as well, to make it match our SELECT clause:

group by Convert(varChar,DateAdd(mi,@timeDiff,[DateTime]),102)

Putting it all together

If you SQL Server is in a different timezone to your portal , this is what you need to do to each of the Site Log report stored procedures:

declare a new variable to hold the difference in the portal timezone and sql server timezone

set this variable to the total difference between the two timezones

modify the WHERE Clause of the SELECT statement in the stored procedure, so that the timezone difference is subtracted from the input user parameters of @startDate and @endDate

modify the SELECT statement DateTime column to add back the timezone difference

modify the GROUP BY statement (if necessary) to add back the timezone difference to match the SELECT statement.

This needs to be done for all 10 stored procedures.

The downloadable script file contains all the modifications for the DotNetNuke 4.02 database version.  To run, simply download it and use a  SQL Query tool to run the changes against your DotNetNuke database.

PLEASE NOTE : This information is provided in good faith but may not be suitable for your installation and database.  By downloading and running the following script you are doing so at your own risk.  Always make a backup of your database before running any script, as there is no ‘UNDO’ function.  This script was designed for a standard DotNetNuke 4.02 database, and may not suit other versions.  Please make sure you understand what you are changing before running the script, we can’t help you if you modify the database in such a way that your website loses data or no longer works.