Question 114717 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can't cast database type timestamp without time zone to Instant

reservationLogs = await this.dbContext.ReservationLogs
                   .Where(r => r.ProcessedAt == null)
                   .Where(r => r.PropertyId == validPropertyId)
                   .OrderBy(r => r.CreatedAt).ThenBy(r => r.Operation)
                   .Take(200)
                   .ToListAsync();

some times with the same query i get the error

‘ Can’t cast database type timestamp without time zone to Instant’

note: CreatedAt nodaTime instane

i am trying to find the exact reason

Tags:

2

Answers


  1. 0

    You can do cast using Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime package

    protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
    builder.UseNpgsql("connection-string",
        o => o.UseNodaTime());
}

    or:

    builder.Services.AddDbContext<ApplicationDbContext>(
    options => options.UseNpgsql(
        builder.Configuration.GetConnectionString("DefaultConection"),
        o => o.UseNodaTime()));

    source

    Login or Signup to reply.
  2. 0

    The issue is that even though the date and time is clear, it is unclear whether or which timezone was in use. If I tell you that tomorrow at 5 P.M. I will go for a walk, then it will be unclear from your perspective what the exact time it will be, unless you know what timezone was I assuming while saying so.

    You have the exact same type of confusion in your code and first, you need to install this plugin: https://www.npgsql.org/doc/types/nodatime.html

    According to the docs, you need to add a dependency like this:

    using Npgsql;

// Place this at the beginning of your program to use NodaTime everywhere (recommended)
NpgsqlConnection.GlobalTypeMapper.UseNodaTime();

// Or to temporarily use NodaTime on a single connection only:
conn.TypeMapper.UseNodaTime();

    The docs go further in specifying how you can read and write values:

    // Write NodaTime Instant to PostgreSQL "timestamp with time zone" (UTC)
using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_timestamptz) VALUES (@p)", conn))
{
    cmd.Parameters.Add(new NpgsqlParameter("p", Instant.FromUtc(2011, 1, 1, 10, 30)));
    cmd.ExecuteNonQuery();
}

// Read timestamp back from the database as an Instant
using (var cmd = new NpgsqlCommand(@"SELECT my_timestamptz FROM mytable", conn))
using (var reader = cmd.ExecuteReader())
{
    reader.Read();
    var instant = reader.GetFieldValue<Instant>(0);
}

    Since you are not directly writing the query, but use the Entity Framework to do so you have another level of expression. But this is well-documented as well. You can safely and soundly declare types like this:

    public LocalDate Date {get; set;}

    Read this full article: https://www.davepaquette.com/archive/2019/03/26/using-noda-time-with-ef-core.aspx

    You will need to find out exactly where the error occurs. It seems to me that the OrderBy is the culprit as well as the selection. You can change the type of the data member of your model.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search