Início / coding / Perguntas / 79310533
Accepted
Andrus
Asked: 2024-12-27 06:17:26 +0800 CST

Como passar tstzrange para postgres

  • 772

Passando tstzrange para postgres no EF Core usando

DateTime algus = DateTime.Parse("2024-11-25T00:00:00+02:00", CultureInfo.InvariantCulture).ToUniversalTime();
DateTime lopp = DateTime.Parse("2025-01-25T22:00:00+02:00", CultureInfo.InvariantCulture).ToUniversalTime();

await ctx.Database.SqlQuery<DateTime>($@"
select lower(tstzrange '[{algus},{lopp})') as ""Value""
").ToArrayAsync();

gera erro

invalid input syntax for type timestamp with time zone: "@p0" at character 20
STATEMENT:  select tstzrange '[@p0,@p1)'

Como passar tstzrange para o Postgres?

O código está no controlador ASP.NET 9 MVC

O aplicativo usa o servidor Postgres 12 e posterior e o mais recente Npgsql Entity Framework Core Provider

asp.net-mvc

1 respostas

  1. Best Answer

    Como PostgreSQLdoc, tstzrangeé um tipo de intervalo integrado https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

    Para Npgsql, o tipo de intervalo é mapeado, NpgsqlRange<TElement>pois nenhuma classe C# comum pode conter seu valor. https://www.npgsql.org/doc/types/basic.html

    A seguir está um exemplo para analisar e selecionar tstzrange:

    using Microsoft.EntityFrameworkCore;
using NpgsqlTypes;

var options = new DbContextOptionsBuilder<ApplicationDbContext>()
    .UseNpgsql("Host=localhost:5433;Database=postgres;Username=postgres;Password=postgres")
    //.EnableSensitiveDataLogging()
    //.LogTo(Console.WriteLine, LogLevel.Trace)
    .Options;

var ctx = new ApplicationDbContext(options);

DateTime algus = DateTimeOffset.Parse("2024-11-25T00:00:00+02:00").UtcDateTime;
DateTime lopp = DateTimeOffset.Parse("2025-01-25T22:00:00+02:00").UtcDateTime;

var range = new NpgsqlRange<DateTime>(algus, lopp);

var r1 = await ctx.Database.SqlQuery<NpgsqlRange<DateTime>>($"select {range} as \"Value\"").FirstAsync();
var list = await ctx.Database.SqlQuery<NpgsqlRange<DateTime>>($"select {range}").ToListAsync();

Console.WriteLine(r1.LowerBound);
Console.WriteLine(r1.UpperBound);

var r2 = list[0];

Console.WriteLine(r2.LowerBound);
Console.WriteLine(r2.UpperBound);


public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
}
    • 0

relate perguntas