Support for `time_bucket` function

[fschick]

Description

Is there any plan to add support for time_bucket function for queries?

_dbContext
    .Set<Measurement>()
    .Select(m => TimescaleDbFunctions.TimeBucket(TimeSpan.FromMinutes(5), m.Time))
    .ToList();

Use case / motivation

I would like to query time buckets outside of aggregates and views.

Proposed solution

The following TimescaleDbFunctions gives an idea what I mean and how it can be implemented.

public static class TimescaleDbFunctions
{
    private static readonly MethodInfo _timeBucket = typeof(TimescaleDbFunctions).GetMethod(nameof(TimeBucket), [typeof(TimeSpan), typeof(DateTimeOffset)])!;

    public static DateTimeOffset TimeBucket(TimeSpan interval, DateTimeOffset timestamp)
    {
        var ticks = timestamp.Ticks;
        var intervalTicks = interval.Ticks;
        var bucketTicks = (ticks / intervalTicks) * intervalTicks;
        return new DateTimeOffset(bucketTicks, timestamp.Offset);
    }

    /// <summary>
    /// Registers TimescaleDB related functions to the entity framework.
    /// </summary>
    /// <param name="modelBuilder">The model builder.</param>
    public static void RegisterTimeScaleFunctions(this ModelBuilder modelBuilder)
    {
        modelBuilder
           .HasDbFunction(_timeBucket)
           .HasName("time_bucket");
    }
}

Or if you prefer extension methods:

public static class TimescaleDbExtensions
{
    private static readonly MethodInfo _timeBucket = typeof(TimescaleDbExtensions).GetMethod(nameof(TimeBucket), [typeof(DateTimeOffset), typeof(TimeSpan)])!;

    public static DateTimeOffset TimeBucket(this DateTimeOffset timestamp, TimeSpan interval)
    {
        var ticks = timestamp.Ticks;
        var intervalTicks = interval.Ticks;
        var bucketTicks = (ticks / intervalTicks) * intervalTicks;
        return new DateTimeOffset(bucketTicks, timestamp.Offset);
    }

    /// <summary>
    /// Registers TimescaleDB related functions to the entity framework.
    /// </summary>
    /// <param name="modelBuilder">The model builder.</param>
    public static void RegisterTimeScaleFunctions(this ModelBuilder modelBuilder)
    {
        modelBuilder
           .HasDbFunction(_timeBucket)
           .HasName("time_bucket")
           .HasTranslation(args => new SqlFunctionExpression(
               "time_bucket",
               [args[1], args[0]], // Swap parameters: interval (args[1]), timestamp (args[0])
               nullable: false,
               argumentsPropagateNullability: [false, false],
               typeof(DateTimeOffset),
               null));
    }
}