Group by local date

Question

My scenario: NHibernate is used to populate some Grid, located on web page. Database is Ms SqlServer 2008/2012 and is not going to be changed in the future year(s). I need to have rows grouped by DateTime column (stored as Sql datetime type in UTC), but only by Date part. Moreover, since clients can have different time zones, I need to group by date that has an offset.The select is something like this:

SELECT 
  CAST(CONVERT(char(8), 
       [dbo].fn_LocalDayFromUTCDate(
             this_.datetime1 /* this is CreationTime property mapped by NHibernate to column datetime1 */, 
             -240 /* hour offset and some other TimeZone params */), 
       112 /* timestyle */) AS datetime) as _date 
  FROM SomeTable as this_ 
  GROUP BY CAST(CONVERT(char(8), 
      [dbo].fn_LocalDayFromUTCDate(this_.datetime1, -240),112) AS datetime)

UPDATED Folowed by Radim's answer, I did manage to register custom dialect with custom sql-function like this:

public class CustomMsSql2008Dialect : MsSql2008Dialect
{
    public CustomMsSql2008Dialect()
    {
        RegisterFunction(
            "toLocalTime",
            new SQLFunctionTemplate(
                NHibernateUtil.UtcDateTime,
                "CAST(CONVERT(char(8), [dbo].fn_LocalDayFromUTCDate(?1, ?2),112) AS datetime)"));
    }      
}

public static class CustomProjections
{
    public static IProjection LocalDate(IProjection datePropertyProjection, int offsetInMinutes)
    {
        return Projections.SqlFunction(
            "toLocalTime",
            NHibernateUtil.UtcDateTime,
            datePropertyProjection,
            Projections.Constant(offsetInMinutes));
    }
}

That I been able to use like this:

var groupProjection = Projections.GroupProperty(
    CustomProjections.LocalDate(
         Projections.Property("CreationTime"), 
         -240));

Problem is, it is generate the GROUP BY part with missing second parameter:

SELECT CAST(CONVERT(char(8), [dbo].fn_LocalDayFromUTCDate(this_.datetime1, @p1),112) AS datetime) as y1_ 
FROM SomeTable this_  
GROUP BY CAST(CONVERT(char(8), [dbo].fn_LocalDayFromUTCDate(this_.datetime1, ?),112) AS datetime) 
    ORDER BY y1_ asc

score 1 · Accepted Answer · edited May 23 '17 at 11:50

A straightforward conversion of the above SELECT and GROUP BY into the SqlGroupProjection could be like this:

var criteria = session.CreateCriteria<SomeEntity>(); // mapped to SomeTable

criteria.SetProjection(
    Projections.ProjectionList()
        .Add(
            Projections.SqlGroupProjection(
                " CAST(CONVERT(char(8), " +
                "     [dbo].fn_LocalDayFromUTCDate(  " +
                "           this_.datetime1 /* this must be mapped by NHibernate column */,  " +
                "           -240 /* hour offset and some other TimeZone params */),  " +
                "     112 /* timestyle */) AS datetime) ",

                " CAST(CONVERT(char(8), " +
                "  [dbo].fn_LocalDayFromUTCDate(this_.datetime1, -240),112) AS datetime)",
                new string[] {}, // could be empty, while not used for  
                new IType[] {} // transformation
                )
        ));

var list = criteria.List<object[]>();

Also check:

selecting only max clause without group by properties in subquery using Nhibernate

thank you, Radim, I was coming this way, now have stocked at: how to replace concrete column name `this_.datetime1` with a `Projections.Property(myGroupPropertyAlias)` and let NHbm generate it for me? — dbardakov, Feb 27 '14 at 11:55
I would say, that only think we can do here, is to replace the *this_* with `{alias}`. That would be later replaced by NHibernate. The other option could be to extend the dialect with new SQL Function. It could later be used with Propety Projection. An example could be found here: http://stackoverflow.com/questions/21725327/. Please, check this, because in your case, that would be the most appropriate and best maintainable — Radim Köhler, Feb 27 '14 at 12:24

Group by local date

1 Answers1