CODEWITHSUNDEEP
nhibernate
dbardakov
dbardakov

Reputation: 699

Group by local date

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

Upvotes: 1

Views: 232

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

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:

Upvotes: 1

Related Questions