Applying DATEDIFF to DateTimeOffset in SQL Server

In a recent post I described how you can upgrade columns from DateTime to DateTimeOffset and add the missing time zone offset. After you do that, you may notice that the DATEDIFF function does not work the way it did before.
For example:

DECLARE @timeInZone1 datetimeoffset, @timeInZone2 datetimeoffset

-- Two times on the same day in timezone UTC +1 (Western Europe)
SET @timeInZone1 = '2012-01-13 00:00:00 +1:00';
SET @timeInZone2 = '2012-01-13 23:59:59 +1:00';

SELECT DATEDIFF( day, @timeInZone1, @timeInZone2 );
-- result is 1 !!!

Not exactly what I had expected. DATEDIFF(day, x, y) will return the number of datelines crossed between x and y. Since both dates are on the same day in the same time zone you’d expect the function to return 0.
It returns 1 however because DATEDIFF does nothing with the time zone offset and compares the underlying UTC time in stead. In UTC the first date is actually January 12, 2012 23:00.
To get the result I had expected, I need to compare the local times not the UTC times. The trick is to convert to DateTime first:

SELECT DATEDIFF( day,
    CAST(@timeInZone1 AS DateTime), 
           CAST(@timeInZone2 AS DateTime) );
-- result is 0

This assumes however that both dates are in the same time zone. If that is not the case you can use SWITCHOFFSET to normalize on a time zone. Then cast the values to DateTime and apply the DATEDIFF function.

DECLARE @offset int;
SET @offset=120; -- UTC +2
SELECT DATEDIFF( day,
    CAST(SWITCHTIMEZONE(@timeInZone1, @offset) AS DateTime), 
           CAST(SWITCHTIMEZONE(@timeInZone1, @offset)@timeInZone2 AS DateTime) );
-- result is 0

UPDATE
Looks like I’m not the only one that was a little surprised about the behavior.

Converting to DateTimeOffset in SQL Server 2008

SQL Server 2008 introduces a new temporal data type, DateTimeOffset. This is the only data type that can hold both a date-time value and time zone offset information, so it’s perfect for holding data collected from around the world.

I’m preparing an application to support global scalability so it’s time to upgrade some columns from DateTime to DateTimeOffset. Conversion however is not exactly trivial. Unlike for example PostgreSQL, SQL Server does not add timezone offset information. You’ll have to do that yourself.

Converting DateTime to DateTimeOffset
When altering the column type to DateTimeOffset type, the timezone offset is set to 0 which is UTC.
The actual offset at any moment in time is determined by the time zone offset (i.e. +1 hour for most of Western Europe) and daylight saving time (DST). So that’s 0 in winter and +1 hour in summer.

The basic time zone offset is fixed, but the date DST starts and ends is different every year (though totally predictable).
The data set I’m updating spans a time period from early 2009 up to now, January 2012. I wrote a simple function that determines the DST time offset based on the actual date that needs to be converted.

CREATE FUNCTION fn_DSTOffset( @date1 DATETIMEOFFSET )
RETURNS INT
AS
BEGIN
RETURN
CASE WHEN ( ( @date1 > '2009-3-29 2:00' AND @date1 <= '2009-10-25 2:00' )
    OR ( @date1 > '2010-3-28 2:00' AND @date1 <= '2010-10-31 2:00' )
    OR ( @date1 > '2011-3-27 2:00' AND @date1 <= '2011-10-30 2:00' ) )
    THEN 60 -- offset in minutes
    ELSE 0
    END
END
GO

SQL Server handles time zone offsets in minutes. The actual offset from UTC is the DST offset plus the base offset. A conversion would look like this:

ALTER TABLE [Data] ALTER COLUMN [Created] DATETIMEOFFSET NOT NULL;
GO
DECLARE @baseoffset INT;
SET @baseoffset = 60; -- UTC +1 = 60 minutes
UPDATE [Data] SET
   [Created]=SWITCHOFFSET([Created], @baseoffset + dbo.fn_DSTOffset( [Created] ) );
GO

The SWITCHOFFSET method applies the specified time zone offset without altering the date and time.

Trapping spambots with honeypots in MVC

Form spam is still a real issue for any public website. Spambots are mostly very primitive scripts. They’ll scan HTML for form fields, fill all of them with some value and submit the form. Using regular textbox fields, hidden from ‘real’ users by CSS, you can trick spam bots into filling in fields a normal user would leave blank. This gives you an easy way to tell people from bots.

Unobtrusive MVC honeypots
It’s quite straight forward to implement honeypot fields in ASP.NET MVC, but of course it should be as unobtrusive as possible. So, what I’ve come up with is an action filter to help out:

[HttpPost]
[HoneypotCaptcha("UserName")]
public virtual ActionResult Questionnaire( InputModel model )
{
...
}

The action filter checks the specified field in the post data, it must be present but empty. If it’s not empty an HTTP Exception with a 403 (access denied) status code is thrown.

public class HoneypotCaptchaAttribute : ActionFilterAttribute
{
  public HoneypotCaptchaAttribute( string formField )
  {
     if ( string.IsNullOrWhiteSpace( formField ) ) throw new ArgumentNullException( "formField" );
     FormField = formField;
  }

  public override void OnActionExecuting( ActionExecutingContext filterContext )
  {
     if ( filterContext.HttpContext.Request.HttpMethod == "POST" )
     {
        var value = filterContext.HttpContext.Request.Form[FormField];

        if ( value == null || value.Length > 0 )
        {
           throw new HttpException( 403, "Stop spamming this site." );
        }
     }
     base.OnActionExecuting( filterContext );
  }

  public string FormField { get; set; }
}

Html helper

To use the honeypot action filter, insert a form field and make it invisible using CSS.

<html>
  <head>
    <style type="text/css">
     .pooh { display: none; }
    <style>
  </head>
  </body>
    <% using( Html.Form() ) {%>
    <!-- your form stuff here -->
    <div class="pooh"><%: Html.Honeypot( "UserName" ) %></div>
    <% } %>
  <body>
<html>

This injects a label and text field:

public static class FormExtensions
{
    public static MvcHtmlString Honeypot(this HtmlHelper htmlHelper, string fieldName )
    {
       return MvcHtmlString.Create( htmlHelper.Label( fieldName, fieldName ).ToString() + htmlHelper.TextBox( fieldName, "" ) );
    }
}

If using this solution in a larger app you'll probably want to include the div in the helper and setup the CSS in the main style sheet.

Fixing Sitefinity 3.7 URL handling

One of the things I’ve been wrestling with lately is Sitefinity’s somewhat outdated way of handling URL’s. There are two issues that can be fixed relatively easily: PathInfo handling and support for IIS URL rewriting module.

PathInfo
Sitefinity seems to have been built with a notion that all URL’s look like this:

www.example.com/documentation/userguide.aspx?version=2.3.6

A direct url to a page and optionally a query string (the part after ?).
However, nowadays we like pretty url’s, and for good reasons.
A prettier version of the above url could look like this:

www.example.com/documentation/userguide.aspx/2.3.6

It’s even better to get rid of the .aspx file extension, but that’s beyond the scope of this post. The bit of the URL after .aspx is known in ASP.NET as PathInfo.

If you try to implement this type of URL in Sitefinity you’ll run into some oddity in the way Sitefinity parses URL’s.

Sitefinity scans the URL to find the page name by looking for the last period (.) in the path (excluding the query string). In the first URL, that’ll work. In the second URL, it won’t. It’ll give a 404 because Sitefinity thinks you’re not requesting a resource managed by the CMS.

The remedy is to hand Sitefinity only the path to the page and the query string and strip out the additional path information. No actual information is lost since the ASP.NET Request object will still hold the original URL, including the path info. We’re just not telling Sitefinity about it…

URL rewriting
Another issue I have with Sitefinity is in the way URL rewriting is handled. Sitefinity offers it’s own Advanced URL rewriting; which basically consists of regex replacements. It works just fine. However there’s also an IIS Rewrite Module that does the same thing (and a lot more) and it’s fully integrated with IIS7 admin console for easy management.

Upon rewriting a URL the IIS Rewrite Module modifies the HttpRequest.Url property.
Sitefinity however does not get that modified URL because it works with HttpRequest.RawUrl ( also reported by Joe Groner here) which contains the URL requested by the browser.

The fact that these properties differ also enables us to detect whether a URL has been rewritten. The fix is to return the rewritten URL in the same way that Sitefinity’s own rewriting mechanism would.

Update: There is one small detail that should not be overlooked. The Uri.PathAndQuery property returns an escaped path where HttpRequest.RawUrl is an unescaped. This means the path returned by Uri.PathAndQuery needs to be unescaped otherwise encoded characters in the path (like %20 for space) will not be handled correctly. In Integrated Pipeline mode it could even mean that requests for static files will fail with 404.0 – Not found.

Implementation
Both fixes must be applied to the HttpModule used by Sitefinty as it’s main entry point: CmsHttpModule. To allow Sitefinity’s own rewriting logic to continue to work I’ve subclassed Telerik.Cms.Web.CmsHttpModuleUrlRewrite (classic ASP.NET pipeline) and Telerik.Cms.Web.CmsHttpModuleIIS7Integrated (integrated pipeline).

Full source on github: https://gist.github.com/1164613
The required changes to web.config are also listed there.

Please note that this solution is not intended for sites that use extensionless URL’s.