Support for CONVERT_TZ() function with appropriate translation.

[crozone]

Preface

MySQL supports a function called CONVERT_TZ(dt,from_tz,to_tz), which allows datetime values to be converted from a given source timezone into a given target timezone. The timezone values can either be specified as a fixed offset (eg +10:00), or more interestingly, an IANA timezone identifier (eg. Australia/Melbourne). This is particularly useful when writing database queries that perform GROUP BY over days/weeks/months, since the GROUP BY query can be run over the appropriately shifted datetime value such that the edges of the day line up correctly with the target timezone. Since an actual timezone identifier can be specified, this also seamlessly accounts for changes in the daylight savings. This is very useful when writing queries for generating reports.

A basic example of such a query:

SELECT DATE(CONVERT_TZ(r.timestamp, @@session.time_zone, "Australia/Melbourne")) AS date, r.id
FROM some_table AS r
GROUP BY date
ORDER BY date;

If r.timestamp is a TIMESTAMP column, it is implicitly converted into the connection timezone before being passed into the CONVERT_TZ function, so @@session.time_zone is used as the from_tz parameter. For converting arbitrary DATETIME values, the from_tz would be set to the actual source timezone.

Issue

Pomelo currently has no translation for the CONVERT_TZ(dt,from_tz,to_tz) method.

Proposed API for consideration

Consider adding additional functions to Microsoft.EntityFrameworkCore.MySqlDbFunctionsExtensions to allow the CONVERT_TZ() function to be expressed:

namespace Microsoft.EntityFrameworkCore
{
    public static class MySqlDbFunctionsExtensions
    {
        // CONVERT_TZ(dateTime,fromTimezone,toTimezone)
        public static DateTime? ConvertDateTimeTimezone([CanBeNullAttribute] this DbFunctions _, DateTime dateTime, string fromTimezone, string toTimezone);

        // ... And an additional function for converting specifically from the connection timezone, for TIMESTAMP columns:

        // CONVERT_TZ(dateTime,@@session.time_zone,toTimezone)
        public static DateTime? ConvertDateTimeFromSessionTimezone([CanBeNullAttribute] this DbFunctions _, DateTime dateTime, string toTimezone);
    }
}

This would allow the above SQL query to be rewritten as the following LINQ query:

var results = await dbContext.SomeTable
    .Select(r => new {
        id = r.Id,
        date = EF.Functions.ConvertDateTimeFromSessionTimezone(r.Timestamp, "Australia/Melbourne").Date
        })
    .GroupBy(d => d.date)
    .OrderBy(d => d.Key)
    .ToListAsync();

[lauxjpn]

Does ConvertDateTimeFromSessionTimezone() need its own name, or should we just add another overload to ConvertDateTimeTimezone()?

[crozone]

An overload can probably do it, it'll just have to be clearly documented in the method XML docstring.

I can't see an elegant way to specify @@session.time_zone as the parameter otherwise.

[crozone]

I'm going to begin some work on this soon and try to get a prototype running, should I branch off master? Ideally I'd like this to be backported into the 6.0.0 release (as a minor or patch release?) since that aligns with the .NET 6 LTS release, if that's feasible.

[lauxjpn]

I'm going to begin some work on this soon and try to get a prototype running, should I branch off master?

Yes.

Ideally I'd like this to be backported into the 6.0.0 release (as a minor or patch release?) since that aligns with the .NET 6 LTS release, if that's feasible.

This will depend on the impact the PR has on the code base. While we usually do not add features to already released major versions, it is possible as long as there is very litte risk involved. Just adding another translation is potentially one of those features that could make it into the current release instead of the next.

[naitikmalaviya]

Another approach would be mapping TimeZoneInfo.ConvertTime methods to CONVERT_TZ mysql function. I've not gone into details of all ConvertTime methods but if this approach looks good to you guys then I'd be happy to submit a PR in next few days.

[lauxjpn]

@crozone I went ahead and provided the EF.Functions implementations with #1860. Feel free to review the PR if you got time.

---

Another approach would be mapping TimeZoneInfo.ConvertTime methods to CONVERT_TZ mysql function. I've not gone into details of all ConvertTime methods but if this approach looks good to you guys then I'd be happy to submit a PR in next few days.

@naitikmalaviya I will currently not implement this translation myself, because it might be cumbersome to ensure that the MySQL time zone information table is in sync with .NET. If you feel strongly about adding those translations as well, please open a separate issue and feel free to provide a PR to discuss the implementation. Thanks!

[crozone]

Looks fantastic, thank you!! Also seems like a very clean example of how to add additional translations in the future.