EFCore Postgres timestamp loses tick precision

Question

I'm having a problem with my application that is reading from a JSON API that is providing me times in UTC in the following format:

2019-09-19T23:46:00.1183275Z

I am storing these times in a Postgres database using Entity Framework Core and using them later to make sure I am only getting newer data from the API. The problem I am having is that when these timestamps are written to the database, I am losing the precision that .NET has down to the ticks of the timestamp. C# has the ticks for the above timestamp as: 637045335601183275, but when I read that same time back out of the database, the ticks are 637045335601183270.

I tried searching the documentation but I could not find a way to change the precision of the timestamp in the database. Is there a way? Failing that is there a consistent way to round the DateTime to a precision that won't be lost by the database?

Example:

Context

public class MyContext : DbContext
{
    public DbSet<Item> Items { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(@"host=localhost;port=5432;database=TestDb;user id=postgres;password=password");
    }
}

Item

public class Item
{
    public int Id { get; set; }
    public DateTime CreateTime { get; set; }
}

Program

class Program
{
    static void Main(string[] args)
    {
        var item = JsonConvert.DeserializeObject<Item>("{Id: 1, CreateTime: \"2019-09-19T23:46:00.1183275Z\"}");
        Item readItem;

        using(var context = new MyContext())
        {
            context.Items.Add(item);
            context.SaveChanges();
        }

        using(var context = new MyContext())
        {
            readItem = context.Items.First();
        }

        Console.WriteLine(item.CreateTime.Ticks.Equals(readItem.CreateTime.Ticks)); //Outputs False
    }
}

Above created using EFCore 2.2.6 and Npgsql.EntityFrameworkCore.PostgreSQL 2.2.4

Answer 1

I tried searching the documentation but I could not find a way to change the precision of the timestamp in the database. Is there a way?

No, there is not.

Postgres date / time types are alas only accurate to the microsecond (1 / 1,000,000 second).

.NET dates are measured in 100 nanosecond units called ticks (a tick is 1 / 10,000,000 second).

Since there are 10 ticks to every microsecond, .NET dates are not going to be round trippable through Postgres.

Failing that is there a consistent way to round the DateTime to a precision that won't be lost by the database?

Yes - DateTime has a constructor which takes Ticks. You could, therefore, use item.CreateTime.Ticks to get your initial ticks value, alter it in some way (decide whether you want to ceiling or floor or round or whatever) and then pass it to the constructor. This value (accurate to the microsecond) would then round trip through Postgres just fine.