0

Say we have a list of activities, for different clients, saved by date. The date is saved in utc, and we know the client's timezone. We want to know the amount of activies per day, in the client's timezone, while taking into account daylight saving times.

In mysql, we could use CONVERT_TZ(A.activity_date, 'UTC', S.timezone) As LocalDate and group by the the LocalDate.

In linq2db, we could get the offset of the timezone and add it to the utc date, however, that would not take into account DST.

Is there a method in linq2db that I have no yet found that could do this? If not, is there a way create a method that would map toCONVERT_TZ in linq2db?

Mnemo
  • 103
  • 7

1 Answers1

1

A possible way to do this is to map the method CONVERT_TZ to a C# method as follows:

[Sql.Function("CONVERT_TZ", ServerSideOnly = true)]
public static DateTime? ConvertToTz(DateTime? i_Date, string i_TzFrom, string i_TzTo)
{
   throw new InvalidOperationException();
}

ConvertToTz can then be called like any linq2db method, such as

...GroupBy(a => ConvertToTz(a.ActivityDate, "UTC", "America/Montreal"))
.Select(g => new {
 Date = g.Key,
 Count = g.Count()
})
Mnemo
  • 103
  • 7