EntityFramework Core format DateTime in sqlite database

Question

I'm using EF Core 2.0 with SQLite and I want to reduce the size of my database without loosing the human readable datetime value in my table column. Currently the data context stores the full DateTime CLR object as a a string like "2018-03-10 16:18:17.1013863" but in my case "2018-03-10 16:18:17" would be enought. What I have to do?

Answer 1

The fractional seconds aren't stored if they're zero. You can safely remove them from the existing data. Update your application to not specify them. You can do this eagerly (when you set DateTime values on your entities) or lazily during SaveChanges.

public override int SaveChanges()
{
    var dateTimeProperties =
        from e in ChangeTracker.Entries()
        where e.State == EntityState.Added
                || e.State == EntityState.Modified
        from p in e.Properties
        where p.CurrentValue is DateTime
        select p;
    foreach (var property in dateTimeProperties)
    {
        // Strip millisecond
        var value = (DateTime)property.CurrentValue;
        property.CurrentValue = new DateTime(
            value.Year,
            value.Month,
            value.Day,
            value.Hour,
            value.Minute,
            value.Second);
    }

    return base.SaveChanges();
}

Answer 2

To add on bricelam reply. A better (Shorter and faster) code for trimming miliseconds would be:

property.CurrentValue = value.AddTicks(-value.Ticks % TimeSpan.TicksPerSecond)